Link to home
Start Free TrialLog in
Avatar of DollarBillIA
DollarBillIAFlag for United States of America

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
Avatar of Gozreh
Gozreh
Flag of United States of America image

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.
Avatar of DollarBillIA

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
ASKER CERTIFIED SOLUTION
Avatar of Gozreh
Gozreh
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I will give this a shot!

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

Open in new window

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
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
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.
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