none
Reading XLSX files as ADO.NET databases RRS feed

Answers

  • Hi DavidThi808,
    Sorry for my misunderstanding, please refer to this link.
    https://www.microsoft.com/en-us/download/details.aspx?id=13255
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by DavidThi808 Monday, December 16, 2019 4:22 PM
    Monday, December 16, 2019 3:22 AM

All replies

  • Hi DavidThi808,
    To read XLSX files as ADO.NET databases, you can refer to the following code.
    private void Form1_Load(object sender, EventArgs e)
    {
        {
            DataTable dataTable = new DataTable();
            string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =C:\\Users\\Desktop\\test.xlsx.;Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
            using (OleDbConnection connection = new OleDbConnection(constr))
            {
                connection.Open();
                string strCom = " SELECT * FROM [sheet1$]";
                OleDbDataAdapter oleAdpt = new OleDbDataAdapter(strCom, connection); //here we read data from sheet1  
                oleAdpt.Fill(dataTable); //fill excel data into dataTable  
            }
        }
    }
    Best Regards,
    Daniel Zhang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 11, 2019 5:30 AM
  • Hi Daniel;

    Thank you, I understand the connection string. My question is where can I download the Excel OleDB connector to install on a server?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Wednesday, December 11, 2019 12:43 PM
  • Hi DavidThi808,
    About"where can I download the Excel OleDB connector to install on a server", you can refer to these documents.
    [Installing OLE DB Driver for SQL Server]
    [OLE DB connection manager]
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 12, 2019 5:43 AM
  • Daniel;

    Both links you sent are for installing the OleDB connector for SQL Server. I need the connector for Excel.

    Where can I download the OleDB connector for Excel?

    - dave

    ps - This is frustrating as I believe my previous questions on this were clear and I am not getting an answer to my question. Again, Where can I download the OleDB connector for Excel?


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Thursday, December 12, 2019 12:28 PM
  • Hi DavidThi808,
    The OleDB connector for SQL Server can also read excle directly. And what is OleDB connector for Excel?
    This document may be useful for you.
    [Import data from Excel or export data to Excel with SQL Server Integration Services (SSIS)]
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 13, 2019 8:57 AM
  • Hi Daniel;

    That is incorrect, you do not use the SQL Server connector. To quote from your comment above, the connection string is:

    string constr = string.Format("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =C:\\Users\\Desktop\\test.xlsx.;Extended Properties = \"Excel 12.0; HDR=Yes;\"; ");
    using (OleDbConnection connection = new OleDbConnection(constr))

    This means there is an OleDB connector for Excel and that is what I should use. Where can I download the OleDB connector for Excel?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Friday, December 13, 2019 1:50 PM
  • Hi DavidThi808,
    Sorry for my misunderstanding, please refer to this link.
    https://www.microsoft.com/en-us/download/details.aspx?id=13255
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by DavidThi808 Monday, December 16, 2019 4:22 PM
    Monday, December 16, 2019 3:22 AM
  • Monday, December 16, 2019 4:22 PM