nulls when import data from excel using dts

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » nulls when import data from excel using dts

nulls when import data from excel using dts

Rate Topic

Display Mode

Topic Options

Author

Message

Christos Siamentzas

Posted Thursday, October 18, 2007 3:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 03, 2012 6:52 AM
Points: 3, Visits: 41

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.

Post #412102

Andrew Watson-478275

Posted Friday, October 19, 2007 5:28 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:44 AM
Points: 953, Visits: 1,875

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.

Post #412691

Christos Siamentzas

Posted Friday, October 19, 2007 7:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 03, 2012 6:52 AM
Points: 3, Visits: 41

Hi Andrew,

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

Thank you again!

Post #412808

AK007

Posted Wednesday, July 09, 2008 5:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 21, 2011 6:07 AM
Points: 3, Visits: 90

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

Post #530644

AK007

Posted Wednesday, July 09, 2008 6:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 21, 2011 6:07 AM
Points: 3, Visits: 90

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"

Post #530712

Christos Siamentzas

Posted Wednesday, July 23, 2008 9:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 03, 2012 6:52 AM
Points: 3, Visits: 41

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.

Post #539411

myra_gilbert

Posted Tuesday, October 14, 2008 9:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 27, 2009 1:27 PM
Points: 1, Visits: 3

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

Post #585916

« Prev Topic | Next Topic »

Permissions