Excel file to TEMP SQL Server Database Table w/ unknown columns

Question

I have an Excel file that has 4 known column headers and the rest will be Year/Month. The Year/Month column headers can be different in every file. There could be any number of these Y/M columns. In my WinForms application, the user will select an Excel file. What I need to figure out how to do is to take that Excel file, import it to a temporary table in my database, do some things, then delete the temp table. Everything I have seen is where you know what the columns will be ahead of time.

How can I accomplish this? Is it even possible?

I will try to be more clear. I have an excel file. The number of and names of the columns will vary(so I cant just create a temp table since I dont know what the fields are). I am taking that excel file and putting it into a local datatable in my application. I need to create a table on my sql database that mirrors that local database then copy the data there. So from my application how do I create a temp table on the mssql server.

Cetin Basoz · Answer

(Comments are getting rather messy. Here is one of my routines where I get tables from excel file - C# sorry):

private List GetTablesFromExcel(string dataSource)
{
  List tables = new List();
  using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;"+
  string.Format("Data Source={0};",dataSource)+
  "Extended Properties=\"Excel 12.0;HDR=Yes\""))
  {
  con.Open();
  var schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
  foreach (DataRow row in schemaTable.Rows)
  {
    string sheetName = (string)row["TABLE_NAME"];
    OleDbCommand cmd = new OleDbCommand(string.Format("Select * from [{0}]",sheetName), con);
    System.Data.DataTable t = new System.Data.DataTable(sheetName);
    t.Load( cmd.ExecuteReader() );
    tables.Add( t );
  }
  con.Close();
  }
  return tables;
}

EDIT: And this is SqlBulkCopy sample (sorry also comments are in Turkish but you could see temp table creation is done with string thus you could build at runtime with StringBuilder):

static readonly string sqlConnectionString = @"server=.\SQLExpress;Trusted_Connection=yes;";
static readonly string path = @"d:	emp\dersler.xlsx"; // excel dosyasi
static readonly string sheetName = "Dersler$";

void Main()
{
    using (OleDbConnection cn = new OleDbConnection(
          "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
          ";Extended Properties=\"Excel 12.0;HDR=Yes\""))
    using (SqlConnection scn = new SqlConnection(sqlConnectionString))
    {
        // Excelden veriyi al ve SqlBulkCopy ile servera yaz  
        // Kaynak
        OleDbCommand cmd = new OleDbCommand(String.Format("select * from [{0}]", sheetName), cn);


        SqlBulkCopy sbc = new SqlBulkCopy(scn);
        // Mapping  
        sbc.ColumnMappings.Add(0, "[SiraNo]");
        sbc.ColumnMappings.Add(1, "[DersinAdi]");
        sbc.ColumnMappings.Add(2, "[SinifTuru]");
        sbc.ColumnMappings.Add(3, "[SinifSeviyesi]");
        sbc.ColumnMappings.Add(4, "[DersTuru]");
        sbc.ColumnMappings.Add(5, "[Secmeli]");

        cn.Open();
        scn.Open();

        SqlCommand createTemp = new SqlCommand();
        createTemp.CommandText = @"if exists
   (SELECT * FROM tempdb.sys.objects 
   WHERE object_id = OBJECT_ID(N'[tempdb]..[##Dersler]','U'))
   BEGIN
        drop table [##Dersler];
   END
   
  create table ##Dersler 
  (
        [SiraNo] int primary key not null,
        [DersinAdi] nvarchar(250) not null,
        [SinifTuru] nvarchar(50) null,
        [SinifSeviyesi] int,
        [DersTuru] nvarchar(50),
        [Secmeli] bit
  )
  ";
        createTemp.Connection = scn;
        createTemp.ExecuteNonQuery();

        OleDbDataReader rdr = cmd.ExecuteReader();


        // SqlBulkCopy'nin propertyleri
        sbc.DestinationTableName = "##Dersler";
        sbc.NotifyAfter = 20; // 10000 alti kullanilmaz da sizin dataniz cok az
        sbc.BatchSize = 10;
        sbc.BulkCopyTimeout = 300; // Saniye. 5 dk cok bile fazla
        sbc.EnableStreaming = true;

        sbc.SqlRowsCopied += (sender, e) =>
        {
            Console.WriteLine("-- Copied {0} rows to {1}.",
            e.RowsCopied,
            ((SqlBulkCopy)sender).DestinationTableName);
        };

        sbc.WriteToServer(rdr);

        if (!rdr.IsClosed) { rdr.Close(); }

        cn.Close();
        
        DataTable t = new DataTable(); // Yazdigimiza bakalim
        t.Load(new SqlCommand("select * from ##Dersler", scn).ExecuteReader());
        
        scn.Close();

        Form f = new Form();
        DataGridView dgv = new DataGridView {Dock=DockStyle.Fill, DataSource=t};
        f.Controls.Add(dgv);
        f.Show();
    }
}

Excel file to TEMP SQL Server Database Table w/ unknown columns

1 Answers