SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


nulls when import data from excel using dts


nulls when import data from excel using dts

Author
Message
Christos Siamentzas
Christos Siamentzas
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 87
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.
Andrew Watson-478275
Andrew Watson-478275
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1757 Visits: 2699
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
Christos Siamentzas
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 87
Hi Andrew,

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

Thank you again!
AK007
AK007
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
AK007
AK007
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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"
Christos Siamentzas
Christos Siamentzas
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 87
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
myra_gilbert
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
Thank you! This worked for me as well. Much appreciated.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search