Solved

Multiple .txt File Import

Posted on 2014-02-12
10
508 Views
Last Modified: 2014-02-28
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
0
Comment
Question by:DollarBillIA
  • 4
  • 3
  • 3
10 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 39853766
What are the fields in your table you want to import this ?
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39854955
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.
0
 

Author Comment

by:DollarBillIA
ID: 39855110
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
0
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
ID: 39855233
This code will import all your txt files to one table, but each line in txt file will be a new record.
so you can use it as a start or maybe this is what you need.
Private Sub ImportFiles()
On Error GoTo ImportFiles_Err
    
    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
    
    For Each objF1 In objFiles
        If Right(objF1.Name, 3) = "txt" Then
            DoCmd.TransferText acImportDelim, "TextImportSpecs", "tblImportedFiles", strFolderPath & objF1.Name, False
        End If
    Next
    
    Set objF1 = Nothing
    Set objFiles = Nothing
    Set objFolder = Nothing
    Set objFS = Nothing
    
ImportFiles_Exit:
    Exit Sub
    
ImportFiles_Err:
    MsgBox Err.Number & " " & Err.Description
    Resume ImportFiles_Exit
End Sub

Open in new window

Import-txt-files.accdb
0
 

Author Comment

by:DollarBillIA
ID: 39855259
I will give this a shot!

Bill
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 10

Expert Comment

by:Gozreh
ID: 39855276
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
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39856752
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
0
 

Author Comment

by:DollarBillIA
ID: 39858121
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
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39859772
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.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39859930
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
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

We are happy to announce a brand new addition to our line of acclaimed email signature management products – CodeTwo Email Signatures for Office 365.
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now