Good afternoon,
How can I extract data from xlsx and upload it to sql server or other db?
I don't want to export it to csv first because it some of my columns have embedded carriage returns and other special chars
Is there a recommended approach or sample on how to do this?
Many thanks,
Alan
Hi,
Well, I think you may export your worksheet data to fill a datatable or array using Cells.ExportDataTable or Cells.ExportArray methods.
//You can open the excel file using Aspose.Cells.Workbook.Open() method, now export / fill a datatable based on the sheet data in the cells, finally use ADO.NET API or your own code to fill/update the SQL Server Database table(s) or other db tables for your need.//
Sample code:
Workbook wb = new Workbook();
wb.Open(“f:\test\MyExcelFile.xlsx”);
Worksheet ws = wb.Worksheets[0];
int trows = ws.Cells.MaxDataRow;
int tcols = ws.Cells.MaxDataColumn;
DataTable dt = ws.Cells.ExportDataTable(0, 0,trows, tcols, true);
Hope, this helps you.
For further reference, please see the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/exporting-data-from-worksheets.html
Thank you.