Importing multiple CSV files and adding the filename as a field

[Mark Carlyle via AccessMonster.com]

Ok I have read all the posts that give this answer

Maybe try something like:

Function import()
path = "c:\import\"
myfile = Dir(path + "*.txt", vbHidden) 'gets first txt
file in path specified

Do While myfile <> "" 'will cause to loop through all
txt files in path
DoCmd.TransferText acImportDelim, "YOUR IMPORT
SPECIFICATION NAME", "TABLE NAME", path + myfile,
0 'imports file
myfile = Dir 'grabs next txt file
Loop
End Function

This would import ALL .txt files in the directory using
your specification. Then delete the files.

I can use this for my problem except I also need to add the filename as a
field in each record in the dataset.

so if the filename was filename.csv I would need to set a field in the
imported record = to that filename.

The other bad part I need to do is to remove the .csv from the filename.

Thanks a ton for any help

 

[Mark Carlyle via AccessMonster.com]

I have been playing with this some while waiting and am wondering.. .do i
need to import the data into a dataset... add the extra field, and then store
the data into a table?

If so can I get a bit of help on the code... I am not sure how to define the
dataset.

 

[John Nurick]

Hi Mark,

One way to do this is to use pretty much the same code you have below.
But make sure to create the table before importing any data; include a
FileName field.

Then add code after the TransferText statement that runs an update query
to include the filename in the just-added records. Something like this:

Dim strSQL As String

...
'build SQL update query
strSQL = "UPDATE [TABLE NAME] SET FileName = '" _
& Replace(myfile, ".csv", "") _
& "' WHERE FileName IS NULL;"
CurrentDB.Execute strSQL

 

[Mark Carlyle via AccessMonster.com]

Ok... here is what I ended up doing....

transimportform is a form that opens with the table data queried to show only
the records without trust numbers... since the imported ones will always be
the only ones blank.


Private Sub Command15_Click()
Dim path As String
Dim myfile As String
Dim trustnumber As String


path = "C:\echeck\Trasactions\"
myfile = Dir(path & "*.csv", vbHidden) 'gets first txt file in path specified

Do While myfile <> "" 'will cause to loop through all txt files in path

DoCmd.TransferText acImportDelim, , "transactions", path + myfile, -1
DoCmd.OpenForm "transimportform"
trustnumber = Left(myfile, 9)
Do While Forms![transimportform]![Description] <> ""

Forms![transimportform]![TrustAcctNumber] = trustnumber
DoCmd.GoToRecord , , acNext
Loop


DoCmd.Close

myfile = Dir 'grabs next txt file
Loop
End Sub

Question I still have...

Can I just populate the data directly into the query or do I have to open the
form?

 

[John Nurick]

You don't need to use a form if you build and execute a SQL query as I
suggested.

Ok... here is what I ended up doing....

transimportform is a form that opens with the table data queried to show only
the records without trust numbers... since the imported ones will always be
the only ones blank.


Private Sub Command15_Click()
Dim path As String
Dim myfile As String
Dim trustnumber As String


path = "C:\echeck\Trasactions\"
myfile = Dir(path & "*.csv", vbHidden) 'gets first txt file in path specified

Do While myfile <> "" 'will cause to loop through all txt files in path

DoCmd.TransferText acImportDelim, , "transactions", path + myfile, -1
DoCmd.OpenForm "transimportform"
trustnumber = Left(myfile, 9)
Do While Forms![transimportform]![Description] <> ""

Forms![transimportform]![TrustAcctNumber] = trustnumber
DoCmd.GoToRecord , , acNext
Loop


DoCmd.Close

myfile = Dir 'grabs next txt file
Loop
End Sub

Question I still have...

Can I just populate the data directly into the query or do I have to open the
form?