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,Androst
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:=acImportDeli
Does anyone have any idea why the manual way works but the vba does not?
Thanks!
Data Filename,Sample Name,Sample ID,Androstenedione,Androst
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:=acImportDeli
Does anyone have any idea why the manual way works but the vba does not?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
nextASKER
These are all great ideas. I am going to try them and see if it helps. Hopefully I will have good news soon!
Thanks!
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
Have you ever run into something like that?
Sadly, any error imaginable is possible in CSV files.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.