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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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?
Click to expand...

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
 
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
 
Back
Top