Solved

Multiple .txt File Import

Posted on 2014-02-12
10
523 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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