Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

nulls when import data from excel using dts Expand / Collapse
Author
Message
Posted Thursday, October 18, 2007 3:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:02 AM
Points: 3, Visits: 48
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
Posted Friday, October 19, 2007 5:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 1,298, Visits: 2,270
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
Posted Friday, October 19, 2007 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:02 AM
Points: 3, Visits: 48
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
Posted Wednesday, July 9, 2008 5:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, July 9, 2008 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, July 23, 2008 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:02 AM
Points: 3, Visits: 48
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
Posted Tuesday, October 14, 2008 9:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse