5

I'm using OleDB and I want to export my objects into excel table. Each row in the sheet will be one of my objects. The problem is that I don't know how to insert data when there's no column headers in the sheet.

This one:

commandString = "Insert into [Sheet1$] values('test1', 'test2')"

throws this exception:

Number of query values and destination fields are not the same.

My connection string is:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filename+";Extended Properties='Excel 8.0;HDR=No'"
Dimitar Tsonev
  • 3,711
  • 5
  • 40
  • 70

2 Answers2

9

If the connection string contains HDR=NO then the Jet OLE DB provider automatically names the fields for you (F1 for the first field, F2 for the second field, and so on). I will try to change your query in this way

commandString = "Insert into [Sheet1$] (F1, F2) values('test1', 'test2')"

this works only after you have created the excel file and have something inserted in the first two cells of the first row in Sheet1

Steve
  • 213,761
  • 22
  • 232
  • 286
  • This is not working. Now it says The INSERT INTO statement contains the following unknown field name: 'F1'. Make sure you have typed the name correctly, and try the operation again. – Dimitar Tsonev Apr 19 '12 at 08:34
  • run a command like "select * from sheet1" and look at the field names that are returned, and build you insert with those names – mslliviu Apr 19 '12 at 09:05
  • 1
    Well, I have managed to find the reason for the INVALID FIELD NAME error. Happens when the excel file is empty. If you insert something in the first cell and second cell, then the F1 and F2 field name are recognized. – Steve Apr 19 '12 at 09:28
  • This helped me out immensely! I was running in to this error with a column header called `Account #`. For whatever reason, the `#` symbol in the column header created this error. – Tim Cooke Apr 13 '17 at 00:28
2

You need to specify which values you are writing, since you don't use an HDR - just use the cells. The Error "number of query values" simplies implies that - there are no fields assigned to the values supplied.

Update: @Steve was right with the Fields (F1,F2,etc), and the code below does work here:

    OleDbConnection Cn = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=No\"", @"D:\test.xls"));
    Cn.Open();
    OleDbCommand Com = new OleDbCommand("INSERT INTO [Sheet1$](F1,F2) VALUES('test3','test4');", Cn);
    Com.ExecuteNonQuery();
    Cn.Close();
riffnl
  • 3,248
  • 1
  • 19
  • 32
  • The INSERT INTO statement contains the following unknown field name: 'A1'. Make sure you have typed the name correctly, and try the operation again. – Dimitar Tsonev Apr 19 '12 at 08:34
  • I was wrong BTW, @Steve had it right - you need to focus on the fields on numeric base (F1,F2,etc). But the updated source does work. – riffnl Apr 19 '12 at 08:53
  • Weird. I'm using the exact same code you provided and again I got this exception. – Dimitar Tsonev Apr 19 '12 at 09:04
  • You're using the F1-Fx fieldstructure?? – riffnl Apr 19 '12 at 09:09
  • What fields are returned on a select? (and to be on the safe side, the sheet is called Sheet1 right?) – riffnl Apr 19 '12 at 11:32
  • @riffnl: Any idea why only `F1` is recognized and an exception is thrown while inserting into `F2`. – Robin Maben May 01 '12 at 07:44
  • @RobinMaben When using a "blank" (with some data) excelfile it should work like normally. However, when you've hidden rows and/or columns the datastructure is altered (FUBAR I might add) and you wont be able to add any columns without exceptions. You could attempt to read the tablestructure using "select *" and loop through the columns even so the results can be unreliable. – riffnl May 01 '12 at 07:58
  • @riffnl: You're right. I tried using the `select` method. That too returns only `F1`. Ugly as it may seem, I'm afraid I might have to accept it and add column headers. (Even though that's what I'm trying to avoid) :( Thanks a lot anyways. – Robin Maben May 01 '12 at 09:09