June 24, 2007 at 7:28 pm
Hi All,
I tried to import data from an excell spreadsheet into sqlserver. The weird thing happened to me if data has both numeric and text data, sql server will import only numeric data, but not text data.
Ex: 12345
123A4
2342
B01E
Only 12345 and 2342 were imported successfully.
The text data was set to null, eventhough I format the column in excel as text data. If I did the same thing with text file, I did not see that problem. All data was imported fine. Is there a way to work around of this problem?
Thanks so any help!
Minh Vu
June 24, 2007 at 10:51 pm
In the excel set the column that has those value to be of type text and in sql server use the datatype either char/varchar to import the data and that should import fine.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 25, 2007 at 1:17 am
My guess is that excel autmatically decides on the data type and hence if it finds a numeric datatype in the first 8 rows it assumes that the remaining rows will contain the same data type and hence when it finds a non numeric data type it is imported as null. Another point to be noted is that once u change the cell in excel to have text property u need to reenter the data otherwise it will still consider it as a numeric value.. i.e. change the property of the cell to text format and re enter the data.. hope this will solve your problem.
June 25, 2007 at 6:13 am
I have same problem and i tried by setting my table field as varchar and excel sheet field as text but still have null values. So i am running differents files for char and numeric data.
Shankar is right....Excel pick up data type.
June 25, 2007 at 7:49 am
The Excel column format is ignored, changing it makes no difference. Exporting to CSV may not be appropriate either.
If you want a quick and dirty fix for this, try putting an embedded space within the string of numbers - I'm importing account numbers & sort codes from Excel worksheets, usually 50 or so rows at a time, and it works fine. If I get more rows than this, then I create a new column and populate it using an expression to give substring & space & substring, then remove the space during processing of the staging table (REPLACE(columnName, ' ', '')).
But that's quick and dirty, I'm sure someone here will come up with something more elegant.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply