How can I extract data from xlsx and upload it to sql server or other db?

Aspose.Cells Product Family
1

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

2

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.