Append filename CSV imported files

[kriemer]

I am using the ===CODE=== at the bottom of this message (lifted from somewhere) to import >5000 CSV formated datafiles into one table. No problem with this part.

I have been struggling with the code to append the file name (minus extention to each record.

I believe that I can use the follow code to get the filename:
filename = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))

I am competely at a loss as to how to proceed from here.

Suggestion please?

============CODE===============
Private Sub Command0_Click()

Dim InputDir, ImportFile As String, tblName As String, filename As String

InputDir = "C:\"

ImportFile = Dir(InputDir & "\*.csv")

SheetName = shtName

Do While Len(ImportFile) > 0

'Select: Import all files into file
tblName = "Uncorrected Ticker Data"

'Select:Import csv
DoCmd.TransferText acImportDelim, "Import Specification", tblName, InputDir & ImportFile, False

ImportFile = Dir

Loop

End Sub

 

[Bat17]

I dont think that uou can do it as you append the data but you could run an updatequery on each loop
some thing like
strSql = "UPDATE tblMyTable SET tblMyTable.txtFile = '" & filename & "' WHERE (((tblMyTable.txtFile) Is Null));"
DoCmd.RunSQL strSql

HTH

Peter

 

[kriemer]

Looks like what I want

I think I understand what your code is doing but the "where... isNull" implies importing to an emty field in each record (I think).

Where does this empty field come from?

Can I append this code (I understand that I may need to add some modifications ) before the "Loop" command?

Thanks

 

[Bat17]

I am assuming that when you do the transferText that it leaves an empty field in the table for the file name. this is what the ISNull is looking for, It will then update those empty fields to the file name. It assumes that field is called 'txtFile'

Private Sub Command0_Click()

Dim InputDir As String
Dim ImportFile As String
Dim tblName As String
Dim filename As String
Dim strSql  As String
InputDir = "C:\"

ImportFile = Dir(InputDir & "\*.csv")

SheetName = shtName

Do While Len(ImportFile) > 0

'Select: Import all files into file
tblName = "Uncorrected Ticker Data"

'Select:Import csv
DoCmd.TransferText acImportDelim, "Import Specification", tblName, InputDir & ImportFile, False

strSql = "UPDATE [" & tblName & "] SET [" & tblName & "].txtFile = '" & filename & "' WHERE ((([" & tblName & "].txtFile) Is Null));"
DoCmd.RunSQL strSql

ImportFile = Dir

Loop

End Sub

HTH

Peter

 

[kriemer]

Almost Working

Wow you are good, this is so close to working!!!

I will describe what is happening:
1 - Access Table is generated: Uncorrected Ticker Data.txtFile
2 - Enter Parameter Window pops up "Uncorrected Ticker Data.txtFile"
3 - Error message in VBA code: DoCmd.RunSQL strSql

Thanks for any help!!

k

 

[Bat17]

do you actualy have a field txtFile in ""Uncorrected Ticker Data"?
If you have called the field to store the filename something else then change txtFile to what ever is the right name.

Peter

 

[kriemer]

Revised the field name

I revised the field name (Field8) in the script

No more error messages

"You are about to update x Rows..." warning pops up (any way to circumvent that >5000 files to import)

I click yes

Field8 is not updated (still empty)

Thanks again

k

 

[Bat17]

It may be you have an empty string rather than Null.
"UPDATE [" & tblName & "] SET [" & tblName & "].txtFile = '" & filename & "' WHERE ((([" & tblName & "].txtFile) =''));"

Must Rush

Peter

 

[kriemer]

Nope that didn't do it either (damn!!!)

But I have been able to turn off the Warnings....

Thanks again, have a good night!

k

 

[Bat17]

if you add debug.print strSql after the "DoCmd.RunSQL strSql" Line
Then Ctl-G will open the debug window and you will be able to see the generated sqlcode. if you copy/paste that into a new query you may be able to work out what I got wrong.
If not paste it here and I will see if I can see why it does not work.

Peter

 

[kriemer]

strSql = "UPDATE [" & tblName & "] SET [" & tblName & "].Ticker = '" & filename & "' WHERE ((([" & tblName & "].Ticker) Is Null));"

Results in:

UPDATE [Uncorrected Ticker Data] SET [Uncorrected Ticker Data].Ticker = '' WHERE ((([Uncorrected Ticker Data].Ticker) Is Null));

And

"UPDATE [" & tblName & "] SET [" & tblName & "].txtFile = '" & filename & "' WHERE ((([" & tblName & "].txtFile) =''));"

Results in:

UPDATE [Uncorrected Ticker Data] SET [Uncorrected Ticker Data].Ticker = '' WHERE ((([Uncorrected Ticker Data].Ticker) =''));

I don't get anything from this, do you?

Thanks

k

 

[Bat17]

Sorrym missed out your line that gives the file name! Add
filename = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))
before
strSql = "UPDATE...............

Peter

 

[kriemer]

Sorry ??????

A mere 19 lines of code; you sir are a genius!!!!

Many thanks


k

 

[kriemer]

Is there any way to slowdown the execution of the import? Access seems to freeze after about 400,000 records are in the table.

I have turned of screensavers, all other programs, etc.

I've tried researching this on a few forums with no luck

 

[Bat17]

you could try sticking 'DoEvents' into the loop to give Access chance to breath

For j = 1 To 50
DoEvents
Next j

You will probably need to play with the number of iterations to get best effect

Peter

 

[kriemer]

Worked like a charm!

BTW, is the iteration nn just a read cycle, or are they time units (milliseconds?)

Thanks again, again.


k

 

[ineuw]

Is there any way to slowdown the execution of the import? Access seems to freeze after about 400,000 records are in the table.

I have turned of screensavers, all other programs, etc.

I've tried researching this on a few forums with no luck

There are 2 places where you can make the change:

In Windows Registry, search for MaxLocksPerFile.
Right click on Modify
Change the data from Hexadecimal to Decimal
Change vaue from 9500 to 100000, 200000 . . . etc. to whatever you wish.
I am using 500000 which works fine.

MaxLocksPerFile appears for each Jet engine version listed (3), which means that you should change all three - Jet 3.5, Jet 3.6, Jet 4.0 values.

You can also set this value in a function called by the Autoexec macro.
The Autoexec macro line Runcode calls a Public function named
fnStartUp [=fnSartUp()]

Here is the code for fnStartUp

Public Function fnStartUp()
DBEngine.SetOption dbMaxLocksPerFile, 500000
End Function

I hope this helps.