0

Is it possible to update an excel file using this SQL query?

UPDATE [My Sheet] SET B40='17'

The B40 is the coordinate of that excel file. Due to the structure of the existing excel file, I cannot use a table column to update. I only have to use coordinates.

When I run this I get this exception below:

No value given for one or more required parameters.

And my connection string is:

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +";Extended Properties='Excel 12.0;HDR=NO;ReadOnly=False;'"

There is also a question here but I'm still getting the same error

E-A
  • 1,995
  • 6
  • 33
  • 47

2 Answers2

0

The usual reason for this error is a missing or misspelled value.

First you check, IS there any Typo error or not?

Aditya Dhanraj
  • 189
  • 1
  • 1
  • 12
  • The query is pretty simple `UPDATE [My Sheet] SET B40='17'`. If the sheet name is wrong, exception says "There is no sheet by that name". And there is definately B40 in that excel file. So there is no misspelling. – E-A May 28 '19 at 07:58
  • 1
    UPDATE ["+sheetName+"$B40:B40] SET F1='17' apply this method then. – Aditya Dhanraj May 28 '19 at 08:00
  • 1
    hey You are using, excel 12.0 so please try this ----- Properties='Excel 12.0 xml;HDR=NO;ReadOnly=False; – Aditya Dhanraj May 28 '19 at 08:02
  • Indeed you did! – E-A May 28 '19 at 08:44
  • Aditya, do you also know how to update MULTIPLE columns with the same coordinate approach? Is it like: `UPDATE [My Sheet$B40:B40;C40:C40] SET F1=17, F2='ssff'` – E-A May 28 '19 at 10:23
  • how many columns you have to update? If its two or three then you can execute it single single(individual) – Aditya Dhanraj May 28 '19 at 12:31
  • objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A4:A4] SET F1='17'", objConn); objCmdSelect.ExecuteNonQuery(); objCmdSelect = new OleDbCommand("UPDATE [Sheet1$A5:A5] SET F1='goodby'", objConn); objCmdSelect.ExecuteNonQuery(); – Aditya Dhanraj May 28 '19 at 12:34
  • Yes, I found that but every query needs to execute before the next query. It's not efficient. Can't we send 1 single query includes multiple updates? I want to EXECUTE only ONCE. Because, execution costs time. – E-A May 29 '19 at 06:48
0

Thank you very much for Aditya on the comments that helped me realize I was thinking this a bit wrong.

First, my conn string is now:

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO;ReadOnly=False;'"

And second, the query is now like:

UPDATE [My Sheet$B40:B40] SET F1=17

I always thought F1 should be THE coordinate I want to update and that's why I failed.

E-A
  • 1,995
  • 6
  • 33
  • 47