Import SSIS Excel to force column type input

Question

I am trying to import a spreadsheet into our database using SSIS. For some reason, SSIS wants to believe that two columns are of type Double when they contain character data. I tried reassigning the columns as nvarchar (255), but he still doesn’t want to select the data which, in his opinion, is doubled because it has characters. If I try to edit the SSIS package and change the column types in the Excel source, it will not let me change the column type in the error output and will give me an error if the usual output and error output columns do not match.

Why does SSIS insist that these columns are Double? How can I make him understand that these are strings? Why should everything Microsoft needs to work properly?

EDIT: I found this: http://support.microsoft.com/kb/236605

I sorted my data so that the mixed data types are at the top, and guess what: the problem has changed. Instead of importing character data, he stopped importing purely numeric data. Apparently, someone does not think that 12345 can be represented as a string ...

Answer 1

I saw this problem before, this is Excel, this is not a SSIS problem. Excel displays the first few rows and then enters the data type, even if you explicitly set it in the text. What you need to do is put this in the connection string of the Excel file in the SSIS package. This command tells Excel that the columns contain mixed data types and hints at the need for additional validation before deciding that the column is numeric if it is not.

;Extended Properties="IMEX=1" 

It should work with this (in most cases). A safer thing is to export Excel data to tab delimited text and use SSIS to import.

• http://blogs.acceleration.net/ryan/archive/2005/01/11/477.aspx

Answer 2

You can convert (i.e. force) the column data into text ... Try this (Note: these instructions are based on Excel 2007) ...

The following steps should force Excel to treat the column as text:

Open a spreadsheet using Excel.

Select the entire column containing your “mostly numeric data” by clicking on the column heading.

Click the "Data" tab in the feed menu.

Select text in columns. This will create a wizard for converting text to columns.

-On Step 1: Click Next

- In step 2: click "Next"

-On Step 3: select the text and click "Finish"

Save the excel sheet.

Retry the import using the SQL Server 2005 Import Data Wizard.

There is also a link to another question that has additional answers:

The data import wizard does not like the data type that I select for the column

Answer 3

One thing that is not mentioned in the accepted answer is that the parameter "IMEX = 1" must be inside the specified part:

 ...;Extended Properties="..."; 

Answer 4

; IMEX = 1; Doesn't Always Work ... All About Mixed Data Types in Excel: Mixed Data Types in an Excel Column

Answer 5

Another workaround is to sort the spreadsheet with character data at the top, causing Excel to see the column as a row and import everything as such.

Answer 6

You can also modify the registry to view more values ​​than just the first 8 lines. I used this method and it works quite well.

http://support.microsoft.com/kb/281517

Answer 7

Well IMEX = 1 did not work for me. Rainier Boyzen's proposal was not there either. (I don't know if this matters, but I am using SQL Server 2008r2). A good explanation of some workarounds, as well as some explanations of why IMEX = 1 is limited to the first eight rows of each table, can be found at http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/78b87712- 8ffe-4c72-914b-f1c031ba6c75

Hope this helps

Answer 8

I used the following recipe:

• Import data from Excel to Access
• Import data from Access on SQL Server

and it worked for me ...

Answer 9

I banged my head on the wall for a while with this problem. In our environment, we use valuable files from our suppliers in various formats, some of which have more than a million records. This problem usually occurs where:

• Lines scanned using the OLEDB driver appear to contain numbers, but later contain mixed values ​​in the recordset or
• Fields contain only numbers, but the source has a format 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 will be scanned every time you run the package and dynamically change it to what OLEDB thinks, this field should be.

Our source files usually contain field headers (text) and prices (numeric fields), which gives me an easy solution:

First step:

• Modify the SQL statement to include header fields. This strengthens SSIS to see all fields as text, including price fields.

For mixed fields:

• Your initial problem has been resolved because your fields are now textual, but you still have a title bar in your release.
• Prevent the inclusion of the header line in your output by modifying the SQL WHERE clause to exclude header values, for example. "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) for the numeric data type. This causes any records containing text in these fields to be rejected, including a header record, but forced conversion of numerical values ​​stored as text.

• Set the error output to ignore failures in numeric fields.

• Also, if you still need any errors in the redirected numeric fields, delete the header row by changing the SQL WHERE clause to exclude the header values, then

• Set the error output to redirect failures in this field.

Obviously this method only works where you have header fields, but hopefully this helps some of you.

Answer 10

Option 1: Use Visual Basic to iterate over each column and format each column as text.

Use the "Text-Columns" menu, do not change the separator and change "General" to "Text"

Answer 11

I had the same problem. The problem is the Excel Source task. When you first configure this task, the task will connect to the specified Excel file (via an Excel connection) and determine what type of each column is based on the current spreadsheet.

Thus, if you configured the Excel Source task, just make sure that the columns that should be text only have the text in the column. This means that the Excel Source task will always assume that any subsequent tables will have the same format and will read 12345 as text because the column was text when the task was configured.

Hope this makes sense!

Answer 12

I had the same problem, multiple data type values ​​in one column, only numeric values ​​to load. Everything remains updated as null.

Decision

To correct this change, the excel data type is one solution. In Excel, copy the column data and paste into another file. Delete this column and paste the new column as a text type and paste this copied data into a new column.

Now in the ssis package, uninstall and recreate the Excel source , and change the destination table to the column data type as varchar .

This will work.

Answer 13

If multiple columns in the Excel spreadsheet have the same name, this error occurs. The package will work after creating the column name. Once hidden columns are ignored when checking column names.

Answer 14

• Click File on the ribbon menu, then click Options.

• Click Advanced, and then below. When calculating this book, check the box "Set accuracy as shown" and click "OK."

• Click OK.

• On the sheet, select the cells you want to format.

• On the Home tab, click the image of the Start button in the dialog box next to Number.

• In the Category field, click Number.

• In the Decimal places field, enter the number of decimal places that you want to display.

Answer 15

It worked for me. Select the problem column in Excel - select the entire column. Change the format to Text. Save the Excel file.

In your SSIS package, go to the data flow panel to import. Double-click the source Excel node. He should warn you that the types have changed and ask you if you want to reassign them. Click Yes. Execution should now work and enter all values.

Note. I am using Excel 2013 and Visual Studio 2015, but I believe that these instructions will work for earlier versions.