Problem in uploading excel data to SQL Server using SSIS
-
Actually while loading data from Excel source to SQL destination I’m facing one problem that one source column in excel say Customer_Id contains both Numeric and Alphanumeric type data. While loading this data to SQL, some times values are populated as NULL in SQL instead Customer_Id.
I set nvarchar as Datatype for Customer_Id column in SQL.
I would appreciate if i get solution for this problem.
--Sundar
-
Format the column in excel to be a text column and then upload the same to sql server with your column datatype as varchar/nvarchar.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Perhaps you can set the input column properties to varchar/nvarchar by right clicking on the tool.
Anil
-
I tested this already, even after changing(changed excel column type to text) this i am facing this problem.
--Sundar
-
The Excel column format is ignored, changing it makes no difference. Exporting to CSV may not be appropriate either.
If you want a quick and dirty fix for this, try putting an embedded space within the string of numbers - I'm importing account numbers & sort codes from Excel worksheets, usually 50 or so rows at a time, and it works fine. If I get more rows than this, then I create a new column and populate it using an expression to give substring & space & substring, then remove the space during processing of the staging table.
But that's quick and dirty, I'm sure someone here will come up with something more elegant.
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
The first thing I would do would be to add a data viewer to the flow to see that the data is actually getting pulled out of excel correctly. (You can do this by right clicking on the flow line between two tasks in the data flow task and picking Data viewers)
If it is OK there - I would then apply a Data conversion to change the data type to the format you require within the data flow.
You can add another data viewer after the conversion to check that the data is now OK, before it hits SQL.
Kindest Regards,Catherine Eibner
cybner.com.au -
I have had this problem. I cheated and opened the dtsx file in notepad and modified the data type of the field in the source component's definition. Once it saw the field as a character field in the Excel document, it worked fine.
Save a copy of the dtsx file before doing this.
After having done this, it occurred to me that if the first record in the spreadsheet had character data in it, the SSIS designer would probably figure it out correctly when it first connects.
-
I had this same problem. I found that if the column did not have any data in the first 9 rows, it would not import. I found the write up blow in this blog (http://www.cubido.at/Blog/tabid/176/EntryID/79/Default.aspx). I know the blog is for exporting, but the row count was the thing that caught my eye
Re: How to format columns when exporting to excel in SSIS
You cannot define the external data type of the Excel column in SSIS. You can change it, but at Run time the external JET driver, that actually reads the Excel will allways reset the data types. For this the JET driver takes the first 8 rows as sample and from this it determines wether the rows are of a numeric, text, date, etc type. This becomes a problem, when you have a mixed typed coulmn, that has text and numeric values too. Like if you have just numeric values in the first 8 rows and afterwards you have string values too, then the type will be set to a numeric type and all the sting type values will be lost. The workaround this is to increase the number of rows that are sampled from 8 to a higher number. This all can be done only in the registry.
In my case i just had the user put a value in the first row. I hope this helps.
-
hello everyone
I had the same problem. What I did was pull atleast 2rows which has char value in that field that ur talking abt and put it as the 1st 2 rows in xls file and then try using cast(field as varchar)
It worked for me. So first thing I do is open excel file and sort to see if I have any rows with char value in that field and put that rows as first few rows.
try this.
-
Hi,
I tested this by adding data viewer, in data viewer itself NULL is populating.
Please provide solution for this case.
-SPS
-
Hello,
I tried with this but still is not resolved...
-SPS
-
Hi,
I tried as you explained but the problem is not resolved.
-SPS
-
Hi Mr S
For each column in Excel which has mixed data types, you must import to char or varchar. Force the cell contents to be a text type by preceeding the cell value with an underscore, or by embedding a space (or something obvious like ¬). I normally do this manually because my source spreadsheets are small, but you can use something like ...
=MID(A1, 1, 2)&" "&MID(A1, 3, 8)
... if you have a lot of rows to deal with. Format the Excel column to text.
After importing to your staging table, remove the space with REPLACE(columnname, ' ', '').
If your Excel column is numeric, you must ensure that the first eight values are all readable as numeric: if you have a cosmetic gap between your heading and your values in a numeric column, you must put in zeros.
You may find it convenient to add an extra worksheet to your files which has the data formatted to your advantage - call it, say, 'Upload', leaving the source worksheet in the file unaltered.
This works for me ... I'm importing typically 3 spreadsheets every day: names & addresses, bank details, stock quantities. Note that you may experience similar problems working the other way - exporting to Excel.
HTH
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Hi a quick and easy why of solving this problem is to tweak your excel drives a bit. Open regedit and find the following key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel'. Find the 'TypeGuessRows' and change it to something bigger thant the default 8 rows. This will allow excel to 'guess' more than 8 row and thus allowing it to convert to varchar instaid of the first 8 (e.g 1-8 numeric sql will assume a int column)
,cheers
l0n3i200n
-
Just replace the existing value by embedding a ' (quote) befor the value, all the data is taken as alphanumeric. You need to do that for the entire column.
-- Thanks
Vinodh
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply