Getting "Type Conversion Failure" message (1 Viewer)

I've got a CSV file that was created using SAS software. The CSV file has column titles in the first row. When I attempt to import the CSV file into Access 2007, some fields give a "Type Conversion Failure" error message.

These offending fields usually start with data that appears all numeric, although the column should be text, and the error occurs when an alphanumeric cell is encountered. In other words, it seems that Access 2007 is automatically switching to numeric based on what is initially encountered in a column.

Is there a way to tell Access to not do this type of recognition? Is there something else I must do to prevent this error from happening? Is there perhaps a piece of code I could use to eliminate these errors?

Thanks in advance to all who reply....looking forward to hearing from you!

Can you not import into an existing table, and have that table set up to be all text fields? After it's imported, you can change the datatypes to whatever you want, but importing all as text first should do the trick. Just setup a blank table with X number of fields, all set to text.

Moniker,

Thanks for replying! I hadn't thought of that approach yet..... I checked on the Microsoft site, and the suggestion there was to add a row into the CSV file that specified a format for each column, i.e., Col A, AA, Col B, 99, Col CC AA, Col D, AA, Col E, AA and so forth. Then, import the file and after importing, then delete the row that was previously added.

I'm working on their suggestion at the moment, and will then try your suggestion.

Thanks again!

The MS approach is the one that i use. The reason for the error is that Access uses the format of the first line of data to determine the field type, hence controlling the content of the first line will set your fieldtypes correctly.

Either way will work, and both require a little extra effort. If you just have one text file to import, then the MS method is probably faster, but if you have several, then editing each CSV would take longer, IMO. Hope you got it figured out.