Problems with import - help

[sqlcasey]

Hi,

I am having issues trying to import an Excel spreadsheet into a table in a SQL Server 2005 database. I do this ALL the time, and don't seem to recall ever having this problem using import in 2000.

However, for some reason, if I try to import a really long text field into a varchar(8000) column, I keep getting errors that it's truncating the column, and the import fails. I seriously cannot believe that this column holds more than 8000 characters. Also because I've seen other people post questions about this exact same problem (in 2005) on columns much shorter than mine.

Does anyone know why this problem is occurs in 2005? The new import interface seems to be a little buggy to me.

How can I work around this? Will BCP work on Excel spreadsheets? I used to use BCP in 6.5 but it's been sooo long I don't even remember how to use it.

THANKS

 

[Catadmin]

I don't know the answer to your question, but I have a somewhat silly suggestion. Have you tried importing from a Text field to a Text field?

If the import is creating your table, you shouldn't have a problem leaving the datatype the same, I would think.

Just out of curiosity, what version of 2k5 are you running? RTM or SP1 or SP1 + SP1 Hotfix?

Yes, there is a hotfix for SP1. Ridiculous, isn't it?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

Yes, I've tried every combo you can think of - text to text, text to ntext, text to varchar(4000), (8000), etc

It just plain old doesn't work

As I've said, I don't recall ever running into this problem in 2000, not to mention I've seen plenty of other posts about this exact problem which leads me to think it's a bug

Meanwhile I am going to use bcp, fingers crossed

 

[ptheriault]

I ran into the same problem. I saved my spread sheet as a .CSV file and the import ran. I'm not sure why.

- Paul
- Database performance looks fine, it must be the Network!

 

[sqlcasey]

Hi - csv didn't work for me either - I select "flat file" as the file type, then change the column types to match correctly, but no dice. Kept getting strange truncation errors on int fields, even after I changed the target column type.

 

[Catadmin]

Can you post the exact error you're getting?


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

I finally had to use BULK IMPORT to get the data into the table. New "import" tool in 2005 bites. Works ok if you're not doing anything fancy, ferget it if you need to work with blobs. YMMV.

 

[sqlcasey]

Just wanted to update - the import started working for reasons I cannot explain, it seems very happy now

 

[Catadmin]

Aha! I know what the problem is. You got the Gremlin version of SQL Server when you bought it. @=)

Glad it's working for you now. If you ever figure out what the problem was, please let us know. I'm interested in hearing the answer.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"