• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 70
  • Last Modified:

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!
0
alevin16
Asked:
alevin16
  • 3
  • 2
  • 2
  • +2
3 Solutions
 
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
 
Dale FyeCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 FyeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now