question

samnick-7321 avatar image
0 Votes"
samnick-7321 asked KranthiPakala-MSFT commented

Parsing CSV failing from sftp to blob

Hi,
i am doing a straight copy of a csv from an sftp site to the blob container , but keep getting the below error.

Failure happened on 'Sink' side. ErrorCode=DelimitedTextMoreColumnsThanDefined,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error found when processing 'Csv/Tsv Format Text' source '20210408072904_Employee Learning Report_sftp_e29270be_.csv' with row number 190: found more columns than expected column count 46.,Source=Microsoft.DataTransfer.Common,'

Below are the setting of the datasest on both source and sink . Exactly the same.

85951-image.png


The row that seems to have an error looks like this, with a single quote and a double quote in the same column. Please advise how to resolve this?

![85897-image.png][2]


azure-data-factory
image.png (27.6 KiB)
image.png (7.5 KiB)
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.

nasreen-akter avatar image
0 Votes"
nasreen-akter answered nasreen-akter edited

Hi @samnick-7321,

Please change the Escape character to " instead of "\". Here is a similar thread about double quote issue in the CSV file. Hope this helps! Thanks! :)

https://docs.microsoft.com/en-us/answers/questions/218105/issue-converting-parquet-format-to-csv-in-adf.html

· 2
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.

@nasreen-akter - the provided link doesnt work :(

0 Votes 0 ·

@samnick-7321, please check the link now. Thanks!

0 Votes 0 ·
KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered KranthiPakala-MSFT commented

Hi @samnick-7321,

Thanks for your reaching out and sharing the source data which helped to understand the issue better. It seems like the issue is with source data -> column Name values.

By looking at the source dataset configuration your columnDelimiter is comma and by looking at your source data, your first column Name has First Name and Last Name separated by comma. Which means that source dataset will treat these values as two different column values which is why you are receiving an error saying DelimitedTextMoreColumnsThanDefined which is a valid error message.

After changing source data, it should look like:

85982-image.png

In order to fix this issue your source data has to be fixed for column Name. The First Name and Last Name value should be enclosed in double quote as shown in above image and the source dataset should be configured as below:

85899-image.png

And your sink dataset should be configured as below to avoid the error.

85900-image.png

Hope this info helps. Do let us know if you have further query.



Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.



image.png (18.0 KiB)
image.png (36.9 KiB)
image.png (38.2 KiB)
· 4
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.

@KranthiPakala-MSFT , unfortunately, i cannot modify the source file. this is a client provided file, that we are supposed to ingest into our system.

0 Votes 0 ·

Hi @samnick-7321,

Thanks for your response. Could you please confirm if your sink is also a csv file? If yes, please confirm if you just want to copy the source data as-is? If you want to copy data as-is from source SFTP to your sink Storage then you don't have to do explicit mapping which would avoid this issue. In case if you want to do explicit mapping then the source data has to updated/formatted correctly by staging the data in your system in order to pass the validation of column mapping.

You can try doing auto mapping in the mapping section of your copy by clicking clear button.

85993-image.png

Do let us know if this info helps or if you have further query.


0 Votes 0 ·
image.png (12.7 KiB)

Hi @samnick-7321,

Checking to see if you have got a chance to see above response. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

Thank you

0 Votes 0 ·

Hi @samnick-7321,

We still have not heard back from you. Just wanted to check if you are still facing the issue or need assistance on this? In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue. Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

0 Votes 0 ·