I am having some issues with Data Types on an Import of data from a Text or Excel File.
I do the first Import and create the table from that first import. I select the field types I want etc and that imports fine. When I attempt to import the next file into the same table I get all kinds of Type COnversion Errors but the data is teh same in the fields.
The fields I am having issues with are:
US Zip Code and Healthcare Diagnosis Codes.
The US Zip field has the 5 digit zip and some have the Zip + Four code with a dash in between. 19606 or 19606-9920 etc.
The Diagnosis code is either an all Numeric Code or an Alpha Numeric. IE 789.1 or V78.1.
I've attempt various field types for these but the issue is the First Import to create the table works fine. But when I go to import secondary files into the same table I get Type Conversion Errors on these fields.
I've tried, Text, Number and even Memo, but with memo I can't use it in a Cross Tab later.
You should be able to import the first file, then go to the table design view and make sure both of these field are text. Both the Zip and Diagnosis need to be text because of the dashes and letters contained in some records. Also make sure the text fields are long enough to hold the data you are importing.
Then import the second file to the existing table. If the second import still fails looks at the import errors table that should be created during the process and it will tell you the exact row and field that had the issue. Go back and look at the raw data for this row and see if you spot the problem.
If this doesn't get you anywhere try importing the second file first into a new table and compare the structure of the two.
If this still doesn't resolve the problem, try temporarily linking the second file and running an append query off it to add the records to the table.
I've tried all of that with the exception of the linking and appending.
They first file works fine but thent he second file the Zip and Diagnosis codes that have the dashes and the letters in them error out. The import errors table shows all as "Type Conversion Failure". But it is only the ones with the Dashes and the Letters in them that are failing even though the field is a text field and is 255 characters long.
Thanks. I actually got it to work. Instead of checking the box for Headers I leave it unchecked and the import, imports the headers as well but the actual data imports clean with no errors. I just have to go and delete the Header row since it is the only row that errors out on import.