Access: Importing Multiple Text Files with Unknown Specs or Delim/Fixed Width

andi used Ask the Experts™
I am looking for some help in the following case. I have various text files to import into an Access db but the files are not consistent. Some are pipe delimited while others are fixed width. The end goal is to import one field the account field. What i am looking to do is, while looping through the text files to choose the proper Specification, generally it is 1 of 3 specs and/or if Pipe Delimited or Fixed Width.

Currently, the workaround is to import each file in the directory three times and then to delete the tables that are empty or less than the 8 char account record.

Any help is appreciated.

Or if anything a solution to choose spec based on file name?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
If you can identify the format based on the source or the file name, you can control the import that way.  If you get large numbers of files, segregate them into separate directories and use the directory to control the format.  Here's a code snippet from one of my apps.  The two text file formats have import specs but the spreadsheet doesn't support them.   If you can't make a common spec, you can still do the import but you will need to link to the external file and then use code to examine it and run an append query to put the data into the permanent tables.
    Select Case Mid(Me.txtFileNameMember, InStrRev(Me.txtFileNameMember, ".") + 1, 3)
        Case "csv"
            DoCmd.TransferText acImportDelim, , "temp_Members", Me.txtFileNameMember, True
        Case "txt"
            DoCmd.TransferText acImportDelim, "SubPipe", "temp_Members", Me.txtFileNameMember, True
        Case Else
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "temp_Members", Me.txtFileNameMember, True
    End Select

Open in new window

Distinguished Expert 2017

Offered code sample to identify file type based on extension.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial