Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gvdamn
Starting Member
2 Posts |
 Posted - 2009-07-01 : 05:58:09
|
| I'm trying to import data from Excel (2003) into SQL Server 2008 with Integration Services (SSIS). One of my columns contains mixed data. For example it contains numbers (like 265657) but also mixed data(like 26022F). Importing it straight away (into a nvarchar(255) field) results in a lot of NULLs. Converting the column in Excel to Text doesn't do the trick. Editing each cell (first converted to Text) individually (through clicking on F2 and Enter) does! do the trick. Thus Excel recognizes it as 'Number stored as Text' and the import works fine.My question: how can I make this process easier (so I don't have to individually edit each cell to be imported)? |
|
|
uberman
Posting Yak Master
159 Posts |
Posted - 2009-07-01 : 08:44:41
|
| Try reordering the xls spreadsheet to make sure that the first row of actual data contains the data with the alphanumerics rather than just numerics and then try the import again... |
 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-01 : 08:55:01
|
| HiAm not sure. but i read some where.....When SQL Server SSIS prepares for the transformation, it looks at the first 8 rows of the source data to determine how datatype for each field. |
|
|
|
gvdamn
Starting Member
2 Posts |
Posted - 2009-07-02 : 04:14:19
|
| Thank you both for your reply!@uberman: I tried your suggestion (with and without setting the format of the cells to text) but no luck I'm afraid.@rajdaksha: You might have come across the following link as I did: [url]http://sqlserversd.wordpress.com/2008/09/14/ssis-excel-values-import-as-nulls/[/url]. I set the registry value (I didn't try it without the registry setting) and added 'IMEX=1;' to the (Excel) connection string in BIDS (Business Intelligence Development Studio). This did the trick! |
|
|
|
|
|
|
Import (Mixed) Data from Excel