asked
Import Data from Excel
Hi All, i have an excel sheet with id name age profession as fields and a table with same names id, name,age profession. As we know the data in Excel will be in nvarchar, how would we load it into table in sql server 2008? I want to do identity insert into table. The error i get is potential loss of data(for age field) while doing the import data from SSMS. sample data id name age profession 1 sam 25 sql server developer 2 david null null 3 john 12 null 14 jim null sql server dba how to convert the nvarchar to int while loading(also tried using ssis with data conversion still no luck)? Thanks!
How you do it really depends on which tool you prefer to use, and they are legion. I frequently use opendatasource which will let you cast the incoming data much as you would from a standard sql table. The data import wizard, which utilizes SSIS, gives you options to convert the data as you go through it. Incidentally, data stored in excel is not necessarily treated as nvarchar, that depends on how the column (or even group of cells if you set it that way) was defined for excel. Also, some tools will try to do things like use the first few rows of data to guess at the datatype for that column. I know you mentioned you tried SSIS and it did not work, but could you be a little more specific about how it failed?
I have taken Excel source and pointed to the excel sheet. Then i connected it to Data Conversion transformation. In Data conversion transformation i have changed id data type from DT_WSTR to DT_I4, age data type DT_WSTR to DT_I4. When i run the package it fails at Data Conversion Transformation and when i looked in the progress tab, the error says the potential loss of data. I guess may be due to Null's in the age ?
If the value in the age column isn't real NULL value, but is a "null" literal, then you will not be able to cast the "null" literal to an integer value and will not be able to use the Data Conversion Transformation for this purpose. If there are a "null" literals in the [age] column, then use a Derived Column Transformation and put expression for new [age] column as `[age]="null" ? NULL(DT_I4) : (DT_I4)[age]`. This will check for the "null" literal and in this case will return a real NULL integer and in other cases it will cast the number to integer. The same approach you can use ag. for the column [profession] if there is "null" literal representing NULL value and not the NULL value itself.
