none
Large CSV with bad data rows, import wizard, no BIDS

    Question

  • Hello everyone,

    I've been busy with the import wizard provided by sql server 2008r2 to import data from csv-files.

    Sometimes the files can be pretty big: 2GB+

    Also sometimes there are bad rows e.g. triple " when the text qualifier is a ", a ? instead of an empty field->convert to numeric error, etc.

    So sometimes the import jams on these rows. Of course I have to lookup the row so I can fix it. Sql server tells you the datarow on which it jams, so in smaller files you can lookup the bad rows to troubleshoot the bad data and fix it. Now with big files this is kinda of a problem, because you can't simply open the file. In that case how can i find out the values of the bad row?

    Tip: For replacing strings/characters in large files you can use FART (find and replace text - command line app), very fast and it does the trick.

    Thx for you suggestions

    Tuesday, December 11, 2012 8:38 AM

Answers

  • At the end of the wizard, you can choose to save the package instead of running it. Save it to your desktop, edit it in BIDS and route the error rows to another destination.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Goqu_green Tuesday, December 11, 2012 10:58 AM
    Tuesday, December 11, 2012 10:48 AM
  • Hey guys,

    I saw this thread again and wanted to add that I found another solution. VIM editor is nice editor that can handle big files. You can even switch on row numers. So if SQL server tells you which row jams the import. You can look it up with VIM.

    greets


    • Marked as answer by Goqu_green Wednesday, January 30, 2013 2:43 PM
    • Edited by Goqu_green Wednesday, January 30, 2013 2:44 PM
    Wednesday, January 30, 2013 2:43 PM

All replies

  • I suggest you redirect the error to a file, then you can clean the file and reload it.
    Tuesday, December 11, 2012 9:08 AM
  • Hey,

    Yea I thought about doing something like saving the bad row seperately and ignore it for the rest of the proces. But I think this flexibility is only possible in BIDS where you can catch the bad rows and create different paths. But in import wizard of sql server management studio, where can I perform such an action? I was looking at one of the last screens 'review data type mapping' (sorry account not verified yet, so i can't post a picture)

    Do I have to look elsewhere or isn't this possible in the import wizard.

    thx

    Tuesday, December 11, 2012 9:44 AM
  • At the end of the wizard, you can choose to save the package instead of running it. Save it to your desktop, edit it in BIDS and route the error rows to another destination.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by Goqu_green Tuesday, December 11, 2012 10:58 AM
    Tuesday, December 11, 2012 10:48 AM
  • Ok so it is obligatory to involve BIDS in this process... I thought maybe it was possible in SSMS only.

    Good to know and thx!

    Tuesday, December 11, 2012 10:57 AM
  • Yes, you will have to customize it.

    Tuesday, December 11, 2012 11:15 AM
  • Hey guys,

    I saw this thread again and wanted to add that I found another solution. VIM editor is nice editor that can handle big files. You can even switch on row numers. So if SQL server tells you which row jams the import. You can look it up with VIM.

    greets


    • Marked as answer by Goqu_green Wednesday, January 30, 2013 2:43 PM
    • Edited by Goqu_green Wednesday, January 30, 2013 2:44 PM
    Wednesday, January 30, 2013 2:43 PM