A column named '' already belongs to this DataTable

Aspose.Cells Product Family
1

Hello,

I am trying to import the contents of an Excel file into SQL Server database.

Below is the code I am using to import

Dim countOfRows As Integer
Dim insertCells As Integer = 0
Dim i, j As Integer
i = 0
countOfRows = worksheet.Cells.MaxDataRow
j = Math.Floor(worksheet.Cells.MaxDataRow / 5000)

While i < j

ExcelSheets = New DataTable
ExcelSheets = worksheet.Cells.ExportDataTableAsString(insertCells, 0, 5001, worksheet.Cells.MaxDataColumn +

1, True)
sqlBulk.DestinationTableName = destinationTblName
sqlBulk.WriteToServerExcelSheets)
i = i + 1
insertCells = insertCells + 5000

End While

I will explain the code. Since the data I want to import is too large and I don't want the timeout to happen , I am importing 5000 records at a time. For the first import I am not getting any errors, but for the next 5000 rows I am getting the following error 'A column named '' already belongs to this DataTable.'

Since I am creating a new instance of data table for every loop , I am not sure there is any problem with datatable.

One more thing that I can't do with the datatable is create a stongly typed datatabel, the reason is the format of the excel sheet going to be imported is not fixed . The column names may vary and their types also.

Can you please help me in this.

I am attaching the file for your reference.

Thanks

2

Hi,


" Dim countOfRows As Integer

Dim insertCells As Integer = 0
Dim i, j As Integer
i = 0
countOfRows = worksheet.Cells.MaxDataRow
j = Math.Floor(worksheet.Cells.MaxDataRow / 5000)

While i < j

ExcelSheets = New DataTable
ExcelSheets = worksheet.Cells.ExportDataTableAsString(insertCells, 0, 5001, worksheet.Cells.MaxDataColumn +

1, True)
sqlBulk.DestinationTableName = destinationTblName
sqlBulk.WriteToServerExcelSheets)
i = i + 1
insertCells = insertCells + 5000

End While"

Seeing your code, it is obvious that you will surely get this error. The reason is simple: How could you have two columns with the same name in a DataTable, this is not possible, so you are getting this error from ExportDataTableAsString() method.


I think for your situation you have two options. I have also explained it with examples. Please see the attached template Excel file that does have data but have a similar “Name” for two columns. For the line of code:

ExcelSheets = worksheet.Cells.ExportDataTableAsString(insertCells, 0, 5001, worksheet.Cells.MaxDataColumn + 1, True)

The last parameter refers to “exportColumnName” which you set to true, so the very first line would be reserved for column names for your underlying data table. I mean the data table’s column names are based on each text in different cells for the row. For example in row 5001, the Cell AH and AL have same values so, column names would be duplicated, so you got this error. Similarly columns N and W have the same values.

For your situation, kindly set this parameter to “False”, it would work fine.


More explanation with sample examples, you may choose any one:

1) Please do not export column names when you use ExportDataTableAsString() method, set false for the ExportColumnName parameter.

Sample code:
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(@“e:\test2\import.xlsx”);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
//Do not export column names, the columns would come as Column1, Column2 etc.
DataTable dt = worksheet.Cells.ExportDataTableAsString(1, 0, worksheet.Cells.MaxDataRow + 1, worksheet.Cells.MaxDataColumn + 1, false);
Aspose.Cells.Worksheet worksheet2 = workbook.Worksheets[1];

worksheet2.Cells.ImportDataTable(dt, true, “A1”);

workbook.Save(“e:\test2\out1.xlsx”);

2) Please create your desired DataTable with your desired column names, now use appropriate ExportDataTable method instead of ExportDataTableAsString to fill that table.

Sample code:
DataTable dt = new DataTable();
dt.Columns.Add(“ID”, typeof(string));
dt.Columns.Add(“Name”, typeof(string));
dt.Columns.Add(“Class”, typeof(string));
dt.Columns.Add(“Name1”, typeof(string));
//etc.
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(@“e:\test2\import.xlsx”);

Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.ExportDataTable(dt,1, 0, worksheet.Cells.MaxDataRow + 1, false);


Aspose.Cells.Worksheet worksheet2 = workbook.Worksheets[1];

worksheet2.Cells.ImportDataTable(dt, true, “A1”);

workbook.Save(“e:\test2\out2.xlsx”);

Hopefully referring to the above examples, you may adopt any approach and change your code accordingly for your requirements accordingly.