SSIS Excel Import Forcing Incorrect Column Type

Question

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     PRB  DTS Wizard may not detect Excel column type for mixed data in SQL Server   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

User · Accepted Answer

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    http   blogs acceleration net ryan archive 2005 01 11 477 aspx

User · Answer

I ve used the following recipe    Import data from Excel to Access Import data from Access to SQL Server   and it worked for me

User · Answer

I had the same issue  multiple data type values in single column  package load only numeric values  Remains all it updated as null    Solution  To fix this changing the excel data type is one of the solution  In Excel Copy the column data and paste in different file  Delete that column and insert new column as Text datatype and paste that copied data in new column   Now in ssis package delete and recreate the Excel source and destination table change the column data type as varchar   This will work

User · Answer

I had the same problem  The problem sit in the Excel Source task  When you setup this task the first time  the task will connect to the specified Excel file  via the Excel connection  and decide what type each column is based on the current spreadsheet    Thus  if you set up the Excel Source task  just make sure that the columns that should be text only has text in the column  This means that the Excel Source task will always assume that any subsequent spreadsheets will have the same format and will read 12345 as text because the column was text when the task was set up   Hope it makes sense

User · Answer

Click File on the ribbon menu  and then click on Options  Click Advanced  and then under When calculating this workbook  select the Set precision as displayed check box  and then click OK  Click OK  In the worksheet  select the cells that you want to format  On the Home tab  click the Dialog Box Launcher  Button image next to Number  In the Category box  click Number  In the Decimal places box  enter the number of decimal places that you want to display

User · Answer

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

User · Answer

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

User · Answer

I was banging my head against a wall with this issue for a while   In our environment  we consume price files from our suppliers in various formats  some of which have upward of a million records   This issue usually occurs where    The rows scanned by the OLEDB driver appear to contain numbers  but do contain mixed values later on in the record set  or Fields do contain only numbers  but the source has some formatted as text  usually Excel files     The problem is that even if you set your external input column to the desired data type  the file gets scanned every time you run the package and is dynamically changed to whatever the OLEDB driver thinks the field should be   Our source files typically contain field headers  text  and prices  numeric fields   which gives me an easy solution   First step    Change your SQL statement to include the header fields   This forces SSIS to see all fields as text  including the price fields    For mixed fields    Your initial problem is solved because your fields are now text  but you still have a header row in your output  Prevent the header row from making it into your output by changing the SQL WHERE clause to exclude the header values e g   WHERE NOT  F4   Price      For numeric fields    Using the advanced editor for the OLE DB source  set the output column for the price field  or any other numeric field  to a numeric DataType   This causes any records that contain text in these fields to fail  including the header record  but forces a conversion on numeric values saved as text  Set the Error Output to ignore failures on your numeric fields  Alternatively  if you still need any errors on the numeric fields redirected  remove the header row by changing the SQL WHERE clause to exclude the header values then  Set the Error Output to redirect failures on this field    Obviously this method only works where you have header fields  but hopefully this helps some of you

User · Answer

Well IMEX 1 did not work for me   Neither did Reynier Booysen s suggestion    I don t know if it makes a difference but I m using SQL Server 2008r2    A good explanation of some workarounds and also some explanations of why IMEX 1 is limited to the first eight rows of each spreadsheet can be found at  http   social msdn microsoft com Forums en-US sqlintegrationservices thread 78b87712-8ffe-4c72-914b-f1c031ba6c75  Hope this helps

User · Answer

This worked for me  Select the problematic column in Excel - highlight the whole column  Change the format to  Text   Save the Excel file   In your SSIS package  go to the Data Flow pane for your import  Double click the Excel Source node  It should warn you that the types have changed and ask you if you want to remap them  Click Yes  Executing should now work and bring in all values   Note  I m using Excel 2013 and Visual Studio 2015  but I assume these instructions would work for earlier versions too

User · Answer

If multiple columns in the excel spreadsheet  present with the same name  this kind of error occurs  The package will work after making the column name s distinct  Sometime the hidden columns are being ignored while checking the columnn names

User · Answer

You can also alter the registry to look at more values than just the first 8 rows  I have used this method and works quite well   http   support microsoft com kb 281517

User · Answer

Another workaround is to sort the spreadsheet with the character data at the top  thereby causing Excel to see the column as string  and importing everything as such

User · Answer

Option 1  Use Visual Basic to iterate through each column and format each column as Text   Use the Text-to-Columns menu  don t change the delimination  and change  General  to  Text

User · Answer

IMEX 1  is not always working    Everything about mixed datatypes in Excel  Mixed data types in Excel column

User · Answer

It took me a bit to realize the source of the error in my package   Ultimately I found that data was converted to null  Example  from  06  to  NULL    and I found this via Preview in the source file connection  Excel Source gt  Edit gt  Connection Manager gt  Sheet  MySheet  gt  Preview       I got excited when I read the post by James to edit the connection string to have extended properties   Extended Properties  IMEX 1    But that did not work for me     I was able to resolve the error by changing the Cell Format in Excel worksheet from    Number    to    Text      After changing the format  the upload process ran successfully   My connection string looks like  Provider Microsoft ACE OLEDB 12 0 Data Source   myServer d  Folder1 Folder2 myFile xlsx Extended Properties  EXCEL 12 0 XML HDR NO    Here is are some screenshots that resolved my error message   Error  Metadata of Excel file connection   Source of error     General    format   Source of error changed     Text    format   Error fixed  Metadata of Excel file connection

[sql-server] SSIS Excel Import Forcing Incorrect Column Type

Examples related to sql-server

Examples related to excel

Examples related to types

Examples related to ssis