| Author |
Topic |
|
Maquis
Starting Member
25 Posts |
 Posted - 2003-10-21 : 10:27:38
|
| I am importing some Excel files, doing a straight import, mapping all columns to nvarchar fields. I have a zip code column, which contains Canadian zip codes which include letters, and I also have a phone number column, most of which contain parentheses and/or dashes. My import process is a straightforward copy column from source to destination. My problems are: the Canadian zips are not imported in the zip column, and any phone number that is entered in Excel as a plain number (without parens or dashes) is not imported. These fields are left as nulls. What's up with that? How do I get the Excel fields into my table? I tried specifically setting the Excel columns to type text (it was general), but it didn't help. Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-21 : 12:24:05
|
| We would need to see the DDL of your table to help. Also an example of a couple of rows from your Excel spreadsheet would help.Tara |
 |
|
|
Maquis
Starting Member
25 Posts |
Posted - 2003-10-21 : 13:16:49
|
| It looks like it's just Excel thinking it knows more than the user does, if I use a tab-delimited text file for the source data, it imports properly. Guess I'll go that way. Thanks. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-10-21 : 20:03:35
|
I think it's not really a DDL issue Tara, although that would be nice for most other posts around here  Anyway, it sounds to me like a variation on this issue http://support.microsoft.com:80/support/kb/articles/Q281/5/17.ASP&NoWebContent=1Basically, when Excel gets imported, DTS scans 8 (by default) rows to determine what sort of data is in there. If what happens after those 8 rows is wildly different, your import will fail or otherwise do weird stuff. You can make a registry change (in the article) to scan all rows.Damian |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-21 : 20:06:18
|
| I just wanted the info so that I could duplicate the problem on my machine.Tara |
|
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2003-11-07 : 00:34:54
|
| I am having the same problem with alphanumeric fields. I have tried the regedit fix but that still does not seem to work. Has anyone got any other ideas??? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-11-07 : 00:42:20
|
| I have gotten around this by entering a dummy row with correct data types. You can then simply hide that row from the user. We delivered it to clients as a template with that row locked and protected....DavidM"SQL-3 is an abomination.." |
|
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2003-11-07 : 00:48:20
|
| I have figured out when the error occurs.... create an excel file with the following:Field1 Field2a 123ab 1234c 12345cd 123456e 1234567eTry and import this and row b and d have no value for field2. I have tried to manually change the format of column2 but it doesn't work. The only way I could fix it was to add a single quote at the front.... but as I have thousands of records this is not an option. |
|
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2003-11-07 : 00:51:05
|
| I have tried that also, still does the same thing. |
|
|
|
|
dropping data on importing Excel files