Export into excel file without headers c# using Oledb

Question

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'"

Steve · Accepted Answer · 2012-04-19 09:30:08Z

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

edited Apr 19, 2012 at 9:30

answered Apr 19, 2012 at 8:19

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dimitar Tsonev Over a year ago

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.

mslliviu Over a year ago

run a command like "select * from sheet1" and look at the field names that are returned, and build you insert with those names

Steve Over a year ago

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.

Tim Cooke Over a year ago

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.

riffnl · Accepted Answer · 2012-04-19 09:09:18Z

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();

edited Apr 19, 2012 at 9:09

answered Apr 19, 2012 at 8:12

riffnl

3,1911 gold badge21 silver badges33 bronze badges

8 Comments

Dimitar Tsonev Over a year ago

The INSERT INTO statement contains the following unknown field name: 'A1'. Make sure you have typed the name correctly, and try the operation again.

riffnl Over a year ago

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.

Dimitar Tsonev Over a year ago

Weird. I'm using the exact same code you provided and again I got this exception.

riffnl Over a year ago

You're using the F1-Fx fieldstructure??

riffnl Over a year ago

What fields are returned on a select? (and to be on the safe side, the sheet is called Sheet1 right?)

|

Collectives™ on Stack Overflow

Export into excel file without headers c# using Oledb

2 Answers 2

4 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related