Browse: Home / Importing CSV files to MS SQL 2005

Importing CSV files to MS SQL 2005

By Joel 'Jaykul' Bennett on 25-Sep-2005

I recently had to figure out for myself how to get comma-delimited text files imported into MS SQL, and when I found this little wizard, I found it’s profoundly simple, but I decided to post this walk-through anyway

Before You Start:

  • This will be easier if THERE IS A ROW AT THE TOP WITH COLUMN NAMES in it
  • Make sure all line endings are the same: {LF} or {CR}{LF}
  • CLEAN the data
  • Remove “?” from numeric fields, leave them blank or use a marker like -1…

From your start menu, run the “SQL Server Management Studio” ...

  1. If you haven’t already, create a database to put the data in.
  2. Right-click the database in the “Object Explorer” pane
  1. Choose “Tasks —> Import Data …”

In the “SQL Server Import and Export Wizard” ...
  1. Go to the “General” section
    1. Choose Data source: Flat File Source
    2. Pick the file
    3. Make sure the “Header row delimiter” is right: {LF} or {CR}{LF}
    4. If you have column names in the top row:
      1. Set “Header Rows to skip” to 0
    1. Check “Column names in the first data row”
  2. In the left side bar of the dialog, select “Columns”
    1. Make sure the “Row delimiter” is right.
    1. Visually inspect the data preview, make sure the column names are right…
    2. In the left side bar of the dialog, select “Advanced”
    3. Click “Suggest Types…”
    4. Set settings appropriately (the defaults are probably ok)
    1. Double-check the columns and their types as listed…
    2. In the left side bar of the dialog, select “Preview”
    1. Double-check the columns and their types as listed…
  3. Click “Next”
  4. Check the destination – it should be ok if you right-clicked the database
  5. Click “Next”
  6. Check the settings for the table (if you’re working with a new DB, it’s ok)
    1. Note: If you’re working with data that has an index or “identity” column, you may want to make sure you “Edit…” the mapping and “Enable identity insert.”
    1. If the table exists (eg: you had an error below and cleaned up the data and are re-importing), you’ll need to check off “Drop and re-create destination table”
  7. Click “Next”
  8. Save the SSIS (SQL Server Import Settings) if you want to.
  9. Click Finish
    1. Watch and pray ....
  1. If there are any errors, you’ll have to go back to “CLEAN the data”

Similar Posts:

    None Found

| Tagged | 6 Responses

6 responses to “Importing CSV files to MS SQL 2005”

  1. Russ Harding
    Russ Harding 26-Oct-2005 at 10:02 am |

    I’m sorry, but maybe I am missing something. I have the september CTP of SQL serevr 2K5, and I just can not find the import wizzard your talking about. This is killing me, and all I want to do is import a couple simple csv files, that I would much rather not do by hand.

  2. Rune W.
    Rune W. 09-Jan-2006 at 5:25 am |

    You are unfortunately not missing something in CTP of SQL 2K5.

    It appears the Import wizard is only included with the full/retail products and not with the free SQL 2005 Express with the CTP Management studio.

    If someone knows otherwise, then please elaborate.

    I would have liked to see this function in the Express/CTP versions also…

  3. CIber Guy
    CIber Guy 26-Jun-2006 at 5:18 pm |

    I just used this Wizard and I love it!!!!

    It worked just as described and quickly too.

  4. quokka
    quokka 20-Jul-2006 at 3:49 am |

    thanks very much for that info, helped heaps… good karma to you

  5. Kraven
    Kraven 21-Dec-2006 at 10:48 am |

    This is an ok plan. However, I am running into an issue trying to import more than 1000 rows. In the Advanced field (Suggest Types), I place 10000 and when i clicked out of the rows field, it reverted back to 1000. Any ideas how I can do this with more than 10000 rows?

  6. Edwin
    Edwin 30-Jun-2007 at 1:07 pm |

    Hi
    I manage the tools very well, but I have a problem. I created a database and i wanto to import a csv file with thounsands and thounsands of records….......I have a column with integer values, but I have problem with this type of values…I have negative and positive values.
    You description is perfect but what about complex situations…..could you help with this.

    - Executing (Error)
    Messages
    Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column “INTVAL” returned status value 2 and status text “The value could not be converted because of a potential loss of data.”.
    (SQL Server Import and Export Wizard)

    Error 0xc0209029: Data Flow Task: The “output column “INTVAL” (22)” failed because error code 0xC0209084 occurred, and the error row disposition on “output column “INTVAL” (22)” specifies failure on error. An error occurred on the specified object of the specified component.
    (SQL Server Import and Export Wizard)

    Error 0xc0202092: Data Flow Task: An error occurred while processing file “E:\minessight\blastinfoattrib.csv” on data row 2.
    (SQL Server Import and Export Wizard)

    Error 0xc0047038: Data Flow Task: The PrimeOutput method on component “Source – blastinfoattrib_csv” (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
    (SQL Server Import and Export Wizard)

    Error 0xc0047021: Data Flow Task: Thread “SourceThread0” has exited with error code 0xC0047038.
    (SQL Server Import and Export Wizard)

    Error 0xc0047039: Data Flow Task: Thread “WorkThread0” received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
    (SQL Server Import and Export Wizard)

    Error 0xc0047021: Data Flow Task: Thread “WorkThread0” has exited with error code 0xC0047039.
    (SQL Server Import and Export Wizard)