SQL Server Performance

Help, Problem importing from Excel into SQL 2000

Discussion in 'SQL Server DTS-Related Questions' started by pharoah35, Jun 27, 2003.

  1. pharoah35 New Member

    Hello<br /><br />I am attempting to import data from an Excel spreadsheet into SQL 2000<br />I have 1 spreadsheet with 6 column headings<br /><br />ProdID, Customer, Vendor, EffDate, EndDate, FixedPrice<br /><br />I SQL Table with the same columns<br /><br />ProdID int <br />Customer nvarchar<br />Vendor varchar<br />EffDate datetime<br />EndDate datetime<br />FixedPrice money<br /><br />When I attempt to import the data from my spreadsheet into the SQL table using DTS, I get the following error:<br /><br />Error during transformation 'DirectCopyXForm' for row number 66.<br />Errors encountered so far in this task: 1<br />TransFormCopy 'DirectCopyXForm' conversion error:<br />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))<br /><br />Is there another way to import data into a SQL table which has a no NULL.<br /><br />Any help concerning this issue would be greatly appreciated.<br />Thanks in advance<br /><br />pharoah35
  2. bambola New Member

    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.
  3. gaurav_bindlish New Member

    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
  4. vbkenya New Member

    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.
  5. bambola New Member

    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.
  6. mani_hafeez New Member

    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
  7. gaurav_bindlish New Member

    See my previous post.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. AllyAnneA New Member

    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.
  9. dwaltuck New Member

    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
  10. dktiger New Member

    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
  11. elin New Member

    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
  12. ynons New Member

    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.
  13. Dave Wells New Member

    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])
  14. vinitha New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by pharoah35</i><br /><br />Hello<br /><br />I am attempting to import data from an Excel spreadsheet into SQL 2000<br />I have 1 spreadsheet with 6 column headings<br /><br />ProdID, Customer, Vendor, EffDate, EndDate, FixedPrice<br /><br />I SQL Table with the same columns<br /><br />ProdID int <br />Customer nvarchar<br />Vendor varchar<br />EffDate datetime<br />EndDate datetime<br />FixedPrice money<br /><br />When I attempt to import the data from my spreadsheet into the SQL table using DTS, I get the following error:<br /><br />Error during transformation 'DirectCopyXForm' for row number 66.<br />Errors encountered so far in this task: 1<br />TransFormCopy 'DirectCopyXForm' conversion error:<br />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))<br /><br />Is there another way to import data into a SQL table which has a no NULL.<br /><br />Any help concerning this issue would be greatly appreciated.<br />Thanks in advance<br /><br />pharoah35<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
  15. vinitha New Member

    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
  16. satya Moderator

    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.

Share This Page