access acimport
i run the following code in my database
For Each Item In fld.Files
FileName = Item.NAME
DoCmd.TransferText acImportDelim, , "tbl_order_audit", foldername + FileName, True
I want to be able to add Foldername + Filename to the end of each record that is added. Every time this is run it imports around 10k lines into my database.
I also run this code for a pipe delimited file, however i tried it with the other file and it doesnt work due to them being different formats.
For Each Item In fld.Files
If InStr(Item.NAME, "RSP") Then
FileName = Item.NAME
Open (foldername & FileName) For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
Debug.Print TextLine ' Print . Textline would be your data value for use in your own 'split' code.
avarSplit = Split(TextLine, "|")
For intIndex = LBound(avarSplit) To UBound(avarSplit)
Next
CurrentProject.Connection.
"insert into tbl_all_audit ([field1], [field2], [field3], [field4], [field5], [field6], [field7], [field8]) select '" & avarSplit(0) & "', '" & avarSplit(1) & "', '" & avarSplit(2) & "', '" & avarSplit(3) & "','" & avarSplit(4) & "','" & Date & "', 'Response', '" & foldername & FileName & "'"
I am using SQL 2008 backend + adp frontend.
For Each Item In fld.Files
FileName = Item.NAME
DoCmd.TransferText acImportDelim, , "tbl_order_audit", foldername + FileName, True
I want to be able to add Foldername + Filename to the end of each record that is added. Every time this is run it imports around 10k lines into my database.
I also run this code for a pipe delimited file, however i tried it with the other file and it doesnt work due to them being different formats.
For Each Item In fld.Files
If InStr(Item.NAME, "RSP") Then
FileName = Item.NAME
Open (foldername & FileName) For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
Debug.Print TextLine ' Print . Textline would be your data value for use in your own 'split' code.
avarSplit = Split(TextLine, "|")
For intIndex = LBound(avarSplit) To UBound(avarSplit)
Next
CurrentProject.Connection.
"insert into tbl_all_audit ([field1], [field2], [field3], [field4], [field5], [field6], [field7], [field8]) select '" & avarSplit(0) & "', '" & avarSplit(1) & "', '" & avarSplit(2) & "', '" & avarSplit(3) & "','" & avarSplit(4) & "','" & Date & "', 'Response', '" & foldername & FileName & "'"
I am using SQL 2008 backend + adp frontend.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As LSMConsulting has already suggested, have you thought of executing and ALTER TABLE/UPDATE query after each import?
ASKER
but if it imports 1000 text files all into the same table, once it's in that table, how will i be able to tell which text file it was imported from?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i think i get what you mean, something like
For Each Item In fld.Files
FileName = Item.NAME
DoCmd.TransferText acImportDelim, , "tbl_order_audit", foldername + FileName, True
CurrentProject.Connection.
next
For Each Item In fld.Files
FileName = Item.NAME
DoCmd.TransferText acImportDelim, , "tbl_order_audit", foldername + FileName, True
CurrentProject.Connection.
next
That's the idea - it looks like you know what you're doing, but please ask if you need further assistance! :-)
ASKER
seems so simple when somebody points you in the right direction! Thanks for help.
ASKER
I had pressed the wrong person to give points to, wanted them split between JezWalters and LSMConsulting.
ASKER
foldername is the name of a mapped drive (M:\imports\)
this then looks through every txt file (filename) in that folder (around 1k of them) and imports each part of information into the database.
However what i want to do is add a field onto the end of the import which will write basically the whole unc path of the file it imported that particular row from (foldername + filename)