nulls when import data from excel using dts

Question

Post reply

nulls when import data from excel using dts

Christos Siamentzas

SSC Enthusiast

Points: 139
More actions
October 18, 2007 at 3:18 am

#179508

Hallo everybody
I' m trying to import data from an excel file which contains data with general format, to a table using dts package. I' m using a MS excel connection and a Data Transformation Task to import the data. I' looking the table in th DB and i realized that when the data of a column are empty for at least 8 first cells, the package converts during the execution all row' s data as null. I' cant finger out a sollution in even if i had tried a lot. I put in the IMAX = 1 in the extended properties, i changed the format of the data.....nothing.
The problem sold when i put a value in the first cell of column but i want to avoid it.
Does anyone have any suggestion? Thanks.

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

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

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 1

We had a similar problem where it was seeing numbers in the first load of rows and assuming it was a numeric column.

We fixed it by setting the following registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]

"TypeGuessRows"=dword:00000000

(may have to do Jet 3.5 as well. I don't have Excel 2007 yet, so I don't know the key for this).

This forces it to look at all the rows before deciding what type it is, so there are probably performance implications.

Christos Siamentzas SSC Enthusiast Points: 139 More actions · Answer 2

Hi Andrew,

I changed the value of the TypeGuessRows and it worked perfectly. Thank you very much for your response.

Thank you again!

AK007 SSC Veteran Points: 213 More actions · Answer 3

Andrew

I had changed Registry setting, restarted PC and changed columns in excel spreadsheet in Text format. I'm using Jet 4.0 driver and SQL Server version 2000.

However It does not work for me. I still see null values in DTS Preview or Import to a table.

Anything I'm missing!

Thanks

AK007 SSC Veteran Points: 213 More actions · Answer 4

In addition to what Andrew had suggested, you may need to do following

Right click on empty space in your DTS designer window

Choose "Disconnected Edit..."

Open up the connections

Open up your Excel Connection

Look in the OLE DB Properties of the connection

The tenth item in the list is Extended Properties

Now look across to the right hand pane and you will see a table with two columns. The one we want has value in the leftmost column and mine has "Excel 8.0;HDR=YES;"

Double click on the value and change the string to read "Excel 8.0;HDR=YES;IMEX=1"

Christos Siamentzas SSC Enthusiast Points: 139 More actions · Answer 5

hi,

I'm trying to make a text file with the help of dts. First i have an excel file and i import it's data into the DB. I'm using an excel connection, a DB connection and for the destination text, a text(destination) connection. I' m using a query to take the data from the db with the help of Transform Data Task. When i have to define the destination columns i can't find any of them. If i press the Define Columns button then the Sql Server Enterprise Manager shows the below error:

"Microsoft Management Console has encountered a problem and needs to close. We are sorry for the inconvenience."

Any suggestions. It seems very strange.

Thank you.

myra_gilbert Newbie Points: 7 More actions · Answer 6

Thank you! This worked for me as well. Much appreciated.