Update or insert from csv into sql server

Question

I have a CSV file containing user info, say

first_name;last_name;user_id;full_name

column separator is ;, row terminator is \n.

What I need to do is to insert or update into users table. Unique key is user_id: if record with this user_id already exists, I need to update, if it doesn't I need to insert.

However, there are some problems that prevent me from using management studio data-import or bulk insert.

First, there are more fields in the users table (not just 4) and the order of columns in csv file does not correspond to the order of columns in the table. So I need to be able to specify which column from the file goes to which column in the table.

Secondly, some additional fields need to be filled. For example, users.email = users.user_id. Here is another obstacle - though for a newly inserted row users.email = users.user_id it is possible that users.email will change in the future, so I cannot just insert user_id and then run update [users] set [email] = [user_id].

have you tried using the import data wizard? sounds like you may need a two stage process — RoughPlace, Dec 16 '13 at 08:56
One thing you can do is import all the data to a temporary table and `insert/update` to your target table using new temporary table. Also have a look at [this](http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/) and [this](http://stackoverflow.com/questions/10418461/how-to-create-and-populate-a-table-in-a-single-step-as-part-of-a-csv-import-oper) articles — huMpty duMpty, Dec 16 '13 at 08:58
@Yakyb in import data wizard I cannot fill additional fields. Or can I? — xaxa, Dec 16 '13 at 09:02
@huMptyduMpty, can I use `#temp` table here? `bulk insert` into it from csv, then write a script like `if exists(... where user_id=...) then (update ...) else (insert ... )` and then `#drop temp`. Is that correct? — xaxa, Dec 16 '13 at 09:06
@xaxa: Sorry I mean not a `#temp`. Physical table in the database to use temporary for this import. Which can be deleted after! — huMpty duMpty, Dec 16 '13 at 09:27
@xaxa: But I think. you can try this way [How to create and populate a table in a single step as part of a CSV import operation?](http://stackoverflow.com/questions/10418461/how-to-create-and-populate-a-table-in-a-single-step-as-part-of-a-csv-import-oper). — huMpty duMpty, Dec 16 '13 at 09:33

score 0 · Answer 1 · answered Dec 16 '13 at 09:09

Use fgetcsv

Example with two colonne :

<?php
$row = 0;
$update = "";
//separator of column
$separator = ";";

//creates two variables containing the index columns to read / modify
$idx_nom = 0;
$idx_rubrique = 1;

//Open file writing
if (($handle = fopen("test.csv", "r")) !== FALSE) 
{

   //we travel the file line by line, storing the data in a table
    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) 
    {
       //can not control that the second line if the first contains the column headers
       if ($row != 0)
       {
          //conditions of one column 
          if (stristr($data[$idx_nom], 'chemise'))
          {
             $data[$idx_rubrique] = 1;
          }
           else if (stristr($data[$idx_nom], 'costume'))
          {
             $data[$idx_rubrique] = 2;
          }
           else if (stristr($data[$idx_nom], 'cravate'))
          {
             $data[$idx_rubrique] = 3;
          }
      }

      $update   .= implode($separator,$data)."\r\n";
      $row++;
   }

    fclose($handle);
}

//update from csv
$ouvre=fopen("test.csv","w+");
fwrite($ouvre,$update);
fclose($ouvre);
?>

Update or insert from csv into sql server

1 Answers1