Manage Text File

  • Thread starter Thread starter Bryan Spiers
  • Start date Start date
B

Bryan Spiers

I have a challenge. Maybe someone can relate and give me some much needed
guidence. I like to use access to handle text files containing data. Almost
all of the time I am dealing with many csv files in a folder and bring them
in to a single table (("MainCpp") and then start the analysis from this data.
____________________________________________
With Application.FileSearch
.LookIn = myFolder
.FileName = "*.csv"
.Execute
If .foundfiles.Count > 0 Then
For fileLoop = 1 To .foundfiles.Count
DoCmd.TransferText acImportDelim, "CPPImportSpec", "MainCPP",
..foundfiles(fileLoop)
Next fileLoop
End If
_____________________________________________________
This handles the many csv files in a folder and imports them to the MainCPP
table. Here is my quesion/challenge. ....I would love to modify this to
allow a user to manage some of the files that do not need to be imported with
out having the user open an explorer window>sequence the files> delete the
oldest ones not needed.

I picture a form that opens up with a list of all the files (sequenced by
date - I get one each day and I normally want to work on the latest 20 or so
files) On the form could vba tell the form to list the files and with a radio
button allow me to select some of the files for deletion prior to importing
the files using the above code>>??
 
You have several approaches.

I would just pull the data into a temp table and launch up a continues form
with a check box column.

So, you build a table with two columns (file name and a yes/no field called
InPort)

then, you use your code to fill up the table.

eg:
dim rstFiles as dao.RecordSet
currentdb.Execute "delete * from tblFiles"

If .foundfiles.Count > 0 Then
For fileLoop = 1 To .foundfiles.Count
rstFiles.Add
rstFiles!FileName = .foundfiles(fileLoop)
rstFiles!InPort = true
rstFiles.Update
next fileLoop

etc

You can then launch a continues form in which the user selects the files

docmd.OpenForm "frmSelectFiles"

The above form would be a "continues" that display the two collums, and the
user could check (or uncheck) which files to import.

You then in code simply process that table of files

eg:

dim rstFiles as dao.RecordSet

set rstFiles = currentdb.
OpenRecordSet("select * from tblFiles where inPort = true")

do while rstFiles.EOF = false
DoCmd.TransferText acImportDelim, _
"CPPImportSpec", _
"MainCPP", rstFiles!FileName
rstFiles.moveNext
loop

The above is "air" code. You could also in place of a continues form use a
listbox and perhaps avoid the use of a temp table. However, the above is a
good workaround for the time being.
 
Bryan said:
I have a challenge. Maybe someone can relate and give me some much needed
guidence. I like to use access to handle text files containing data. Almost
all of the time I am dealing with many csv files in a folder and bring them
in to a single table (("MainCpp") and then start the analysis from this data.
____________________________________________
With Application.FileSearch
.LookIn = myFolder
.FileName = "*.csv"
.Execute
If .foundfiles.Count > 0 Then
For fileLoop = 1 To .foundfiles.Count
DoCmd.TransferText acImportDelim, "CPPImportSpec", "MainCPP",
.foundfiles(fileLoop)
Next fileLoop
End If
_____________________________________________________
This handles the many csv files in a folder and imports them to the MainCPP
table. Here is my quesion/challenge. ....I would love to modify this to
allow a user to manage some of the files that do not need to be imported with
out having the user open an explorer window>sequence the files> delete the
oldest ones not needed.

I picture a form that opens up with a list of all the files (sequenced by
date - I get one each day and I normally want to work on the latest 20 or so
files) On the form could vba tell the form to list the files and with a radio
button allow me to select some of the files for deletion prior to importing
the files using the above code>>??
Click to expand...

The following might give you a head start:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/12315af158a03f05

James A. Fortune
(e-mail address removed)
 
Thanks for the link, I am reviewing the detail and having a tough time, give
me some time to digest how to make this happen. Looks promising for sure.

I do appreciate you taking time to assist. Thank you.
 
Albert,

Thank you for reviewing this. I have been looking this over and I think I
see that you have given me something to work with-I am grateful to have your
help. Let me work on this and see if I can meld it into what I am trying to
accomplish. Thank you so much for taking time to reply. I am appreciative.
 
Back
Top