Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

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
0
DollarBillIA
Asked:
DollarBillIA
  • 4
  • 3
  • 3
1 Solution
 
GozrehCommented:
What are the fields in your table you want to import this ?
0
 
Harry LeeCommented:
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
 
DollarBillIAAuthor Commented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
GozrehCommented:
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
 
DollarBillIAAuthor Commented:
I will give this a shot!

Bill
0
 
GozrehCommented:
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
 
Harry LeeCommented:
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
 
DollarBillIAAuthor Commented:
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
 
Harry LeeCommented:
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
 
Harry LeeCommented:
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now