Bulk insert

Aspose.Cells Product Family
1

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.

2

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

3

what is the difference between the two?

4

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?

5

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.

6

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.


7

The code should be:

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

8

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