Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    25

    Unanswered: bulk insert problems

    hi

    situation is like this:

    I have csv file I would like to import into table. However I have problems with code page 852 so I changed it to 850. Here is the command

    bulk insert ticket_dump_remedy from 'c:\work\ticket_dump_remedy_040308.csv'
    with
    (
    CODEPAGE = 850,
    FIELDTERMINATOR =',',
    ROWTERMINATOR ='\n'
    )

    After that execution ends with error:
    Bulk insert data conversion error (type mismatch) for row 2, column 2 (Create Date).

    Create Date is defined as datetime....

    Example of file row is this one...
    "Case ID+","Create Date","Type+"
    "APHD00000031378","12/2/2003 10:13:00 AM","General"

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, so you've got bad data....

    Load the data to a stage table that has varchar for all data types

    The use ISNUMERIC and ISDATE to test to see the bad rows....

    ALTER The table after the load to add an identity column to confirm the row the badat data is on
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    25
    thank you for your help and time

    but this csv file comes every day and my goal is update database according to this file so I will have to look for another solution

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you have a process to audit the file?

    How big is it?

    Do you have headers and trailers with the data?

    Since it's csv, does it have a column list in row 1?

    If you make a stage table, all varchar, you can check the file out before you apply it.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Posts
    25
    it's file about 3000 rows

    unfortunatelly I don't have any process to check if the csv is ok...

    I will have xls file with the same data so probably I will try to connect to xls file via ODBC and get data from there....hopefully it will work..


    anyway thank you for your time

  6. #6
    Join Date
    Mar 2004
    Posts
    25
    I have tried the excel file and it's working.... I converted csv to excel and than uploaded data from there and somehow it's working. Don't ask me how...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •