Select and Insert command OLEDB Command c#

Question

I have a excel sheet with two tabs so i want to get a row from one tab and insert into another,i thought it would be same like in sqlserver or mysql . Just select and insert..

I am using this query but it says syntax error not sure what is wrong in it.

  testCommand.CommandText = "Insert into [ActiveLicenses$]( Select * from [companies$] 
                             where [License Number] = '" + lnumber + "')";

  testCommand.ExecuteNonQuery();

UPDATE

Is there any way to delete the rows directly from excel sheet?

Jeremy Thompson · Answer 1 · 2013-01-07T05:26:38.037

0

You can use SQL to extract the data from Excel:

     using (OleDbDataAdapter da = new OleDbDataAdapter(
     "SELECT " + columns + " FROM [" + worksheetName + "$]", conn))
     {
       DataTable dt = new DataTable(tableName);
       da.Fill(dt);
       ds.Tables.Add(dt);
     }

Unfortunately inserting into excel doesn't work this way. I am pretty sure you cant specify a cell to write to using OleDb Insert Command, it will automatically go to the next open row in the specified column. You can workaround it with an update statement:

sql = "Update [Sheet1$A1:A10] SET A10 = 'YourValue'"; 
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();

Personally I would use VSTO rather than oleDB. Once you have extracted the cell simply open up the spreadsheet with code and insert the data:

Excel.Workbook wb = xlApp.Workbooks.Open(filePath);
rng = wb.Range["A1"];
rng.Value2 = "data";

edited Jan 07 '13 at 05:26

answered Jan 07 '13 at 05:05

Jeremy Thompson

61,933
36
195
321

I need to get almost 500k rows one by one so this might make it real slow,,,, – confusedMind Jan 07 '13 at 05:10
Using OleDB to insert rows individually sounds like it would be really slow, suggest using VSTO will increase performance especially if you use this technique: http://stackoverflow.com/a/2294087/495455 – Jeremy Thompson Jan 07 '13 at 05:29
CHECK MY ANSWER TOO PLEASE DOES THAT LOOK CORRECT AND THE RITE APPROACH – confusedMind Jan 07 '13 at 05:43

score 0 · Accepted Answer · answered Jan 07 '13 at 05:41

A faster method.

I take all the licenses into a DataTable and remove the ones not required takes less than 1 minute. and then simply export DataTable to Csv so i have the file ready in less than 1 minute.

Sample below:

static List<string> licensecAll = new List<string>();
DataTable dt = new DataTable();
            OleDbDataAdapter dp = new OleDbDataAdapter("select * from [companies$]", testCnn);
            dp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                for (int i = dt.Rows.Count-1; i >= 0; i--)
                {
                    string lnum = dt.Rows[i][0].ToString();
                    Console.WriteLine("LICENSE NUMBER" + lnum);
                    if (!licensecAll.Contains(lnum))
                    {
                        Console.WriteLine("ROW REMOVED");
                        dt.Rows.RemoveAt(i);
                    }
                }

            }

Then simply run datatable to csv....

  public static void DataTable2CSV(DataTable table, string filename, string seperateChar)
    {

        StreamWriter sr = null;

        try
        {

            sr = new StreamWriter(filename);
            string seperator = "";
            StringBuilder builder = new StringBuilder();
            foreach (DataColumn col in table.Columns)
            {

                builder.Append(seperator).Append(col.ColumnName);

                seperator = seperateChar;
            }

            sr.WriteLine(builder.ToString());

            foreach (DataRow row in table.Rows)
            {

                seperator = "";
                builder = new StringBuilder();
                foreach (DataColumn col in table.Columns)
                {

                    builder.Append(seperator).Append(row[col.ColumnName]);
                    seperator = seperateChar;

                }

                sr.WriteLine(builder.ToString());

            }

        }

        finally
        {

            if (sr != null)
            {

                sr.Close();

            }

        }

    }

Select and Insert command OLEDB Command c#

2 Answers2