February 9, 2010 at 10:32 am
Hi,
The column 'Amount' is being imported as 'NULL'.
Attached is the excel sheet.
Please help me. I am unable to understand, what's wrong.
I see the column as 'NULL' in preview as well 🙁
Any help is appreciated!
Thank you.
February 9, 2010 at 10:50 am
You should start the values on the first cell(A1) in excel .
Also i would be reluctant to use a custom format in excel as this also may cause issues(?)
Fix the spreadsheet and try your import again, if there are any more issues then let us know.
February 10, 2010 at 4:14 pm
SSIS samples rows in Excel. It looks like it examines the first 8 rows.
Search for this phrase to learn more. 'ssis typeguessrows excel'
February 10, 2010 at 5:14 pm
Thanks Steve.
I am getting the excel sheet from the other team so could not try what you had suggested.
Thank you very much Emily. I wanted to try the below thingy first which worked otherwise your solution would be my last option.
Well, for the people who will face the issue, here is the solution:
The connection string property of the Excel mgr has to be changed to include IMEX = 1.
February 13, 2010 at 8:14 pm
Emily, want to write thanks again.
I had to implement your suggestion in order to import another Excel file.
I had to increase the 'TypeGuessRows' to 1000 since there were many records with no values at all.
It raises another Q. Does changing the 'TypeGuessRows' impacts the performance of the SSIS with other Excel files. I also have noticed that the type of column is DT_CY ( i.e. Currency). If I change the format of the column from 'accounting' to 'number', it is imported with 'TypeGuessRows' = 8, as well.
What's the issue with Excel, I have no clue, never had an issue like that before.
Is there any other way to solve the problem since these settings will have to be changed in the production server as well.
Thanks in advance.
February 14, 2010 at 12:51 am
touchmeknot123 (2/13/2010)
...Is there any other way to solve the problem since these settings will have to be changed in the production server as well.
Thanks in advance.
If you can use CSV source files instead, you will find that most of your problems go away.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 15, 2010 at 1:46 am
I almost killed my brain by trying to import Excel files in .xls format.
As Phil says. Use CVS or TAB separted format. Make a small makro in the Excel application.
Then use bulkinsert to import the textfile.
/Gosta M
February 15, 2010 at 9:28 am
Thanks Phil.
Gosta, it feels a lot better to know that others have issue as well and its just not I and my skills 😛 !
I can now conclude that the root cause is '.xls' :).
March 17, 2010 at 6:23 am
I'm able to import an Excel file on my local machine without any problems. However some of the columns are null when I run the code on the production server. I'm using IMEX=1.
Also MS Office is not installed on the production server. So it appears the production server is ignoring IMEX=1. So could the problem be that MS Office is not installed on the production server?
March 17, 2010 at 7:36 am
fm447k (3/17/2010)
I'm able to import an Excel file on my local machine without any problems. However some of the columns are null when I run the code on the production server. I'm using IMEX=1.Also MS Office is not installed on the production server. So it appears the production server is ignoring IMEX=1. So could the problem be that MS Office is not installed on the production server?
No, the installation of MS Office has nothing to do with SSIS importing Excel files correctly or incorrectly.
IMEX=1 tells the JET driver to import the column as text if there are mixed data types found in the row sampling. But how it behaves depends on some registry settings, which are likely to be different on the production server compared with your local machine.
For more information, see this URL:
http://swap.wordpress.com/2007/09/18/solution-for-excel-data-uploading-problem-using-ssis/
Oh yeah, @touchmeknot123:
the problem is not ".xls", the problem is the JET driver which speaks to ".xls". 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 17, 2010 at 7:57 am
Thanks for posting that link - good info.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 17, 2010 at 8:08 am
So just so I understand this the IMEX=1 is being overlooked because of the registry setting. Also how do I change the registry setting?
March 17, 2010 at 8:10 am
So just so I understand this the IMEX=1 is being overlooked because of the registry setting. Also how do I change the registry setting?
March 17, 2010 at 8:11 am
Sorry for the duplicate posts.
March 17, 2010 at 8:18 am
fm447k (3/17/2010)
So just so I understand this the IMEX=1 is being overlooked because of the registry setting. Also how do I change the registry setting?
Maybe. You should check your data to see whether the first 8 rows are all numeric and that would corroborate the possibility.
Use RegEdit to edit the Registry, but be very careful. If you change the wrong values, you can cause yourself real pain.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply