How to upload Excel (XLSX or XLS), CSV, Tab Delimited Text files to SQL Server

I opened my blog dashboard after a long time and found this in draft! I do not remember anything about this 🙂 Why I was uploading files to excel using C#, for which project I did this , absolutely no idea. But posting, so it may help somebody.

Part 1: Upload the file to any temporary folder on the server. (Recommended)

Sometimes client side files can create “access denied” or “read only” exceptions. It is recommended that we should upload the file to server before using it as data source.

For CSV or Tab Delimited text files we should upload a “Schema.ini” file along with the source file. The detailed steps are mentioned in this post.

——————————————————————————————————————————

Part 2: Create connection string for source

for excel 2007 or 2010 files

string connectionstring = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\”” +
“[Full file path including extension]” + “\”;Extended Properties=’Excel 12.0;HDR=Yes’;”;
// e.g. “c:\foldername\filename.xlsx” or Server.MapPath(“~\foldername\filename.xlsx”)

for excel 97 – 2003 files

string connectionstring = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\”” +
“[Full file path including extension]” + “\”;Extended Properties=’Excel 8.0;HDR=Yes’;”;
// e.g. “c:\foldername\filename.xls” or Server.MapPath(“~\foldername\filename.xls”)

for CSV or text files

string connectionstring = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\”” +
“[folder path]” + “\”;Extended Properties=’text;HDR=Yes;FMT=Delimited()’;”;
// e.g. “c:\foldername\” or Server.MapPath(“~\foldername\”)

Note: the attribute HDR=Yes means the source file has header row. If your source file does not have a header row then change it to HDR=No.

————————————————————————————————————————————————-

Part 3: Retrieve data from the file and insert into SQL Server table (using C# code).

using (OleDbConnection oledbConnection = new OleDbConnection(connectionstring))
{
  oledbConnection.Open();

  #region[required only for XLSX or XLS file]

  DataTable dbSchema = null;
  dbSchema = oledbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
  string cmdText = string.Format(“SELECT * FROM [{0}]”, dbSchema.Rows[0],

  [“TABLE_NAME”].ToString());

  #endregion[required only for XLSX or XLS file]

  #region[required only for CSV or txt files]

  string cmdText = string.Format(“SELECT * FROM [{0}]”, “[file name]”);

  // e.g filename.txt or filename.csv

  #endregion[required only for CSV or txt files]

  using (OleDbCommand oledbCommand = new OleDbCommand(cmdText, oledbConnection))
  {
    using (OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(oledbCommand))
    {
      DataTable tempTable = new DataTable();
      oledbAdapter.Fill(tempTable);
      using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(“[Connection string for SQL Server]”))
      {
         sqlBulkCopy.DestinationTableName = “[Table name on SQL server]”;
         sqlBulkCopy.WriteToServer(tempTable);
         sqlBulkCopy.Close();
      }
    }
  }
}

Note:

The TABLE_NAME attribute in the database schema returns worksheet name of the Excel file. Here we are using data from first worksheet (Rows[0][“TABLE_NAME”]).

For CSV or text files, we can specify the file name as source table name.