Importing Mixed Data from Excel Worksheet.

Question

Post reply

Importing Mixed Data from Excel Worksheet.

Viewing 7 posts - 1 through 8 (of 8 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: 247183 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.

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: 247183 More actions · Answer 5

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

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: 247183 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 🙂