none
Import Multiple Text Files into Access RRS feed

  • Question

  • I have multiple text files (400+) I need to import into Access.

    Each text file has a different layout. I have a shell for each file already imported in Access (the shells were provided in .csv format and I was able to use DoCmd.TransferSpreadsheet to import).

    I would now like to automate the import of each text file (comma delimited) into Access. While I can manually import each one, that will take a long time.

    DoCmd.TransferText does not work for me because I do not have a file import specification for each of these text files:

    DoCmd.TransferText acImportDelim, , "SEQ" & mySEQ, MyFile, False

    I get the error message: Run-time error '2391': Field 'F1' doesn't exist in destination table 'SEQ001.'

    I then tried to use a SQL command to import the files:

    Dim SEQ As Integer
    Dim mySEQ As String
    Dim MyFile As String
    Dim daoDB As Database
    Dim strSQL As String

    SEQ = 1

    Set daoDB = OpenDatabase("C:\Census Data\2014 5-Year ACS\ACS 2014 5-Yr Dataset\2014ACS-5Yr-CT.accdb")

    Do Until SEQ > 121 'Round 1 import first 121 tables

        If SEQ < 10 Then
            mySEQ = "00" & SEQ
        ElseIf SEQ < 100 Then
            mySEQ = "0" & SEQ
        Else
            mySEQ = SEQ
        End If
       
        MyFile = "C:\Census Data\2014 5-Year ACS\ACS 2014 5-Yr Dataset\Connecticut All Geographies Not Tracts Block Groups"

        strSQL = """INSERT INTO [SEQ" & mySEQ & "] SELECT * FROM [TEXT;HDR=NO;DATABASE=" & MyFile & "].[e20145ct0" & mySEQ & "000.txt];"""
            Debug.Print strSQL
        daoDB.Execute strSQL

    I get this error: Run-time error '3078': The Micorsoft Access database engine cannot find the input table or query ... Make sure it exists and that its name is spelled correctly.

    I have hard-coded in the text file name just to make sure I have it correct, producing the same error message.

    I don't have a preference on which method to use to solve this problem, I just want to figure out how to automate the import of my numerous text files.

    Thanks in advance for your help!

    Wednesday, January 13, 2016 6:03 PM

Answers

  • Hi. See if the technique of using schema.ini presented in this thread at UtterAccess helps. Cheers!
    • Marked as answer by kms97003 Thursday, January 14, 2016 5:45 PM
    Wednesday, January 13, 2016 6:32 PM
  • It appears you have more quotes around your strSQL than you would need.  I believe you can get by with

        strSQL = "INSERT INTO [SEQ" & mySEQ & "] SELECT * FROM [TEXT;HDR=NO;DATABASE=" & MyFile & "].[e20145ct0" & mySEQ & "000.txt];"

    -Bruce


    • Marked as answer by kms97003 Thursday, January 14, 2016 5:45 PM
    Wednesday, January 13, 2016 8:22 PM
  • I was able to successfully import my 400+ text files into Access through code. Here are the steps I took to solve this problem:

    1. Since I had the file layout information already available to me in my access database, I followed these instructions to write a schema.ini file for each directory in which my text files reside.

    https://support.microsoft.com/en-us/kb/155512

    With some minor code alterations, I was able to use this code to write my schema.ini files for all of my text files.

    2. I then used the following code to import my text files:

    Sub ImportACSData()
    
    Dim SEQ As Integer
    Dim mySeq As String
    Dim MyFile As String
    Dim daoDB As Database
    Dim strSQL As String
    
    SEQ = 1
    
    Set daoDB = OpenDatabase("C:\Census Data\2014 5-Year ACS\ACS 2014 5-Yr Dataset\2014ACS-5Yr-CT.accdb")
    
    Do Until SEQ > 100
    
        If SEQ < 10 Then
            mySeq = "00" & SEQ
        ElseIf SEQ < 100 Then
            mySeq = "0" & SEQ
        End If
        
        MyFile = "C:\Census Data\2014 5-Year ACS\ACS 2014 5-Yr Dataset\Connecticut All Geographies Not Tracts Block Groups\E 1-99"
        strSQL = "INSERT INTO [SEQ" & mySeq & "] SELECT * FROM [TEXT;HDR=NO;DATABASE=" & MyFile & "].[e20145ct0" & mySeq & "000.txt];"
            'Debug.Print strSQL
        daoDB.Execute strSQL
        SEQ = SEQ + 1
    Loop
    
    ...
    
    End Sub
    

    I ran several loops to get each group of text files into Access.

    Thank you again for your help and advice Bruce and the DB Guy!

    • Marked as answer by kms97003 Thursday, January 14, 2016 5:51 PM
    Thursday, January 14, 2016 5:51 PM

All replies

  • Hi. See if the technique of using schema.ini presented in this thread at UtterAccess helps. Cheers!
    • Marked as answer by kms97003 Thursday, January 14, 2016 5:45 PM
    Wednesday, January 13, 2016 6:32 PM
  • It appears you have more quotes around your strSQL than you would need.  I believe you can get by with

        strSQL = "INSERT INTO [SEQ" & mySEQ & "] SELECT * FROM [TEXT;HDR=NO;DATABASE=" & MyFile & "].[e20145ct0" & mySEQ & "000.txt];"

    -Bruce


    • Marked as answer by kms97003 Thursday, January 14, 2016 5:45 PM
    Wednesday, January 13, 2016 8:22 PM
  • >>>DoCmd.TransferText does not work for me because I do not have a file import specification for each of these text files:

    DoCmd.TransferText acImportDelim, , "SEQ" & mySEQ, MyFile, False

    I get the error message: Run-time error '2391': Field 'F1' doesn't exist in destination table 'SEQ001.'<<<

    According to your description, I suggest that you could modify like below:

    DoCmd.TransferText acImportDelim, "", "SEQ" & mySEQ, MyFile, False


    SpecificationName: A string expression that's the name of an import or export specification you've created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.

    For more information, click here to refer about DoCmd.TransferText Method (Access)

    Thursday, January 14, 2016 2:33 AM
  • Thank you for the responses.

    Bruce - when I remove the extra quotes in my strSQL, I get a new error message: Run-time error '3127': The INSERT INTO statement contains the following unknown field name: 'F1'. Make sure you have typed the name correctly, and try the operation again.

    David - I get the same error message with the original code as I did with your suggested change. (Run-time error '2391': Field 'F1' doesn't exist in destination table 'SEQ001.')

    I am going to try to create a schema.ini file to define the layout for my text files as suggested by the DB guy, as it seems the undefined layout definitions are the root of my problem.

    I'm open to alternative solutions if anyone has any suggestions.

    Thanks.

    Thursday, January 14, 2016 12:58 PM
  • I was able to successfully import my 400+ text files into Access through code. Here are the steps I took to solve this problem:

    1. Since I had the file layout information already available to me in my access database, I followed these instructions to write a schema.ini file for each directory in which my text files reside.

    https://support.microsoft.com/en-us/kb/155512

    With some minor code alterations, I was able to use this code to write my schema.ini files for all of my text files.

    2. I then used the following code to import my text files:

    Sub ImportACSData()
    
    Dim SEQ As Integer
    Dim mySeq As String
    Dim MyFile As String
    Dim daoDB As Database
    Dim strSQL As String
    
    SEQ = 1
    
    Set daoDB = OpenDatabase("C:\Census Data\2014 5-Year ACS\ACS 2014 5-Yr Dataset\2014ACS-5Yr-CT.accdb")
    
    Do Until SEQ > 100
    
        If SEQ < 10 Then
            mySeq = "00" & SEQ
        ElseIf SEQ < 100 Then
            mySeq = "0" & SEQ
        End If
        
        MyFile = "C:\Census Data\2014 5-Year ACS\ACS 2014 5-Yr Dataset\Connecticut All Geographies Not Tracts Block Groups\E 1-99"
        strSQL = "INSERT INTO [SEQ" & mySeq & "] SELECT * FROM [TEXT;HDR=NO;DATABASE=" & MyFile & "].[e20145ct0" & mySeq & "000.txt];"
            'Debug.Print strSQL
        daoDB.Execute strSQL
        SEQ = SEQ + 1
    Loop
    
    ...
    
    End Sub
    

    I ran several loops to get each group of text files into Access.

    Thank you again for your help and advice Bruce and the DB Guy!

    • Marked as answer by kms97003 Thursday, January 14, 2016 5:51 PM
    Thursday, January 14, 2016 5:51 PM
  • Hi. Congratulations! Glad we could assist. Good luck with your project.
    Thursday, January 14, 2016 5:57 PM
  • that's great that you got it working kms.  You may want to bookmark this link for future reference.

    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

    Ken demonstrates how to import data from multiple Excel files in a folder (the code could easily be changed to import CSV files or text files or whatever).


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, January 15, 2016 5:29 PM