a_d_m_i_n
asked on
(bug?) Excel not importing properly into SQL server
There definitely appears to be a bug in SQL Server 2000 importing a spreadsheet of data into SQL Server.
I have tried to connect to the Excel spreadsheet and import the data and some (10%) of the text fields are coming in as NULL.
I tried to connect to the spreadsheet and export to a CSV file and the same few fields are NULL.
I created a link table in an Access Database created a query that queries the data from the linked table (excel spreadsheet) and connected to the Access Database in the DTS and results are still NULL in SQL Server 2000 (they show up properly if I open the query in Access).
Has anyone else run into a bug with SQL Server 2000 and Excel? Does anyone know of a way around this?
Thanks.
I have tried to connect to the Excel spreadsheet and import the data and some (10%) of the text fields are coming in as NULL.
I tried to connect to the spreadsheet and export to a CSV file and the same few fields are NULL.
I created a link table in an Access Database created a query that queries the data from the linked table (excel spreadsheet) and connected to the Access Database in the DTS and results are still NULL in SQL Server 2000 (they show up properly if I open the query in Access).
Has anyone else run into a bug with SQL Server 2000 and Excel? Does anyone know of a way around this?
Thanks.
Can you post some of the problem data? What's the datatype of the column that's changing to NULL?
ASKER
The column is all text. I played around with the column data type, but it didn't make it any better. The data is for instance:
606512
435115
PURD80
590221
and the in that type of situation, strange data would not import (like 435115). Very strange.... No reason I can see that this particular field gets NULL'ed.
Thanks.
606512
435115
PURD80
590221
and the in that type of situation, strange data would not import (like 435115). Very strange.... No reason I can see that this particular field gets NULL'ed.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ahh... very helpful..
Your suggestion fixed most of the problems, but I still have a small issue that maybe you can help me fine tune the connection.
After applying what you mentioned, here are the results.
I have 180 successful imported fields and 12 that fail. I appears that when the number is one character more than the usual, it does strange stuff to the data.
SOURCE DATA
---
689781
688182
4765430
351714
3010015
459930
696600
---
IMPORTED DATA
---
689781
688182
4.76543e+006
351714
3.01002e+006
459930
696600
---
Your suggestion fixed most of the problems, but I still have a small issue that maybe you can help me fine tune the connection.
After applying what you mentioned, here are the results.
I have 180 successful imported fields and 12 that fail. I appears that when the number is one character more than the usual, it does strange stuff to the data.
SOURCE DATA
---
689781
688182
4765430
351714
3010015
459930
696600
---
IMPORTED DATA
---
689781
688182
4.76543e+006
351714
3.01002e+006
459930
696600
---
try to format the column in excel if you have not. can't think of anything else at the moment.
HTH,
</wqw>
HTH,
</wqw>
ASKER
OK
Thanks
If we put a single quote as the first character, it ignores the quote and brings in the field properly. I guess we will just do that.
Thanks for you help.
Thanks
If we put a single quote as the first character, it ignores the quote and brings in the field properly. I guess we will just do that.
Thanks for you help.
Only a grade B? I thought that was an excellent answer--I've run into this before myself; now I finally know why. :-)
ASKER
Sorry... I didn't know the grade meant that much....
I have only used this service a few times.
I have only used this service a few times.