Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Importing a csv file directly into MS SQL

    I have a csv file which has been exported from one of our systems. I need to be able to write a script that can import this csv directly into two SQL tables. The first column of the csv file determines which table the data should go in.

    i.e.

    JREV, 1, lhr, 2, 5
    JSTA, 2, gla, 3, 6
    JSTA, 5, lhr, 5, 7
    JREV, 2, sla, 8, 8

    Once i have created the 2 tables, (one called JREV and another called JSTA), I need to be able to import/query the data directly from the CSV file into these two tables.

    Is there a way to do this please?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rosmiq View Post
    I need to be able to import/query the data directly from the CSV file into these two tables.
    Are you sure that is exactly what you need? Because that would be complex. Easier would be to have a staging table. BULK INSERT in to that, do any cleaning\ checks\ transformations you require, then just run two insert queries for your two destination tables.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    Hi, thank you for your quick reply.

    Yes because the data on the CSV file is mixed with different data types, i.e. column 2 may have a datetime field and also a numeric/data field.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    With a staging table you would not enforce datatypes - just use NVARCHAR columns. The idea of this is that you don't want your entire export to fail if there is one rogue value. Make the import as simple as possible (a dump into an unconstrained table). You can then do any cleaning or checks required and then move all rows that are clean in the final destination tables.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

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