DollarBillIA
asked on
Multiple .txt File Import
I have about 3000 product data files in the .txt format. I would love to be able to pull them into a single spreadsheet so I can work with them and upload them to my website.
I can't figure out a way to do this a bunch at a time, and don't want to open 3000 individual files. I will attach a few of the files for samples.
Thanks in advance for the help.
Bill
W5083.txt
1503.txt
W1692PC.txt
W54008.txt
I can't figure out a way to do this a bunch at a time, and don't want to open 3000 individual files. I will attach a few of the files for samples.
Thanks in advance for the help.
Bill
W5083.txt
1503.txt
W1692PC.txt
W54008.txt
What are the fields in your table you want to import this ?
DollarBillIA,
Seems like the text files do not have standard formats.
All four samples you have uploaded are in different format. It makes automation very difficult. Unless their format is standardized, there will be lots rows that contains multiple errors.
Seems like the text files do not have standard formats.
All four samples you have uploaded are in different format. It makes automation very difficult. Unless their format is standardized, there will be lots rows that contains multiple errors.
ASKER
Even if I could just get them into one field it would help. I am going to rewrite them, my other alternative is to look at a paper catalog and go through them that way, but would prefer to just have them in a spreadsheet for reference as I created the text I want.
Not sure if that is clear or not, but 3000 rows of all the data from one file dumped into one field would be sufficient.
Bill
Not sure if that is clear or not, but 3000 rows of all the data from one file dumped into one field would be sufficient.
Bill
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will give this a shot!
Bill
Bill
I changed now the coding if you want for every file new record, give a look on it
Private Sub ImportFiles()
On Error Resume Next
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
strFolderPath = "C:\Import TXT files\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files
Dim txtfile As Object
For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
Set txtfile = objFS.OpenTextFile(objF1.Path)
Dim strFile As Variant: strFile = txtfile.ReadAll
If strFile = "" Then strFile = "Error"
CurrentDb.Execute "INSERT INTO tblImportedFiles ( Field1, FilePath ) SELECT '" & strFile & "' AS Expr1, '" & objF1.Path & "' AS Expr2;"
End If
Next
Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing
MsgBox "Done !"
End Sub
Import-txt-files.accdb
DollarBillIA,
This macro will help you to import all the text file (sitting in the same folder) all at once.
It will transfer all the data from each text file line by line. Each line on the text files will be transpose into columns, and one row per text file.
After that, you will have to fix up the combined columns or extra columns due to the inconsistent format of text files.
Import-All-TXT-in-Folder.xlsm
This macro will help you to import all the text file (sitting in the same folder) all at once.
It will transfer all the data from each text file line by line. Each line on the text files will be transpose into columns, and one row per text file.
After that, you will have to fix up the combined columns or extra columns due to the inconsistent format of text files.
Import-All-TXT-in-Folder.xlsm
ASKER
Harry, this starts to work but then I get an error after the first file it grabs. Here is what it says:
Run time error '1004'
I apologize, but I know nothing about creating or running these, so I am not good at troubleshooting.
Bill
Run time error '1004'
I apologize, but I know nothing about creating or running these, so I am not good at troubleshooting.
Bill
DollarBillIA,
Can you try going into the VBA editor and click on the Stop button before running the macro?
I just downloaded the file from my above post, and it ran just fine. Maybe something was jamming it.
Give it a try.
Can you try going into the VBA editor and click on the Stop button before running the macro?
I just downloaded the file from my above post, and it ran just fine. Maybe something was jamming it.
Give it a try.
DollarBillIA,
I have corrected a few issues on the vba to make sure it's getting all the available information in the text files.
Please test this new uploaded file instead.
By the way, can up load a few more text files? Preferably from the top of the list when sorting the folder in A-Z order. Since you said the vba stops after the first file. I want to see if there is anything in the text files that cause the Run-time 1004 error.
Import-All-TXT-in-Folder.xlsm
I have corrected a few issues on the vba to make sure it's getting all the available information in the text files.
Please test this new uploaded file instead.
By the way, can up load a few more text files? Preferably from the top of the list when sorting the folder in A-Z order. Since you said the vba stops after the first file. I want to see if there is anything in the text files that cause the Run-time 1004 error.
Import-All-TXT-in-Folder.xlsm