importing alphanumeric columns from excel into SQL-Server or MS-Ac

[Guest]

I have an Excel sheet containing variable length alphanumeric productIDs. The
productIDs always start with digits and may end with hyphen and characters,
eg. "12345-ABC".

When importing the sheet using
- MS-Access Import
- MS-SQL Server 2005 Import
- my own code using ADO.NET 2.0 via ODBC provider
I loose various parts of the table:

I set the Excel format to "text". However all the above mentioned tools
treat the column as Double/Decimal and do not let me change it.
(SQL Server import replaces the alphanumeric productID by NULL, MS-Access
and ODBC ignore the rows completely.)

How do I teach Excel to present the column as "text" to others?

thanks herbert

 

[Jan Karel Pieterse]

Hi Herbert,

set the Excel format to "text". However all the above mentioned tools
treat the column as Double/Decimal and do not let me change it.
(SQL Server import replaces the alphanumeric productID by NULL, MS-Access
and ODBC ignore the rows completely.)

How do I teach Excel to present the column as "text" to others?

I suspect the first couple of records only contain numeric codes, right?

AFAIK, both import routines (MSAccess and MSSQL) use the first n rows to
determine filed type.

What happens if you prepend all codes with a single apostrophe:

'1234
'2345
'3456
....

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Guest]

Thank you very much - issue solved.

1) I had to insert *more than one* rows with a character productID, I use
ten rows. One row is definitely not enough.

2) Using '1234 in Excel creates a String '1234 (inlduing the ') in SQL
Server using its Import tool.

thanks again. Herbert