question

Abhishek-6288 avatar image
0 Votes"
Abhishek-6288 asked ShaikMaheer-MSFT commented

ErrorCode=DelimitedTextMoreColumnsThanDefined

There is a column at the end and that is coming empty from the SFTP. But there is an extra comma in the SFTP CSV which is causing this issue.
Can we somehow trim the extra comma(delimiter) which is causing failure in the mapping?
Adding this for your reference to understand my issue.

https://answers.microsoft.com/en-us/msoffice/forum/all/csv-file-has-extra-commas/bc07501b-3a16-4027-be31-da9f37734b5c

The complete error message is as follows:

ErrorCode=DelimitedTextMoreColumnsThanDefined,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error found when processing 'Csv/Tsv Format Text' source 'rma_20211022122932_000001.csv' with row number 2: found more columns than expected column count 9.,Source=Microsoft.DataTransfer.Common

Thanks and Regards,
Abhishek

azure-data-factory
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Abhishek-6288 ,
Just checking in to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hi @ShaikMaheer-MSFT . I think removing commas this way will be cumbersome so I didn't give it a shot. Because we don't have to remove all characters but comma in the last column when it is empty. I communicated to the other side to correct the CSV files on their end.

0 Votes 0 ·

Hi @Abhishek-6288 ,

Ya if CSV files itself gets correct then we are good I believe.

0 Votes 0 ·
Samy-7940 avatar image
0 Votes"
Samy-7940 answered Abhishek-6288 commented

Hi @Abhishek-6288 Please go through below post. The issue looks similar:

Error code: DelimitedTextMoreColumnsThanDefined Azure Data Factory:

https://stackoverflow.com/questions/65040758/error-code-delimitedtextmorecolumnsthandefined-azure-data-factory Thanks

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have gone through that post but that is not going to help because my last column is empty. How does setting the escape character as a double quote(") will help me?

0 Votes 0 ·
Samy-7940 avatar image
0 Votes"
Samy-7940 answered Abhishek-6288 commented

Hi @Abhishek-6288 , Below are the possible causes for this particular error as per documentation, however , if the

column is empty from source side, couldn't you get rid of it in mapping setting?


144059-image.png



image.png (61.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

But it is not necessary to be empty for every row. This means that the column is necessary to be in the schema.
For example if my spreadsheet was like:

Matt | Mary | John | Tom | James

5 | 7 | 6 | 4 | 8

Yes | Yes | | |

The CSV file will look like:

Matt,Mary,John,Tom,James

5,7,6,4,8

Yes,Yes,,,
I don't want the three commas after the second "yes" to be there. Because the pipeline in adf is interpreting as another column in the end. So I want to trim the comma from the end of my file for every row.

0 Votes 0 ·
Samy-7940 avatar image
0 Votes"
Samy-7940 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.