Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Amethystium
Aged Yak Warrior
701 Posts |
 Posted - 2003-09-15 : 08:50:56
|
| Hi guys,am I correct in saying that DTS is full of bugs and MS did a terrible job releasing a tool that does not function correctly?I have a problem(s) especially with importing data from Excel files. It often skips data in columns and I am sure of that because I tried it again and again. In fact I had to convert the excel file to a .CSV in order for the DTS transformation task to function correctly without skipping any data.Has anybody experienced similar problems in the past?__________________Make love not war! |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-09-15 : 08:57:42
|
DTS is great! I think the problem you are having is related to the Excel driver... which does suck.There was the full answer on another site which I belive is no longer online, it has to do with the number of rows that are tested to find the field size.I'm going to find the answer.... gimme 10 minutes  Damian |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-09-15 : 09:05:06
|
| Back!Go to HKEY_Local_Machine\Software\Microsoft\Jet\4.0\Engine\Excel and change the TypeGuessRows key to a value of 0.This is the number of rows it tests to define how big a varchar field is. The default on W2k was 8, so the field got set to the size of the biggest value in the first 8 rows. Which generally doesn't mean squat. Setting the registry value to zero means the whole set is tested before any big decisions are made.Does that make any sense ?Damian |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-09-15 : 10:12:15
|
| Thanks Damian.I did what you suggested and after re-starting my machine, I tried importing the same excel file but the results are the same as before.Column 1, 2, 3 and 4 are populated in full but column 5 (the 15 row) contains a null instead of the actual value that exists in the excel sheet (at the same position). Actually there 6 values missing out of a possible 96. I've checked the length of the data missing and none of them seem to be different from those that are loaded successfully.As I said, converting the document to .CSV format seems to do the trick but now I am worried about my other 30 excel documents. To be honest I am too far in to this and I don't have the time to convert every single one to .CSV format.Any more suggestions?__________________Make love not war! |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-09-15 : 10:18:32
|
| Me again,I’ve just noticed something about the 6 values missing. The column in question contains PPS Number (or National ID Numbers). Anyway, PPS Numbers is what they call them in Ireland.The 6 that are being let out are purely numeric. While a PPS Number is suppose to end with a letter, as in 6767631A, the ones missing are purely numeric values.Therefore, why is the transformation task assuming that only alphanumeric fields should be imported? What am I doing wrong?Thanks in advance.__________________Make love not war! |
|
|
|
sphadke
Yak Posting Veteran
55 Posts |
Posted - 2004-04-02 : 11:35:21
|
| Try saving the Excel document as a text file. It worked for us when we imported huge excel spreadsheets Sachin |
|
|
|
|
|
|
|
|
DTS problems