title: Data Corruption: select into to create a duplicate table as alternative for alter table for excel using ado.net
body: <p>I have tried to use Alter table on an excel spreadsheet to add a new column to existing spreadsheet but found out that I cannot use alter table on excel.</p>
  
  <p>From browsing the net I found that I can use select into to create a duplicate worksheet with new column in it. Here is the code for this.</p>
  
  <pre><code>static private bool CopyAndCreateNewSheet()
          {
              try
              {
                  DataTable dataTable = new DataTable();
                  using (OleDbConnection conn = new OleDbConnection(ConfigHelper.ConnectionStringReadWriteWorkBook))
                  {
                      conn.Open();
                      //string selectQuery = "SELECT 0 As IsProcessed, * INTO [Copy] FROM [" + ConfigHelper.WorkSheetName + "$]";
                      string selectQuery = "SELECT 0 As IsProcessed, * INTO [Excel 12.0;HDR= Yes;DATABASE=c:\\data\\Apr12.xlsx].[copyd]  FROM [" + ConfigHelper.WorkSheetName + "$]";
  
                      OleDbCommand cmd = new OleDbCommand(selectQuery, conn);
                      int count = cmd.ExecuteNonQuery();
  
                      MessageBox.Show(count.ToString());
  
                      conn.Close();
                  }
  
              }
              catch (OleDbException e)
              {
                  MessageBox.Show(e.Message.ToString());
  
              }
              catch (Exception e)
              {
                  MessageBox.Show(e.Message);
              }
  
  
              return true;
          }
  </code></pre>
  
  <p>My config for connectionstring is as follows.</p>
  
  <pre><code>&lt;add name="ReadWriteWorkBook" connectionString='Provider = Microsoft.ACE.OLEDB.12.0; Data Source="c:\data\Apr12.xlsx"; Extended Properties="Excel 12.0;HDR=Yes;READONLY=FALSE"'/&gt;
  </code></pre>
  
  <p>I have tried with both commented query and uncommented query to run select into but with no luck...</p>
  
  <p>When I open my workbook after above method call I am getting the following error. "Excel found unreadable connect in the file. do you want to recover the contents of this workbook? If you trust the source of this workbook, click yes."</p>
  
  <p>When I click yes, my new tab is there and also new headers are there but no data as excel has removed data from it.</p>
  
  <p>Any help much appreciated.</p>
  
  <p>Thanks in advance.</p>