How to read an excel without exception when there are duplicate columns inside excel

How to read an excel without exception when there are duplicate columns inside excel

Hi Sravya,

If you have duplicate columns (headers) you can untick the “AddHeaders” property of the read range, which will rename the columns as “Column1”, “Column2”,… for as many columns as you have.

Alternatively if the excel spreadsheet is always the same, you can rename some of the offending column headers before reading the range, thus ensuring they are all unique.

Hope this helps,

1 Like

No Harris,

My requirement includes reading header data as well and I don’t want to change the name of the columns. All doors closed. Please help me with the solution.

You could utilize a try catch. Try read excel with headers. On exception (should put the exact type of exception instead of overall system.exception) read data without headers, iterate through each column in first row (since first row contains the real headers) and change the name if duplicates are found

You could read first the headers … with Read Row for example … and then read all the range without headers.

I don’t think it will throw an exception even there are duplicate columns inside excel.

First you use read range to put all the data into a datatable variable, after that you should use for loop to check which column is duplicated.

At the end, it depends on you want to delete the column or write it to another worksheet.

See, when there are duplicate columns inside excel it wont read the data and will throw the exception for sure. Though i handle it using try-catch block, only exception will be handled. It doesn’t mean that data will be read successfully. So please tell me proper workaround if anyone is aware of this situation

can we have the excel file so that we can have more understanding of it?