I've been using azure not for too long and am learning how ADF works. I'm trying to run a copy activity node in a pipeline that will allow me to write data from an excel csv file(source) which is stored in a data lake gen2 container to a SQL database(target) which i have also created in azure and linked to SSMS. There is only 21 columns in the source file and I created 21 columns in the target database. I also want to mention that there is a duplicate column in the source file. I made a linked service which connected to my source file which is in the gen2 and another linked service that connected to the database and have properly set up the source, sink, and mapping within the copy activity node. When attempting to debug the pipeline i keep getting this error message:
"ErrorCode=DelimitedTextColumnNameNotAllowNull,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The name of column index 22 is empty. Make sure column name is properly specified in the header row.,Source=Microsoft.DataTransfer.Common,'"
I put it into ChatGPT to get a general idea of what the message is telling me and it says that there's an issue with the 22nd index column name in the delimited text file which is the source file. I don't know if the duplicate column is the issue or something else is but I have been stuck on this for 4 days and its kind of driving me crazy. Any help is appreciated
I've tried deleting the duplicate column is hopes that maybe it was the duplicate column that was throwing it off but that did not work. I redid the mapping over and over thinking that maybe there was a column that was missing but nothing came from that.
It seems to be an issue with the formatting of CSV file.
If delimiter character (comma) is part of any of the column data, it could make ADF parser think there are more columns than there are headers for.
As Chen Hirsh commented, double check the CSV file to make sure there are no issues with the CSV file.