none
Update excel or CSV cell via SQL statement from c# application RRS feed

  • Question

  • I realise that updating an excel spreadsheet  or CSV file has more constraints than say updating a SQL database table. However searching online I was led to believe that I could achieve the following:
    connection string
                                Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myLib\myFile.csv;  Extended properties='csv; DR=No;IMEX=1;FMT=Delimited;ReadOnly=False'

    SQL Statement
                                Update [myFile$B32:B32] SET F1='TEST'

                                or

                                Insert into [myFile$B32:B32] values('TEST')

                              I am able to read through the spreadsheet successfully via the above connection string. However when I attempt to update a cell (eg Row 32 , Col 2) then I get an error message of "Could not find installable ISAM". I have tried many variations of both the SQL statements and the connection string without any success. Appreciate any advise on how to update a .csv files cell via an SQL statement run from a c# application.

                              regards
                              Pat
    Thursday, October 6, 2016 8:43 PM

Answers

All replies

  • Hi PadraigIrl,

    This is the forum to discuss questions and feedback for Excel for Developers, I'll move your question to the MSDN forum for ADO.NET Managed Providers

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, 
    and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. 

    Thanks for your understanding.
    Friday, October 7, 2016 5:42 AM
  • Hi PadraigIrl,

    According to your description, I create a demo and reproduce your issue on side, it seems that we could not use update statement with CSV file. we could use StreamWriter to achieve it. like this:

    string file = @"C:\temp\csvfile.csv";
    string[] lines = System.IO.File.ReadAllLines(file);
    System.IO.StreamWriter sw = new System.IO.StreamWriter(file);
    foreach(string s in lines) {
    	sw.WriteLine(Regex.Replace(s, "^(.*?)(?i:independent)$", "$1Democratic"));
    }
    sw.Close();

    If you want to use update statement, please use .xlsx file. and modify your connection like this:

    string dataPath = @"D:\Data\Excel\20161007.xlsx";
    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dataPath + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, October 7, 2016 9:04 AM
    Moderator
  • The Text Provider/ISAM does not know anything about Excel Ranges. I don't know whether you can use the Excel ISAM with a .csv file so you may need to convert it to an .xlsx file and use the appropriate connection string for Excel instead in order to insert to or update an Excel Range.

    https://www.connectionstrings.com/ace-oledb-12-0/


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, October 7, 2016 12:58 PM