SQL Server Performance Forum – Threads Archive

Problem importing data from excel into SQL

Hi, FIELD SPECIFICATIONS Excel | SQL
——————-|—————————-
|
DrawingNo(general) | DrawingNumber(varchar(50))
When I am using DTS to import values from excel sheet to sql table. I am using the criteria that DrawingNo is not null than DTS is not inserting some of the records in the sql table though the DrawingNo is not NULL. eg: of the values it inserts
4587-sdfd-87
df.458/df.0.
ert-97-005 e.g. of values it doesnot insert as the transformation considers it as null. 789456
130456214
789456.1
I tried converting the DrawingNo in excel sheet from datatype ‘general’ to ‘text’ and it inserts them but now when I try converting the whole column of DrawingNo to text, it inputs some records but others it doesnot considering the drawingno field as a null value
I have in all 7000 records in 50 sheets of a workbook. Its difficult for me to find out the fields that are not being input into the sql table. Can anyone please help me with this as I couldnot make out what is wrong… excel sheet data type or sql data type or some other problem?… Thanks in advance.

---

This has to do with how excel determines what the data type of a column is. It only checks to fist 7 rows, so if the data in the first 7 rows is alpha numeric but later in the file this changes to all numbers you will get null values where the data is all numbers. This is a bug in the excel ISAM driver not DTS. I have found that formating the excel sheet to text instead of general sometimes works, but only if you format the sheet before it has any data. If there is already data you must format the sheet and re-enter the data. From my own experience this doesn’t always work however. If you can switch from excel to access DTS can import from access without these problems.

---

]]>