title: Import Data From CSV
body: <p>Ok, this started out as a pretty simple task to import a customers data into our database. The script below loads all the data into memory and then allow manipulation after.</p>
  
  <pre><code>string ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + FileLoc + "\"; Extended Properties=\"text;HDR=Yes;FMT=Delimited(,)\"";
  
  using (OleDbConnection conn = new OleDbConnection(ConString))
  {
      using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM DataInput.csv", conn))
      {
          conn.Open();
  
          using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
          {
              int RowNumber = 0;
              while (dr.Read())
              {
  
  
                  if(RowNumber==0)
                  {
                      for (int i = 0; i &lt; dr.FieldCount; i++)
                      {
                          TitleData.Add(new CSVTitleData() {ColumnID = i,ColumnName = dr.GetValue(i).ToString()});
                      }
                  }
                  else
                  {
                      var DataToInsert = new List&lt;CSVData&gt;();
                      for (int i = 0; i &lt; dr.FieldCount; i++)
                      {
                          if (i == 8)
                          {
                              var A = dr.GetValue(i);
                              Console.WriteLine(A.GetType().ToString() + " - " + i);
                          }
                          DataToInsert.Add(new CSVData(){ColumnID = i,Value = dr.GetValue(i).ToString()});
                      }
  
                      Content.Add(DataToInsert);
                  }
                  RowNumber++;
              }
          }
      }
  }
  </code></pre>
  
  <p>This works fine until there is data like </p>
  
  <pre><code>4950
  4951
  4952
  4998
  2024
  4176
  7025-1
  RVAC010
  RVAC011
  4945
  4946
  4947
  4948
  </code></pre>
  
  <p>Excel tries to be clever and reads each of the fields as an Int. Resulting int the ones with a letters in being read as DBNull.</p>
  
  <p>Now I've done some reading and there are lots of people with this question but no solid solution. One of the possible solutions is to create a Scheme.ini I wonder if someone can help me out with the exact format of that?</p>
  
  <p>Or perhaps there is an easier was of reading in the CSV?</p>