Results 1 to 9 of 9

Thread: Import datetime

  1. #1
    Join Date
    Sep 2004
    Location
    Mansfield, OH
    Posts
    22

    Question Unanswered: Import datetime

    I am having a problem importing data from a csv file. I am trying to import into a table with numeric and datetime columns, but it errors out everytime. Is there a way to accomplish time? I am not very familiar with Microsoft SQL, so hopefully this can be accomplished.

    I am not sure how to approach this. Any help is greatly appreciated. Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What are you using? DTS, BCP, BULK INSERT?

    What errors are you getting?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Sep 2004
    Location
    Mansfield, OH
    Posts
    22
    Trying just normal import. Right click database -> All Tasks -> Import Data.
    Going from csv file into already established table. I have attached the error box.
    Attached Thumbnails Attached Thumbnails sql_error.JPG  

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Import it to a new table...let dts create the table for you, then check out the datatypes...sounds like your mappings are off...

    I usually always create a staging table for imports...that way I can audit/sanitize the input files.

    If tere's something wrong you can stop it there, BEFORE you hose the good data
    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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You get bad data ?!?!

    Oh the shame! Your users don't always provide you with nice, clean, carefully QA'ed data in their files? Especially CSV files ?!?

    Ok, so I'll climb down off of my soap box now...

    As Brett observed, it is a lot safer to just bring the data wholesale into a staging table, so that you can inspect it carefully, and if it is found wanting you can dump it without a second thought. I generally presume that data is absolutely worthless until after I prove otherwise. More often than not I'm wrong, but not a whole lot more often!

    -PatP

  6. #6
    Join Date
    Sep 2004
    Location
    Mansfield, OH
    Posts
    22
    Actually, the data in question is the original data from the table. Someone decided to screw with constraints to allow some new data to be imported and ended up with duplication. Due to some other constraint, the duplicates could not be deleted (I didn't see the error). So the data should be OK.
    The import utility won't take a field from the csv file (that was exported from that table) and import it back into the numeric or datetime field in the table.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Whenever I do a raw import from a CSV file, I get the columns processed in ascending order. Based on the error message that you posted, you appear to be using a transformation that processes the columns in descending order.

    Are you using a custom transformation? If not, which Enterprise Mangler / SQL Service pack are you using?

    You also processed 91 rows Ok, and 92 went "toes skyward" on you. I'd investigate the CSV file, looking hard at row 92.

    -PatP

  8. #8
    Join Date
    Sep 2004
    Location
    Mansfield, OH
    Posts
    22
    Thank you all for your help. Too much frustration made me rush through things. Come to find out, after importing the csv file into a new table (all columns vchar), the users inputed commas in some of the descriptions (one of the columns). This of course forced additional columns to be created and unmatched datatypes within certain columns.
    Being the only DBA here for MSSQL, Oracle, Sybase, and MySQL, I have no one to work through these things with locally.
    Thanks, to the forums and your responses.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You are NOT alone

    "mind what you learn here...save you it will...."

    Oh, and "trust no one Dr. Jones..."
    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.

Posting Permissions

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