Excel Read Rangee

I have excel sheet where I have two column of same name…
While I use read range, it shows the error that the columnname belongs to this datable,
Please help me in resolving the issue

1 Like

Do you have two columns with the same name in the excel @Sweety_Girl?

1 Like

Yup…

1 Like

yah if we have two column names similar we would get this error
better uncheck the ADD HEADERS property and try
but to refer each column we need to column index only where 0 is the column index for first column
Cheers @Sweety_Girl

1 Like

Yes… But we can you the column index, if the column name is in the same order.
In other case?
Any other way?

kindly come again once pls
Cheers @Sweety_Girl

If the column order changes? we will not be able to process correct!

1 Like

yes correct
but we got to go for index if the columnnames are same buddy
and make sure that they are in order
Cheers @Sweety_Girl

Anyway that we can rename the duplicate column?

  1. Using VB code, you can loop through all the column names
  2. Then add the columns to array or collection while iterating
  3. Before adding the value to the collection, check the array or collection contains the same value
  4. If exists, we will have the index , so change the name of the column

This can be done using the VB code in the InvokeCode activity @Sweety_Girl

Let me know if you want code for it

How to change the name of the duplicate column?

1 Like

When the value exists in the array, add a specific value or any thing to the name @Sweety_Girl

Any link for VB code

You can do it on your own as we have lot of threads available online. But here is the code… @Sweety_Girl

Make sure you have installed Microsoft.Office.Interop.Excel package

     Dim excel As Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook
    Dim ws As Microsoft.Office.Interop.Excel.Worksheet
        excel = New Microsoft.Office.Interop.Excel.Application

   wb = excel.Workbooks.Open(File full path, [ReadOnly]:=False)
    excel.Visible = True

    ws = CType(wb.Sheets(Sheet name), Microsoft.Office.Interop.Excel.Worksheet)
    ws.Activate()
   Dim MTUsedColumnsRange As Integer = ws.UsedRange.Columns.Count
  Dim arrayColNames(MTUsedColumnsRange) As String
    For c As Integer = 1 To MTUsedColumnsRange
        Dim colName As String = CType(ws.Cells(1, c), Microsoft.Office.Interop.Excel.Range).Value.ToString
        If arrayColNames.Contains(colName) Then
            colName = colName + "2"
   CType(ws.Cells(1, c), Microsoft.Office.Interop.Excel.Range).Value = colName
        End If
        arrayColNames(c) = colName
    Next

    wb.Save()
    wb.Close()
    excel.Quit()
2 Likes

Thank you, I will work it and ping you about it

Capturedatsss
@HareeshMR This error

kindly have a view on this

Cheers @Sweety_Girl

I have nearly 40+ columns in it

1 Like

so were we facing any issue due to that
Cheers @Sweety_Girl

In that they have only 2 column are check the 2column name in if

Here we have like 40+ column, can we apply condition for 40 column in single if

1 Like