Inserting ExcelOnline Data
You can execute INSERT statements when working with worksheets as tables. You can specify the column names detected from a header row or specify the alphabetical column names in Excel Online. You can also configure how the provider parses the input values into Microsoft Excel Online cell formats.
Set the Header property to configure column names. Set ValueInputOption to configure input parsing.
Note that inserting to ranges is not supported. Use Batch Inserts or Temporary Tables instead.
Inserting with Headers
When the Header property is set to true, you can specify cells in a header row as the column names.
INSERT INTO Test_xlsx_Sheet1 (Name, Amount) VALUES ('Test', 10)
Inserting without Headers
When the Header property is set to false, provide the alphabetical column names.
INSERT INTO Test_xlsx_Sheet1 (A, B) VALUES ('Test', 10)
Configuring Input Parsing
By default, the provider parses all values you input in the SQL statement the same as if you entered the input in the Microsoft Excel Online UI. For example, strings may be converted to numbers, dates, etc.
To disable the parsing of input values, set ValueInputOption. Additionally, see Using Formulas to configure formula evaluation.
Example: Bulk Inserts using Temporary Tables
If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory.
Creating Temporary Tables
Insert the rows you need in a temporary table. Then insert all the rows from that temporary table into the actual table.
Code Example
Below is the complete code to insert 2 rows into Test_xlsx_Sheet1:ExcelOnlineConnection connection = new ExcelOnlineConnection("InitiateOAuth=GETANDREFRESH;");
connection.Open();
ExcelOnlineCommand command = new ExcelOnlineCommand("INSERT INTO [Test_xlsx_Sheet1#TEMP] (Column1, Column2) VALUES ('value1', 'value2')", connection);
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO [Test_xlsx_Sheet1#TEMP] (Column1, Column2) VALUES ('value3', 'value4')";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO [Test_xlsx_Sheet1] (Column1, Column2) SELECT Column1, Column2 FROM [Test_xlsx_Sheet1#TEMP]";
command.ExecuteNonQuery();
connection.Close();