importing alphanumeric columns from excel into SQL-Server or MS-Ac
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 |
importing alphanumeric columns from excel into SQL-Server or MS-Ac
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 |
importing alphanumeric columns from excel into SQL-Server or M
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 |
All times are GMT +1. The time now is 01:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com