Access VBA import of text file is not working

In Access 2013 I am trying to import a comma separated text file.  The first row will not import, I get Type conversion Failure.  But when I do it manually using the wizard I have no issue.  Here are the first few "columns" in the text file:

Data Filename,Sample Name,Sample ID,Androstenedione,Androstenedione 13C3,Cortisol,Cortisol D4

Yes those are headers, but they can change with every file so I feel it would be easier to just import them as regular data and deal with it with some programming. The first 3 "columns" work fine, but after Sample ID none of the others will work, they all get the Type Conversion Failure.  I checked the table and all the fields are Short Text.

 Here is the vba line:

DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="", TableName:="NoPound", FileName:="C:\OMNI LIS\Preconversion\NOPound.txt", hasfieldnames:=False

Does anyone have any idea why the manual way works but the vba does not?

Thanks!
alevin16Asked:
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:
No, unless the VBA method isn't properly handling the hasfieldnames:=False argument.  Add another row ahead of the existing first row to see if you get a different result.  Make sure it has properly formed column names.

Look closely at the field names.  Are you sure that none of them start with a space or non-printable character?

I once had to import a file with a name that had a leading space.  Access couldn't do it and the data was such that importing the header row as data was also a problem.  I ended up using OLE automation to open the file in Excel and fix the damn header.  Then importing the fixed .csv file.  The state refused to fix the column name because Excel didn't have a problem with the bad column name so as far as they were concerned, it was my problem.
0
Gustav BrockCIOCommented:
If the manual method works, save the setup with a specification name.
Then apply that name here:

SpecificationName:="NameOfYourSpecification"

Open in new window

0

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
Dale FyeOwner, Developing Solutions LLCCommented:
Personally, I would import the header row as a header row and then deal with the column headings using the fields collection of the imported table, something like:
Dim rs as DAO.Recordset
Dim fld as DAO.Field

set rs = currentdb.openrecordset("SELECT * FROM NoPound WHERE 0 = 1")
for each fld in rs.Fields
   debug.print fld.Name
next

Open in new window

You can also determine the data type of each field using the fields collection.
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

alevin16Author Commented:
These are all great ideas.  I am going to try them and see if it helps. Hopefully I will have good news soon!

Thanks!
0
Matt NicholasBusiness AnalystCommented:
Are you trying to import and append the CSV? If so, the import may be failing due to the static table not containing the 'columns' that are dynamic in your import.

If you have a complete list of column headers I would build the static Access table to contain each of these so that an import will map the respective entries based on the existing rows in the table (since it appears that the first three columns always map fine).
0
alevin16Author Commented:
Hello Matt,

I actually tried that just before you posted :)  It was odd, I got an error that it could not import a NoName field which leads me to believe there is something odd about the header row.  I am wondering if there are non printing characters in there I am not seeing.

Have you ever run into something like that?
0
Gustav BrockCIOCommented:
Have you ever run into something like that?

Sadly, any error imaginable is possible in CSV files.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Agree with Gustav regarding the reliability of CSV files, I've found similar situation with importing EXCEL files.  

The latest issue I've had with CSVs is that Access may not accurately break the fields on commas, when the comma is encapsulated within quotes (ie, a text field which contains commas).  I've resorted to reading the text files, parsing them on the commas, and reconstructing each line by replacing these embedded commas with "||", then I write each line back out to another text file.  After reading the modified file into Access, I then replace the || that are embedded in the text with the original commas.
0
PatHartmanCommented:
Did you open the file with another application as I suggested earlier?

Try Excel
Try Notepad

If you can't see any spaces, try just typing over the header row to see if that gets rid of the problem.  If it does, we can help you automate the fix if the creator of the file won't fix the problem.
0
PatHartmanCommented:
Alevin,
Selecting random different answers in an effort to be gracious doesn't help anyone.  No one who reads the thread will be able to determine what worked for you, if anything and if one solution did actually work, you diminished the value of their contribution by awarding participation points to everyone else.
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
Programming

From novice to tech pro — start learning today.