I have an excel file that, at least to me, is pretty simple. It's got 8 tables with about 25 records each. I'm trying to import 2 of those tables. Everytime I try to import I get this message:
Column "Name" cannot convert between unicode and non-unicode string data types.
So I've tried every combination of import/export that I can think of here and stil I get this message. Now I really don't think that I have any oddball characters in my data. As a matter of fact there isn't even any punctuation in there. And yet, SQL thinks there's some unicode in there. My dest column type for Name is a varchar(max). No issues there. So this is really about the simplest sort of data transfer that I can think of.
And yet it doesn't work. Which leads me to my question: did anyone at the big M actually use this wizard before they shipped it? No really. I'm doing a very simple data transfer and I'm gonna have to resort to some custom code to import my data? I've successfully imported data into the database, but I had to do it one table at a time (csv), because of the above unicode issue. I have foreign key constraints here, so I'm gonna have to import all this data in a very specific order, one table at a time.
One really absurd oversight is the names of the tables. Excel seems to save table names with a $ after. But the wizard doesn't seem to understand that. So every time I have to tell it that the Company$ table in excel actually needs to point to the Company table in SQL. Huh.
I could've saved myself some time if that I/O wizard just wasn't there.
One more thing, did they think that I might want to import more than one table to the same database via a csv file? No, they didn't because the wizard, once completed, only gives you the option of exiting. You can't hit "Back" and do the same thing with a different flat file. You have to restart the wizard and re-connect to the database etc. etc. Start from scratch. So I'm wasting even more time because of this thing.
Wait, it gets worse!!!
Now I'm trying to export the database to an excel file and I'm getting the same message!
How can an export wizard not know how to export varchar fields? Possibly the second most common field type, right? No, they aren't nvarchars, they're varchars.
Now I'm gonna have to custom code this ***. Just so I can export a freakin varchar field.
|||Can you supply some more specifics? I'm not exactly a MS lover, but I have used the Import/Export wizard on a wide variety of data sources and have seldom been totally stymied. I have encountered some difficulties with Excel imports, mostly around fields being truncated due to formatting.
Can you isolate the data that it is barfing on? It is also possible to specify the output type. You can also adjust the query used to select the data.
Repeating an import/export is also a pet peave of mine, but it is possible to save the definition as a DTS package and get reuse there. Also, the list boxes are good at remembering your previous choices, so the repetition can be slightly less tedious.
Like I said, I'm usually the first to bash the products that MS has "discovered" from others, but Import/Export is a place I usually give them their props.
|||Here's a list of more problems I'm encountering:
- wizard is locking the excel file so that I can't edit it, sometimes locking it regardless of where I am in the process of importing (even locked on the pick a file screen sometimes)
- wizard is encountering truncation errors on fields that are not included in the import (marked as <ignore> in field mapping)
- encountering a truncation error on a column that is defined as nvarchar(max) in the database
The bottom line: I'm giving up on using this wizard with an excel file. After changing every text field in the database to nvarchar(max) I'm still not able to import fields that have a grand total of one character in them due to truncation errors.
There is simply nothing special about this data. No symbols. It's all the keys that you see on your keyboard and not even the ones above the numbers (!@.#$ etc). I've blown too much time trying to get this wizard to do something extremely simple.
|||anomolous wrote:
Can you isolate the data that it is barfing on? It is also possible to specify the output type. You can also adjust the query used to select the data.
Repeating an import/export is also a pet peave of mine, but it is possible to save the definition as a DTS package and get reuse there. Also, the list boxes are good at remembering your previous choices, so the repetition can be slightly less tedious.
Like I said, I'm usually the first to bash the products that MS has "discovered" from others, but Import/Export is a place I usually give them their props.
It's barfing on any text field.
I think the DTS package save will only work for specific values, right? Like I'd really like to be able to just hit <BACK> a few times, change the flat file that I'm using then hit forward and so on. Without having to re-select the db and password. Of course this would all be a moot point if I could get the freakin excel import to work.
Reply if you want to, but I've decided using this wizard to import an excel file is a waste time. Now I get to use flat files, which means I have to go through this wizard 12 freakin times.
And this textbox sucks too. I just inserted a smily and it deleted half of a paragraph!
No comments:
Post a Comment