Problem in uploading excel data to SQL Server using SSIS

Question

Problem in uploading excel data to SQL Server using SSIS

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Microsoft's recommended method to get around the problem:

http://support.microsoft.com/kb/194124/EN-US/

You can add the option IMEX=1; to the Excel connect string in the OpenDatabase method. For example: Set Db = OpenDatabase("C:\Temp\Book1.xls", _

False, True, "Excel 8.0; HDR=NO; IMEX=1;")

NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.

The possible settings of IMEX are: 0 is Export mode

1 is Import mode

2 is Linked mode (full update capabilities)

The registry key where the settings described above are located is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

jaimeortiz2005 Valued Member Points: 52 More actions · Answer 2

By adding the IMEX=1 in the connection string of the excel connection manager as described on this KB, it fixed the issue.

Example:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\example.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

http://support.microsoft.com/kb/194124/EN-US/

Ed B Hall of Fame Points: 3665 More actions · Answer 3

This doesn't help, but this is the reason I believe that excel is the devil.

Excel 2007 does a much better job of handling text values that resemble numbers, but I do whatever I can to avoid receiving data in excel format. The biggest problem I have is that if the file contains medical data such as ICD9 diagnosis codes, often there are values such as '003.30' and '03.30', both of which excel will convert to 3.3.

You can make Excel behave properly, but chances are that anyone using excel to send data doesn't know how to.

patcom Newbie Points: 9 More actions · Answer 4

I had the problem of Integer values being imported form a spreadsheet as that meaningless scientific notation, while the alphanumeric values were fine. First 8 rows were aplphanumeric by the way, and the formatting appears to be ignored. Strangely enough if I keep the spreadsheet open and run the SSIS import the problem is solved. Not sure why but try it and see what happens

Gert-Jan

hbpank SSC Eights! Points: 828 More actions · Answer 5

Quite useful...

Thanks a lot all, very good contribution.

Harry

Thanks a lot,
Hary

DBA Cabuloso SSCommitted Points: 1733 More actions · Answer 6

Thanks l0n3i200n

Changing the register was the only thing that solved my problem.

I used the value F (which means 15)

DBA Cabuloso

________________
DBA Cabuloso
Lucas Benevides

jim-1129345 SSC Enthusiast Points: 176 More actions · Answer 7

I have exact same issue now. But look through all posts. It seems to me -- there is no real solution, right??? How about SQL Server 2008 that is able to fix this? Need big gruuu help!

bsuh_t SSC Enthusiast Points: 142 More actions · Answer 8

I had the same issue today I fixed it in my own dirty way but it worked and I hope it will help people out there like me :-).

Solution: I have the file like below, I copied all the records where ever there is a number with text into another sheet in the same excel file and uploaded separately.

orderid

12345

12356

11234-qwer

12345-qwer

kbatta SSCrazy Points: 2274 More actions · Answer 9

First, SSIS 2008 still has this problem.

We often receive Excel files where fields are strings of digits but leading zeroes must be preserved. There are also columns with mixed data types.

If the Excel file has headers, I do the following:

1) Use IMEX=1.

2) Define the sheet as not having headers. Then change F1, etc. to useful names.

3) In the data flow task I add a step to count the rows so I know which is the header.

4) Split out the header and check for reasonability. Sometimes the file format changes.

I have not found a good solution for the scientific notation bug though.

Paul Swinfield SSC-Addicted Points: 498 More actions · Answer 10

Have you tried creating a table using the excel sheet as a template? Often if you do this the data type will be varchar rather than nvarchar. I would be tempted to insert quotes around the excel values and ensure that the top value in the sheet is the largest alphanumeric value found in the sheet.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

If you add IMEX=1 to the connectionstring and set the TypeGuessRows registry property for the JET provider to 0 (meaning it will scan all the rows to determine the data type), most of the problems will go away.

There still can be some issues, such as the scientific notation. You could try to write a SQL query to the Excel file and use the FORMAT function to solve that issue.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ckishore 72107 SSC Journeyman Points: 82 More actions · Answer 12

[font="Arial"]try this one:

1)Make a view in the database having the same column names as in excel sheet.

2)copy the data in the excel sheet and paste it in view.

i think this simple steps will solve your problem:smooooth:

[/font][font="Arial"][/font]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

ckishore 72107 (5/17/2011)
[font="Arial"]try this one:
1)Make a view in the database having the same column names as in excel sheet.
2)copy the data in the excel sheet and paste it in view.
i think this simple steps will solve your problem:smooooth:
[/font][font="Arial"][/font]

You use this method to import flat files into the database?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ckishore 72107 SSC Journeyman Points: 82 More actions · Answer 14

[font="Arial Black"]can u post what is the issue?[/font]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 15

ckishore 72107 (5/17/2011)
[font="Arial Black"]can u post what is the issue?[/font]

This thread was started in 2007 and was picked up by numerous people over the years.

So the issues are spread out over the 3 different pages (depending on your settings).

Happy reading time!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP