November 25, 2003 at 7:29 am
I using a Transform Data Task to import import data from an Excel file into a table.
One of the columns in the Excel file contains both numeric data and character data but only the character data is appearing in the table - the numerics are replaced with nulls.
The column in the table is varchar so should take both values without any problems.
Any ideas?
Jeremy
Edited by - Jeremy Kemp on 11/25/2003 07:32:24 AM
November 25, 2003 at 9:49 am
Jeremy,
You should be able to preview data in your DTS. Can you preview it to see it's that's all it is putting? sometime it think that the other stuff at the end belong to another column. You might want to modify it or drag the dividing line to the end of the line instead of cutting that column in half.
mom
November 26, 2003 at 1:36 am
When I use preview to look at the data, the numeric data is missing. The first row is column headers, the second row has a character value in this column (which is correctly inserted) but the third row has a numeric (which becomes null).
How do I drag the dividing line when I am inserting directly from Excel? DTS doesn't seem to give me any options for that. I have seen it for importing text files. The file comes in as Excel and I want to eliminate all manual intervention so that SQL Server processes the file as is.
Jeremy
November 26, 2003 at 3:30 am
Some info here, although not good news I'm afraid:
If the workaround give here is unacceptable, you might be able to fudge it by applying a macro onto the excel sheet that explictly converts all numeric values to character data in the affected columns (I know you don't want to play these games if you can avoid them, but maybe the producer of the sheets can do it for you?)
November 26, 2003 at 3:33 am
More info (untested).
November 26, 2003 at 3:40 am
Thanks. I've gone with the option of saving the file as a csv and then importing that.
If I get some more time, I will try some of the other options (re-entering data is not an option!!).
Jeremy
November 26, 2003 at 11:55 am
Whenever I import data from Excel, especially using DTS, I open the doc and import it using COM. The "feature" you're dealing with is MS's way of optimizing ODBC data transfers. Another way I've gotten around this is to add an apostrophe in front of each value in the column I'm trying to import. That forces Excel to report them to ODBC as all text.
September 20, 2005 at 2:19 pm
Tried it...didn't work in my case
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
September 21, 2005 at 6:03 am
Have you tried using the Excel formatting options on the column? It sounds as if it set to General. Try setting it to Text.
In your transform, force it to be a non-variant data type by using this:
Destination("my_column") = CStr( "" & Source("Col002").Value )
-Mike Gercevich
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply