Excel Import

[tstoneh]

Hello Out there,

Trying to import an Excel Spreadsheet that keeps getting errors when I import it. I don't think the data is that bad but it keeps kicking out some of the information into the errors file. Some of the data has alpha charaters and numeric data. As an example it looks like this. N46467644. Then there is data without a alpha character like 978695413. That ones with alphas show up on the error report. I thought I could change the field options data type but I can't get it to activate. Just wondering if you would know what I need to do to get the field options to activate or maybe you would have a bettter way to do this?

Thanks,

 

[Dugantrain]

TStoneh,
What you're dealing with is a very common problem; Access makes guesses about the data types of the Excel columns based on the first 10 or so fields of data and doesn't allow you to alter its guesses. There are numerous ways to avoid this, but all are fairly complicated work-arounds:
-Insert a new row into the Spreadsheet and type "X" into every field. This way, everything will import as text and, when it's actually in Access, you can automate deleting that "X" row and then use C functions (CLng, CDate, etc.) to massage the data and get it into the type that you want.
-Use Excel automation (using the Excel Object Library from within Access via VBA) to perform row by row validation of the spreadsheet to get a handle on exactly what data type each column is.
-Maybe the easiest, if you have this option, is to use a text file rather than an Excel spreadsheet to import data. Access will allow you to define the data type for each field. You could actually convert your .xls file to a .csv file and even though it still "looks" like a spreadsheet, Access will read it as a text file.

 

[tstoneh]

Ok thanks I will try it. Why do you like pants?

 

[Dugantrain]

I posted why a year or so ago

 

[Norie]

You could try importing into a existing table which has the fields you want with the data type you want.

eg set the type of the field causing the problems to Text