Excel imported to Access (1 Viewer)

[rodin]

Hi,

I have a table in Excel, and when I import the table in Access, some records from a specific field shows #Num! after importing, even before I imported the table, I selected the entire field in Excel and I Formated all cells to text.

The specific field hosts records in digit format[34222256], and sometime in mixed letter-digit format[M22228778], but are supposed to be as TEXT! After importing I see the first as #Num!, and the second as M22228778.

How can I get around this issue in Excel or Access.

Thank you!

 

[gemma-the-husky]

the trouble is that the access import wizard in excel doesn't give you any options. if it thinks a column is a number column (probably from the first item), it acts accordingly.

I find the only way to go with this is to save the xls as a csv, and THEN import the csv into Access, as you can then create an import spec, and you get much more control over it. Its awkward if you are trying to automate something though.

 

[neileg]

I agree with gemma but there is a trick you might try. Forget formatting since that doesn't change the data only the display. Create a new first record in your spreadsheet and fill this with dummy data that is unambiguously the datatype you want, import the data and then delete the first record.

For example
Text field, enter TEXT
Integer, enter 1234
Date, enter 1 Jan 2006
Decimal number, enter 1.23456

This always seems to work for me.

 

[gemma-the-husky]

good thinking, Neil - thats one for the future then.

 

[Shawnus-Maximus]

If those excel fields are the result of formulas, then...

I recently had the same problem. I copied the column in excel and pasted it as a Value. Then when I imported into access I did not have the #Num! problem. Changing the format in excel to text doesn't change the formula's result into text, and unless you edit the cell it stays in the General format.

Have not tried the other suggestions, but will keep them in mind.

Else
Ignore this post.

SM

 

[boblarson]

Or, you can do what I do in those cases. I forget about using the import feature completely and just bring the data into the table via an ADO connection and reading through the Excel file using the Excel Object Model. In my case, I didn't want to touch the Excel file to add a dummy row, or turn it into a CSV, as I was importing 140 spreadsheets a month and wanted to completely automate the process.

Check out this post for an abbreviated example of how I was doing it:
http://www.access-programmers.co.uk/forums/showthread.php?t=117520

 

[rodin]

Thank you guys!