Reading and checking .csv file and then uploading it to the sql server 2008 r2

Question

Hi,

I have got a page in which there is a file upload option where I have to upload/import the csv file. What I want to do is to check if the correct format of file is uploaded for instance if any other than csv file is uploaded, the system should give an error message. Also what I need to do is to check certain fields of the csv file for instance there are some mandatory fields in the csv file which should be there like name , postcode, How can I check that these fields are not empty . After performing these task, the system should automatically upload the csv file onto the sql sever 2008. Any ideas or tutorial ... will be highly appreciated.

Answer 1

Check out this thread:

http://stackoverflow.com/questions/2081418/parsing-csv-files-in-c

I believe this will let you extract it as you want and then easily shove it into a table in SQL Server.

Answer 2

try using Oledb. this codesnippet helps you read your csv file into a datatable which should be pretty easy to dump to a database.

public static DataTable ParseCSV(string path)
{
  if (!File.Exists(path))
    return null;

  string full = Path.GetFullPath(path);
  string file = Path.GetFileName(full);
  string dir = Path.GetDirectoryName(full);

  //create the "database" connection string
  string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
    + "Data Source=\"" + dir + "\\\";"
    + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";

  //create the database query
  string query = "SELECT * FROM " + file;

  //create a DataTable to hold the query results
  DataTable dTable = new DataTable();

  //create an OleDbDataAdapter to execute the query
  OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

  try
  {
    //fill the DataTable
    dAdapter.Fill(dTable);
  }
  catch (InvalidOperationException /*e*/)
  { }

  dAdapter.Dispose();

  return dTable;
}

Answer 3

Check out the fast CSV reader over at codeproject.com

Another option would be to use SSIS.