none
Export dtat from Excel to SQL using openxml RRS feed

  • Question

  • Hi all.,

    I'm new in Openxml.

    I want export data from excel to SQL table using open xml.

    How to do?

    Tahnks in Advance.


    • Edited by Virendhar Tuesday, January 7, 2014 1:17 PM changes
    Tuesday, January 7, 2014 1:17 PM

Answers

  • Hi,

    It’s difficult to export data from excel spreadsheet to SQL Server Table with Open XML directly.

    We could only get the data from Excel spreadsheet. Here is a sample to read the number format data from a spreadsheet. If the data is string format, we should get related data from the sharedStrings element.

    static void ReadExcelFile(string fileName)
    {
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
        {
            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
            SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
            string text;
            foreach (Row r in sheetData.Elements<Row>())
            {
                foreach (Cell c in r.Elements<Cell>())
                {
                    text = c.CellValue.Text;
                    Console.Write(text + " ");
                }
            }
            Console.WriteLine();
            Console.ReadKey();
        }
    }

    About how to import these data into a SQL table, you could consider posting it in the SQL Server related forum for more effective responses.

    In my opinion, I think getting data from a spreadsheet with Open XML and then importing them into a SQL table is somehow complex, I suggest you resorting to ODBC driver to export the worksheet directly instead of using Open XML technology.


    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.

    Wednesday, January 8, 2014 10:53 AM
    Moderator