Importing Mixed Data from Excel Worksheet.

Question

Post reply

Importing Mixed Data from Excel Worksheet.

SQLFanatic

SSCommitted

Points: 1640
More actions
October 29, 2009 at 9:15 am

#139712

[font="Arial"][font="Comic Sans MS"]Hi all,
I am importing the data from the Excel Sheet(97-2005) into SQL 2005 Table using SSIS. The Excel Sheet has an AddressNo. column which has some mixed data records down at 500th row and so on such as
[font="Comic Sans MS"]
a) 1234
b) 67.28
c) N.456
d) s-345
I tried the Import/Export wizard and by telling it to create a new table in SQL to see the Implicit data conversions. It created the column in SQL table with datatype 'float'. When I executed the package though it was a success but all those values with mixed data were shown as 'NULL' in SQL Table except the decimal values. I tried after changing the REGISTRY value for TypeGuessRow more than 8 and using 'IMEX=1' parameter in the Extended Properties of the Connection String. No Luck ! :(. So, How can I get exact same values in SQL as in Excel column?
Here's the Connection String - Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\DOWNLOADS\Project Florida\From karen\Leads\MG\FNIC20091001.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";
Any comments and help would be truly appreciated.
Thanks 🙂[/font][/font][font="Arial"][/font][/font]
________________________________________________________________
"The greatest ignorance is being proud of your learning"

Viewing 8 posts - 1 through 7 (of 7 total)

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

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 1

I think you need to change the AddressNo attribute from FLOAT to VARCHAR .

This should fix your problem.

Hope this helps,
Rich

[p]
[/p]

SQLFanatic SSCommitted Points: 1640 More actions · Answer 2

I changed it to nvarchar(255) but again with NO LUCK !!

Thanks for your reply.

________________________________________________________________
"The greatest ignorance is being proud of your learning"

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

No - those approaches won't work. The data doesn't even make it into the data pipeline.

The Excel driver 'decides' on a datatype for every field as it is pushing the data into SQL Server (string or numeric or whatever) and if it encounters anything which is not of that datatype, it just throws in the towel and brings in a NULL instead.

You need to force the data in Excel to be all text (and not by formatting it, which just affects display) - possibly by using the TEXT() function - and then use that data. Or use a CSV file as your source - much cleaner.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SQLFanatic SSCommitted Points: 1640 More actions · Answer 4

The excel file is part of the daily import process which is pulled from a FTP location. I think I shall give it a try using Flat File or like you said CSV. It already took much of my time in finding the records in question in a large amount of data and testing again and again to get it going.

I thank you for your valuable reply.

________________________________________________________________
"The greatest ignorance is being proud of your learning"

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

No problem & good luck. Too many people have wasted too much time on this Excel data-typing / NULLing issue.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

SQLFanatic SSCommitted Points: 1640 More actions · Answer 6

Hi Phil,

Actually it worked !! and left me amazed.

Here's what I did.

I edited the Registry value of TypeGuessRows to 0, Decimal this time and made sure that ImportMixedType is set to 'Text'

Then I added that IMEX=1 in Extented Properties of the connection string.

The column datatype was set to nvarchar(255) in SQL table and metadata was refreshed in BIDS.

Voilla !! got the exact same data as in Excel..

The problem is with jet guessing the datatypes.

Thanks.

________________________________________________________________
"The greatest ignorance is being proud of your learning"

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

Get that man a beer :exclamation::exclamation::exclamation::exclamation:

I am impressed & will no doubt be checking out this thread again when next I have to import from Excel 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin