Free Support Forum - aspose.com

Bulk insert

this is probably not the right place to be asking this question but i’m going to anyways. any feedback that anyone provides would be greatly appreciate.

I need to import a significant amount of data from an excel file to a database. Has anyone ever done this using aspose? Laurence, how can i get a column’s data and place it into a datatable?

again, any suggestions would be greatly appreciated.

Please try Cells.ExportDataTable method or Cells.ExportDataTableAsString method.

what is the difference between the two?

nevermind the question above. i found the answer in your api.

i do have one problem though

Dim dt As DataTable = xlwsheet.Cells.ExportDataTable(3, ColumnNameToIndex(“X”), 4466, ColumnNameToIndex(“X”))
Dim dt2 As DataTable = xlwsheet.Cells.ExportDataTable(3, ColumnNameToIndex(“A”), 4466, ColumnNameToIndex(“A”))

dt2 results in no columns. any idea why that is?

with regards to memory consumption, does the above use less memory then
Dim dt As DataTable = xlwsheet.Cells.ExportDataTable(3, ColumnNameToIndex(“A”), 4466, ColumnNameToIndex(“X”)) ?

in reality, all i need are three columns from one worksheet, A, C and X. what would be the most efficient way of getting this data from a workbook into a datatable?

If you want to export data from 3 non-contiguous columns, you'd better export them into 3 DataTables.

I think that column A may have different data types. You can change your code to:

Dim dt2 As DataTable = xlwsheet.Cells.ExportDataTableAsString(3, ColumnNameToIndex("A"), 4466, ColumnNameToIndex("A"))

If you still find any problem, please post your Excel file here.

thank you for the suggestion but it did not work.

secondly, my first bit of code
Dim dt As DataTable = xlwsheet.Cells.ExportDataTableAsString(3, ColumnNameToIndex(“X”), 4466, ColumnNameToIndex(“X”))
does not return a datatable with a single column. instead, it returns multiple columns containing data not even in my initial selection. it returns a datatable with 26 columns containing data after column X from the workbook.


The code should be:

Dim dt As DataTable = xlwsheet.Cells.ExportDataTableAsString(3, ColumnNameToIndex("X"), 4466, 1)

thanks laurence, that did the trick.

and for anyone who is interested in bulk insert, i found this article on the web.

http://www.gridviewguy.com/ArticleDetails.aspx?articleID=77