January 16, 2006 at 12:27 pm
| Jan 16, 2:19 pm |
| From: "LadyReader" <mfriedent...@netzero.com> - Find messages by this author |
| Date: Mon, 16 Jan 2006 11:19:50 -0800 |
| Local: Mon, Jan 16 2006 2:19 pm |
| Subject: Importing data |
| Reply | Reply to Author | Forward | Print | Individual Message | Show original | Remove | Report Abuse |
I set up a table with 3 fields, all defined as varchar(50). The first
field is called "WC", the 2nd field is "Description" and the third
field is "Department". I right-clicked on the table and selected "All
Tasks" and "Import Data". I selected an Excel spreadsheet as the data
source. I clicked my way through the wizard, selecting the destination
table and a 1-for-1 transformation. 175 rows were copied.
However, while it appears that for those rows where WC was numeric, WC
was copied correctly, for those rows where WC was not numeric, WC was
created as <null>. Coincidentally all rows that had non-numeric WC
values also had <null> Description fields.
Can anyone explain why WC isn't being imported correctly and what I need
to do to fix it?
Thanks.
January 16, 2006 at 12:59 pm
Is it possible that when you went through the wizard, you made the WC column datatype specific?
What I mean is, at some stage do you indicate that WC is anything other than General (sorry, been a while since I imported from Excel).
January 16, 2006 at 1:07 pm
When importing from Excel, the driver reads a small number of rows from the worksheet and makes a best guess as to their datatype. In this case, it is probably assuming that all your WC values are numeric, and then substituting NULL when it eventually runs across an alpha numeric value.
It's a known issue and there is a workaround:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q194124
January 16, 2006 at 1:25 pm
PW - thanks for letting me know this is a known issue and that I am not doing something wrong. I will try to re-enter the data after reformatting the column, as recommended in the link you sent me.
January 16, 2006 at 2:06 pm
PW - I tried the first suggested resolution in the link you posted, but it did not work. The resulting table still had <null>s where real values ought to be.
Since the number of affected columns is relatively small, I thought I might manually enter the WC values for those rows. However, SQL won't let me do that, saying that there are too many rows affected and made mention of a key - but no fields were defined as keys for this table. Does the first field default to the table's key?
The 2nd suggested resolution has the user changing the db defaults and the registry which I don't want to do as there are many other tables and users which could be affected.
Can you suggest anything further?
Thanks!
January 16, 2006 at 2:35 pm
Melody,
A couple of thoughts:
You could sort the data in the XLS file so that the WC column has some alphanumeric data in the first few rows, and then import it.
Another thing you can do is create a new table before you do the import. Create an id column, say, wcID, make it an int, NOT NULL and IDENTITY (1,1) and primary key. Then when you import, select the new table as the destination and use the Transformation button when you specify tables.
Un-check "enable identity insert" and then you should be ok.. but if you can't sort the Excel file you will still have some NULLs, but you'll be able to update the table manually now that you have a primary key.
Greg
January 16, 2006 at 2:50 pm
Greg - thanks for your time and suggestions. Before I saw your post I did the following: I added an identity field, imported the data (the result set was still wrong) and then manually went through and updated the "bad" rows - about 20 out of 170, so that wasn't too bad.
January 19, 2006 at 3:56 am
You might also try formatting the column as text in Excel before importing. We sometimes get a similar problem (but the other way round) where numeric columns containing commas (eg 123,000.00) get treated as text.
(Marvin)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply