Menu
I'm trying to import a spreadsheet to our database using SSIS. For some reason SSIS wants to believe two of the columns are of type Double, when they contain character data. I've tried remapping the columns to be nvarchar(255) but it still doesn't want to select the data it thinks is double, because there are characters in it. If I try to edit the SSIS package and change the column types in the Excel Source, it won't let me change the type of the columns in the Error Output and gives me an error if the regular output and error output columns don't match.
Why is SSIS insisting that these columns are Double? How can I force it to realize these are strings? Why does everything from microsoft have to not quite work correctly?
EDIT:
I found this:
I sorted my data so that mixed data types would be at the top, and guess what: The problem reversed. Instead of not importing character data, it stopped importing purely numeric data. Apparently someone doesn't think 12345 can be represented as a string...
541
Yes, you can. Just go into the output column list on the Excel source and set the type for each of the columns. To get to the input columns list right click on the Excel source, select 'Show Advanced Editor', click the tab labeled 'Input and Output Properties'. You have more control over what you convert to.
The way Excel import works make it not possible to automate it. You have to modify most of the excel files manually in order to load them. This is why we are no longer using ODBC/OleDB/Ms Jet for Excel connections. Our ETL solutions work correctly with Excel all the time.
To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.”
In the Connection Managers area, right-click and select New connection. In the Add SSIS Connection Manager dialog box, select EXCEL and then Add. On the SSIS menu, select New connection. In the Add SSIS Connection Manager dialog box, select EXCEL and then Add.
This instruction tells Excel that the columns contain mixed data types and hints it to do extra checking before deciding that the column is a numeric type when in fact it's not. It should work with this (in most cases). The safer thing to do is export the Excel data to tab delimited text and use SSIS to import that.
You provide this info in the Excel Connection Manager Editor in an SSIS package, or on the Choose a Data Source page of the Import and Export Wizard. If you disable this option because the source data doesn't contain column names, the wizard uses F1, F2, and so forth, as column headings.
The connection information that you have to provide, and the settings that you have to configure, when you load data from or to Excel with SSIS. Specify Excel as your data source. Provide the Excel file name and path. Select the Excel version. Specify whether the first row contains column names.
For some reason SSIS wants to believe two of the columns are of type Double, when they contain character data. I've tried remapping the columns to be nvarchar (255) but it still doesn't want to select the data it thinks is double, because there are characters in it.
I've seen this issue before, it's Excel that is the issue not SSIS. Excel samples the 1st few rows and then infers the data type even if you explicitly set it to text. What you need to do is put this into the Excel file connection string in the SSIS package. This instruction tells Excel that the columns contain mixed data types and hints it to do extra checking before deciding that the column is a numeric type when in fact it's not.
;Extended Properties="IMEX=1"
It should work with this (in most cases). The safer thing to do is export the Excel data to tab delimited text and use SSIS to import that.
158
You can convert (ie. force) the column data to text... Try this (Note: These instructions are based on Excel 2007)...
The following steps should force Excel to treat the column as text:
Open your spreadsheet with Excel.
Select the whole column that contains your "mostly numeric data" by clicking on the column header.
Click on the Data tab on the ribbon menu.
Select Text to Columns. This will bring up the Convert Text to Columns Wizard.
-On Step 1: Click Next
-On Step 2: Click Next
-On Step 3: Select Text and click Finish
Save your Excel sheet.
Retry the import using the SQL Server 2005 Import Data Wizard.
Also, here's a link to another question which has additional responses:
Import Data Wizard Does Not Like Data Type I Choose For A Column
20
One thing that isn't mentioned in the accepted answer is that the "IMEX=1" parameter has to go inside the quoted portion of:
...;Extended Properties="...";
;IMEX=1; is not always working... Everything about mixed datatypes in Excel: Mixed data types in Excel column
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
