Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I sum columns with cells that contain alphanumeric data? | Excel Worksheet Functions | |||
Problem importing data into sql server database | Excel Discussion (Misc queries) | |||
Importing Text file from a server | Excel Programming | |||
Importing Data from a Stored Procedure (SQL SErver) | Excel Programming | |||
Importing data from an Excel file on a web server using ADO/VBA | Excel Programming |