title: How to write data from CSV file to SQL table?
body: <p>I have a <strong>C# windows application</strong> which I am writing and I have a can't seem to figure out how to insert data from a CSV file to a Database.</p>
  
  <p>I have a csv file named "<strong>data.csv</strong>" with records as follows</p>
  
  <pre><code>Header 201501
  id      code    amount
  1       ab11    5000
  2       ab11    6000
  3       ab11    8000
  3       wx34    2500
  3       df21    1000
  4       ab11    7000
  4       zx54    3500
  </code></pre>
  
  <p>and I have to get this data to a <strong>sql table</strong> to look as follows</p>
  
  <p><strong>ab11</strong> represents fees to be paid
  and <strong>any other code represent the amount which the student has paid</strong></p>
  
  <p><strong>first table tblStudents</strong></p>
  
  <pre><code>id      code    amount
  1       ab11    5000
  2       ab11    6000
  3       ab11    8000
  4       ab11    7000
  5       ab11    9000
  </code></pre>
  
  <p><strong>second table tblPaid</strong></p>
  
  <pre><code>id     code  Paid  Month
  3       wx34    2500  201401
  3       df21    1000  201401
  4       zx54    3500  201401
  </code></pre>
  
  <p>basically what I have to do is capture this data from the <strong>data.csv</strong> text file and sort it to a table to look like <strong>tblStudents</strong> and <strong>tblPaid</strong>.</p>
  
  <p>for the <strong>tblStudents</strong> I have to take everything the code "ab11" and put write to table.</p>
  
  <p>for the <strong>tblPaid</strong> I only have to write only data which doesn't have the code of "<strong>ab11</strong>" and put in a field that reads the date next to the header on the <strong>data.csv</strong> text file.</p>
  
  <p>I was thinking of using a <strong>BULK INSERT</strong> but the header shouldn't not be in the table.</p>
  
  <p>So for I can only get to create the table and read the <strong>data.csv</strong> text file as follows</p>
  
  <pre><code>        //Open File Dialog to open csv file
          //Only get csv files
          openFileDialog1.Filter = ".csv file|*.csv*";
          openFileDialog1.FilterIndex = 1;
  
          DialogResult result = openFileDialog1.ShowDialog();
  
          if (result == DialogResult.Yes)
          {
              String file = openFileDialog1.FileName;
  
              //Read csv text file to get the month in the first line
              using (var reader = new StreamReader(file))
              {
                  string firstLine = reader.ReadLine();
                  string[] splitedLine = firstLine.Split(';');
                  string month = splitedLine[1];
              }
              try
              {
                  //String with connection information of the database PSAHoldings
                  string connect = "Data Source=BRIAN-PC\\SQLEXPRESS; Initial Catalog=PSAHoldings; user id =sa; Password=kagiso";
  
                  //String Query to create t_original table if it does not exist already
                  string table = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_original' AND xtype='U')" +
                      "CREATE TABLE t_original (" +
                      "empId varChar(10) NULL," +
                      "paycode varChar(10) NULL," +
                      "amount int NULL," +
                      ")";
  
                  //String Query to create tblStudents table if it does not exist already
                  string tblStudents = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_employee' AND xtype='U')" +
                      "CREATE TABLE t_employee (" +
                      "empId varChar(10) NOT NULL PRIMARY KEY," +
                      "paycode varChar(10) NULL," +
                      "amount int NULL," +
                      ")";
  
                  //String Query to create tblPaid table if it does not exist already
                  string Paid = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_deduction' AND xtype='U')" +
                      "CREATE TABLE t_deduction (" +
                      "empId varChar(10) NOT NULL PRIMARY KEY," +
                      "amount int NULL," +
                      "balance int NULL," +
                      ")";
  
                  //String Query to create t_institutions table if it does not exist already
                  string t_institutions = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_institutions' AND xtype='U')" +
                      "CREATE TABLE t_institutions (" +
                      "empId varChar(10) NOT NULL PRIMARY KEY," +
                      "paycode varChar(10) NOT NULL," +
                      "amount int NULL," +
                      ")";
  
  
                  //Connecting to the server/database
                  SqlConnection con = new SqlConnection(connect);
                  con.Open();
  
                  //Create the tables
                  SqlCommand createTable = new SqlCommand(table, con);
                  SqlCommand createEmployee = new SqlCommand(t_employee, con);
                  SqlCommand createDeductions = new SqlCommand(t_deduction, con);
                  SqlCommand createInstitution = new SqlCommand(t_institutions, con);
  
                  createTable.ExecuteNonQuery();
                  createEmployee.ExecuteNonQuery();
                  createDeductions.ExecuteNonQuery();
                  createInstitution.ExecuteNonQuery();
  
  
                  //String Query to insert the text file into the t_original table
                  String BulkInsert = "BULK INSERT t_original FROM_" +
                      file + "_WITH (--FIRSTROW = 3," +
                      "FIELDTERMINATOR = ''," +
                      "MAXERRORS = 0," +
                      "ROWTERMINATOR = '\\n')";
                  //"UPDATE t_original"+
                  //"SET month =" + month + 
                  //"WHERE month is null";
  
                  //insert the text file into the t_original table
                  SqlCommand bulkCmd = new SqlCommand(BulkInsert, con);
                  bulkCmd.ExecuteNonQuery();
  
  
              }
              //Catch exeption
              catch (SqlException ex)
              {
                  MessageBox.Show(ex.ToString(), "Exception Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
              }
  </code></pre>
  
  <p>I have writen a <code>BULK_INSERT</code> but can't get anything into my table</p>
  
  <p>Any help will be appreciated as I am a beginner in development</p>