SQL Server Performance Forum – Threads Archive

Help, Problem importing from Excel into SQL 2000

Hello I am attempting to import data from an Excel spreadsheet into SQL 2000 I have 1 spreadsheet with 6 column headings ProdID, Customer, Vendor, EffDate, EndDate, FixedPrice I SQL Table with the same columns ProdID int Customer nvarchar Vendor varchar EffDate datetime EndDate datetime FixedPrice money When I attempt to import the data from my spreadsheet into the SQL table using DTS, I get the following error: Error during transformation ‘DirectCopyXForm’ for row number 66. Errors encountered so far in this task: 1 TransFormCopy ‘DirectCopyXForm’ conversion error: Destination does not allow NULL on column pair 1 (source column ‘ProdID’ (DBTYPE_R<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, Destination column ‘ProdID’ (DBTYPE_14)) Is there another way to import data into a SQL table which has a no NULL. Any help concerning this issue would be greatly appreciated. Thanks in advance pharoah35

If you can keep null values in your table, you can change your table definition. If you want to specify a value in case of null use ISNULL. If you do not wish to import those NULL rows, you can write your own query and
add where column_name IS NOT NULL. Bambola.

I think there is some problem in the data in the excel sheet. Try just importing the data into a table with all columns nullable. After you import the data, you can analyze if some fields were not imported successfully. Look at the Excel File and see if any chnages are required. Once you become convinced that results are fine, just go ahaead and do it for the main table. Gaurav

Bambola and Gaurav are both correct but you should clean up your Excel file first before importing the data. I would hope that you are conscious of data integrity issues because I am sure that a record wouldn’t be valid (in your case) if there was no value for the ProdD. If the excel file is OK then you wouldn’t have to modify anything on the SQL server side. SQL Server and your initial table design are just trying to reinforce your own business rules. Regards, Nathan H.O.

I think I did not explain myself clearly. The problem is that you are trying to insert a NULL value into a column that does not allow NULLS.
As to how to solve it, refer to my previous post. Bambola.

Hi I am facing the same problem with excel file. Somehow I am getting error message whenever he try to map the 2nd last row.I ahve checked the excel file and it looks to me fine. Can anyone suggest me what should i do.
Mansoor

See my previous post. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Sorry to come late to this discussion– You are using a data pump, I assume? Open up its properties, go to the Options tab, and uncheck "Keep NULL values". The data pump is doing a BULK INSERT if you have "fast load" checked. See BULK INSERT, default values in Books Online.

I have had the same problem importing columns that contain both text and numeric data from Excel 2000 to SQL 2000 tables where that column is varchar. I’ve found you can get past it by selecting the appropriate column in Excel, then go to Data, Text to Columns. Step 1-Delimited. Step 2-deselect everything. Step 3 (THIS IS THE IMPORTANT PART) – Set Column data format to TEXT, and click FINISH. For some reason, just highlighting the column and formatting it as text doesn’t work.
Hope this helps! Dan
Austin, TX

Hi, Importing Excel sheet to SQL Server, you must check the definition of objects like table. The column "ProdID" must be not allowd with the value NULL.(Maybe you configured w/ primary key.) For the consistency of the database, you need to keep the constraint. In my opinion, When you use the Data Transforming service, you must check the NULL value by using the function "isnull". In other way, before transforming, check the spread sheet whether the ProdID has space or not. Thanks. Ted Kim
Sr. System Engineer
CJ Systems

Hi,
I had the same problem importing Excel data to SQL 2000 db, mostly because of the date fields containing null and other text data. I agree it’s the best practice that you clean up and format the Excel data first. If you’re importing the date field as text field to sql, Access’s importing wizard still won’t like it. I’ve tried to format the date column to ‘general’ verses ‘date’, but the date imported as something like ‘789098’. After numerous trial-and-error, I sometimes could change the first two row’s values of the date field to text and got the importing finished successfully but sometimes I still couldn’t. I had to isolate each date column to test the importing so that I could make sure where the problem was. Later, I found out I could easily use SQL’s import wizard through the enterprise manager and get the data imported without any problem. Boy, I learned it the hard way. elin

Hi Bambola,
I have an Excel that i have imported from the sql server,
i used column_name IS NOT NULL.
after manipulatnig the excel i’m trying to update the sql server data respectily and get the same NULL exception.. Do u have a lead?
Thanks in advance

quote:Originally posted by bambola If you can keep null values in your table, you can change your table definition. If you want to specify a value in case of null use ISNULL. If you do not wish to import those NULL rows, you can write your own query and
add where column_name IS NOT NULL. Bambola.

There’s 2 issues that I know of which commonly affect Excel import. 1) Excel uses the first 5 rows of a column to determine the data type of that column. So if the first 5 rows for example all look like integers then the data type will be an integer. Any string later on in the column will be treated as NULL. 2) non-latest version with up to date service pack of Excel quite often gets confused thinking that a blank row is part of the datasource. This can happen when deleting / inserting rows AND columns. The only way I’ve found to fix the data after this happens is to highlight all of the blank rows and delete them (that is delete the rows not the data [right click and delete])

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by pharoah35 Hello I am attempting to import data from an Excel spreadsheet into SQL 2000 I have 1 spreadsheet with 6 column headings ProdID, Customer, Vendor, EffDate, EndDate, FixedPrice I SQL Table with the same columns ProdID int Customer nvarchar Vendor varchar EffDate datetime EndDate datetime FixedPrice money When I attempt to import the data from my spreadsheet into the SQL table using DTS, I get the following error: Error during transformation ‘DirectCopyXForm’ for row number 66. Errors encountered so far in this task: 1 TransFormCopy ‘DirectCopyXForm’ conversion error: Destination does not allow NULL on column pair 1 (source column ‘ProdID’ (DBTYPE_R<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, Destination column ‘ProdID’ (DBTYPE_14)) Is there another way to import data into a SQL table which has a no NULL. Any help concerning this issue would be greatly appreciated. Thanks in advance pharoah35 <hr height="1" noshade id="quote"></blockquote id="quote">

hi, I am trying to import from an Excel Sheet to SQL table using DTS. When I import all the blanks are getting converted to NULL. I cant delete the whole row because some of the columns have data. BUt can someone tell how to keep blanks as blanks Appreciate your time and help

Have you check what has been refered above?
Why don;t you open a new thread for the problem, rather than getting the old thread. As it confuses and diverts from your problem. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>