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

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?
andiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Offered code sample to identify file type based on extension.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.