Azure Data Factory Copy Data is not parsing CSV correctly. It is splitting at ',' within double quotes.

Question

Azure Data Factory Copy Data is not parsing CSV correctly. It is splitting at ',' within double quotes.

Paul Simon 5

The CSV parser is not working properly. In the below it is splitting "PRI/LTD BY GUAR/NSC (Private, limited by guarantee, no share capital)" into three columns even though double quote text qualifier is selected.

"""BE"" NEWHALL MANAGEMENT COMPANY LIMITED","07326397","","","VANTAGE POINT","23 MARK ROAD","HEMEL HEMPSTEAD","HERTFORDSHIRE","","HP2 7DN","PRI/LTD BY GUAR/NSC (Private, limited by guarantee, no share capital)","Active","United Kingdom","","27/07/2010","31","7","30/04/2024","31/07/2022","DORMANT","24/08/2016","27/07/2015","0","0","0","0","98000 - Residents property management","","","","0","0","http://business.data.gov.uk/id/company/07326397","","","","","","","","","","","","","","","","","","","","","10/08/2023","27/07/2022"

This is data from Companies House.

https://download.companieshouse.gov.uk/BasicCompanyDataAsOneFile-2023-02-01.zip

Does anyone know a way around this?

On the Dataset I have selected a Compression Type ZipDeflate

Comma Delimiter: Comma

Row delimiter: Default \r,\n or \r\n

Encoding UTF-8

Escape Character: Backslash \

Quote Character: Double quote (")

First row as header ticked

Null value: is left blank

I have opened the Part 1 of 7 version of the file in Notepad++ and the CSV Query PlugIn has parsed it correctly, and I didn't need to tell it anything.

Why is a simple thing like this not working in ADF?

Regards

Paul Simon

Paul Simon 5 Reputation points

2023-02-13T16:58:58.1666667+00:00

By the way the error that ADF is giving is that there are more columns than the expected 55. When I look at the output I can see that there are 57 columns because it has incorrectly created columns by splitting at commas within double quotes, which it should be ignoring.
Paul Simon 5 Reputation points

2023-02-13T16:59:32.37+00:00

Question for Microsoft - Are you going to refund the £500 worth of run time that has gone in to finding this?
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-02-14T23:59:00.67+00:00

Hi Paul Simon,

Welcome to Microsoft Q&A forum and thanks for posting your question.

Could you please confirm if you are still blocked with the data issue? For creating the datasets in ADF we will have to set the dataset properties as per your source data format so that ADF can read the data without issues. As there are different data formats with different properties, we have to let ADF know which format and type of properties that dataset should expect the data to be.

Please refer to this document where it explains about the properties that can be configured as per the custom data format need: ADF Delimited Text format - Dataset properties

I just downloaded the file (unzipped) you have provided and was able to copy the data successfully with below dataset configuration:

Pipeline ran successfully and file copied as expected:

In case if you are already unblocked and the intent of the post to get an answer related to the billing which incurred because of this issue, then I would recommend filing a Billing support ticket and the support team can assist you accordingly. Please note that, Azure provides unlimited free support for subscription management, which includes billing, quota adjustments, and account transfers. For technical support, you need a support plan.

Ref doc: Azure Support Plans

Hope this info helps. Please let me know if you have any questions

Thank you
Paul Simon 5 Reputation points

2023-02-15T16:58:49.7+00:00

Sorry the above does not answer the question. Please see the replies below which I have mistakenly put in as answers
Paul Simon 5 Reputation points

2023-02-15T16:59:26.46+00:00

Hi Kranthi

Thanks for the reply. I only saw this because I checked back. Unfortunately we have multiple domains here. My email address is at smartestenergy, but the account I need to use for data warehousing is at dual-energy. I checked my dual-energy profile but could not see a way to specify an email address for correspondence. Is there a way to do that?

I think what you have done is to just access the file via HTTP, unzip it and write it to a file. That clearly has worked, but isn't what I am looking for.

I suggest that you try processing the file, eg just selecting a subset of the fields.

I was originally using identical settings to yours. The problem you will find, if you try processing the file is that it will reject a number of the rows saying that they have more columns that the expected 55. The sample that I gave above was a Part 1 of 7 version of the file to allow it to be processed in smaller chunks. The full file has 4,500,000 records and when I tried processing that about 250,000 records were rejected due to this issue.

http://download.companieshouse.gov.uk/BasicCompanyDataAsOneFile-2023-02-01.zip

I have done a lot of further analysis on the file using VBA, which is slow but at least it works.

Some of the problems in the file are that

a) The terminator is LF rather than CRLF. I did try changing the row delimiter to just \n but still got the same number of columns issue.

I would have expected that this is something that Azure could derive, given that it would only need to look at the first line of the file, search for CR or LF. If LF is found first then it is an LF terminated row. If CR is found first then check to see if the next character is LF, and if so, then it is a CRLF terminated row, otherwise it is a CR terminated row. I don't see how a CSV file could have no row delimiter?

b) I suspect the key issue that is upsetting ADF is that Companies House have allowed companies to use double quotes " in the company name.

Therefore you can have Company Names like

"TRIPLE D" PROPERTIES LIMITED"

Given that the string quote character is also double quote, within the file, this appears as

"""TRIPLE D"" PROPERTIES LIMITED"

in other words the double quote " within the company name appears as two double quotes "" in the file.

Given that some Company Names can also contain commas, I suspect that the combination of the '""' and ',' within the company name field is what is causing ADF to end up splitting the fields incorrectly and therefore generating more than the expected 55 columns. It appears to be merging the Company Name field into the later Company Category field which often has commas, and this tends to result in a line that has 57 columns instead of the expected 55.

(It would be helpful if the ADF error message told you how many fields it had got rather than just telling you there were more than the expected 55).

If we can get a solution to this in ADF that would be useful as the processing speed is likely to be somewhat faster than VBA!

I have also tried importing the file into SQL Server using Task - Import Data, and that has also failed with the column issue, so it seems to be a fairly common issue in MS products.

Incidentally, why is it that in ADF you always have to land an external file into a file or other Data Set before you can do any further processing on it? Why can't the output of the Source side of the Copy Data task be connected directly to the input of a Data Flow task?

I was never a big fan of SSIS, but that approach made rather more sense than the approach in ADF, which seems to use up a lot of disk space unnecessarily.

Regards

Paul Simon
Paul Simon 5 Reputation points

2023-02-15T16:59:42.8633333+00:00

I have tried giving the Escape character on the Source as " and then on the Sink as back slash. It processes the file correctly, but the resulting file then cannot be read by eg SQL Task - Import Data. (I need to get this into SQL Server on prem and the Self-Hosted IR I set up has stopped working - authentication issue).

The file can still be read correctly by Notepad++ CSV Query Plug In.

If it will help I can upload the VBA that I am using to process the file. It works but is too slow. However, it might serve as a spec for how to process a file of this type.

I have also been in contact with Companies House and raised a ticket there to see if the format for the file can be improved.

Regards

Paul
Paul Simon 5 Reputation points

2023-02-15T17:03:10.5266667+00:00

An example row that is causing a problem is

""" BORA "" 2 LTD","13220580","","","26 CARMICHAEL CLOSE","WINSTANLEY ESTATE","LONDON","BATTERSEA","ENGLAND","SW11 2HS","PRI/LTD BY GUAR/NSC (Private, limited by guarantee, no share capital)","Active","United Kingdom","","23/02/2021","28","2","30/11/2023","28/02/2022","MICRO ENTITY","23/03/2022","","0","0","0","0","47820 - Retail sale via stalls and markets of textiles, clothing and footwear","","","","0","0",http://business.data.gov.uk/id/company/13220580,"","","","","","","","","","","","","","","","","","","","","08/03/2023","22/02/2022"
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-02-16T07:16:44.4033333+00:00

@Paul Simon - Thanks for your feedback. And yes, your understanding is correct. The current behavior of dataset depends on the dataset configuration by the user as the format of the file/data may vary from enterprise to enterprise. But if you have any product improvement idea, we recommend submitting a feature request in feedback portal.

As the issue seems to be purely data side issue, the possible options to fix is either to manually correct the data or if you have large amount of data in incorrect format, then request the data source owners to provide the data in expected format or to have a custom automation in place to detect the incorrectly formatted data and correct it as expected and then process through ADF. Unfortunately, I don't see any out of box ADF feature that can handle such data related issues.

PRODUCT FEATURE REQUEST:

We request to please go ahead and log a feature request in the feedback portal here: https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c

Product group does monitor and review the requests and based on the priority of requests, they can plan for implementation in future. Once you log the feature request you will also be notified on the status of the request when respective product acknowledges it.

Once you have logged the feature request in the above feedback portal, please do share link for the same here, so that we can share it with internal teams to review it.

Thank you
Paul Simon 5 Reputation points

2023-02-16T10:19:48.8666667+00:00

An example row that is causing a problem is

""" BORA "" 2 LTD","13220580","","","26 CARMICHAEL CLOSE","WINSTANLEY ESTATE","LONDON","BATTERSEA","ENGLAND","SW11 2HS","PRI/LTD BY GUAR/NSC (Private, limited by guarantee, no share capital)","Active","United Kingdom","","23/02/2021","28","2","30/11/2023","28/02/2022","MICRO ENTITY","23/03/2022","","0","0","0","0","47820 - Retail sale via stalls and markets of textiles, clothing and footwear","","","","0","0",http://business.data.gov.uk/id/company/13220580,"","","","","","","","","","","","","","","","","","","","","08/03/2023","22/02/2022"

1 answer

Your answer

Paul Simon 5 Reputation points

2023-02-13T16:58:58.1666667+00:00

By the way the error that ADF is giving is that there are more columns than the expected 55. When I look at the output I can see that there are 57 columns because it has incorrectly created columns by splitting at commas within double quotes, which it should be ignoring.
Paul Simon 5 Reputation points

2023-02-13T16:59:32.37+00:00

Question for Microsoft - Are you going to refund the £500 worth of run time that has gone in to finding this?
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-02-14T23:59:00.67+00:00

Hi Paul Simon,

Welcome to Microsoft Q&A forum and thanks for posting your question.

Could you please confirm if you are still blocked with the data issue? For creating the datasets in ADF we will have to set the dataset properties as per your source data format so that ADF can read the data without issues. As there are different data formats with different properties, we have to let ADF know which format and type of properties that dataset should expect the data to be.

Please refer to this document where it explains about the properties that can be configured as per the custom data format need: ADF Delimited Text format - Dataset properties

I just downloaded the file (unzipped) you have provided and was able to copy the data successfully with below dataset configuration:

Pipeline ran successfully and file copied as expected:

In case if you are already unblocked and the intent of the post to get an answer related to the billing which incurred because of this issue, then I would recommend filing a Billing support ticket and the support team can assist you accordingly. Please note that, Azure provides unlimited free support for subscription management, which includes billing, quota adjustments, and account transfers. For technical support, you need a support plan.

Ref doc: Azure Support Plans

Hope this info helps. Please let me know if you have any questions

Thank you
Paul Simon 5 Reputation points

2023-02-15T16:58:49.7+00:00

Sorry the above does not answer the question. Please see the replies below which I have mistakenly put in as answers
Paul Simon 5 Reputation points

2023-02-15T16:59:42.8633333+00:00

I have tried giving the Escape character on the Source as " and then on the Sink as back slash. It processes the file correctly, but the resulting file then cannot be read by eg SQL Task - Import Data. (I need to get this into SQL Server on prem and the Self-Hosted IR I set up has stopped working - authentication issue).

The file can still be read correctly by Notepad++ CSV Query Plug In.

If it will help I can upload the VBA that I am using to process the file. It works but is too slow. However, it might serve as a spec for how to process a file of this type.

I have also been in contact with Companies House and raised a ticket there to see if the format for the file can be improved.

Regards

Paul
Paul Simon 5 Reputation points

2023-02-15T17:03:10.5266667+00:00

An example row that is causing a problem is

""" BORA "" 2 LTD","13220580","","","26 CARMICHAEL CLOSE","WINSTANLEY ESTATE","LONDON","BATTERSEA","ENGLAND","SW11 2HS","PRI/LTD BY GUAR/NSC (Private, limited by guarantee, no share capital)","Active","United Kingdom","","23/02/2021","28","2","30/11/2023","28/02/2022","MICRO ENTITY","23/03/2022","","0","0","0","0","47820 - Retail sale via stalls and markets of textiles, clothing and footwear","","","","0","0",http://business.data.gov.uk/id/company/13220580,"","","","","","","","","","","","","","","","","","","","","08/03/2023","22/02/2022"
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-02-16T07:16:44.4033333+00:00

@Paul Simon - Thanks for your feedback. And yes, your understanding is correct. The current behavior of dataset depends on the dataset configuration by the user as the format of the file/data may vary from enterprise to enterprise. But if you have any product improvement idea, we recommend submitting a feature request in feedback portal.

As the issue seems to be purely data side issue, the possible options to fix is either to manually correct the data or if you have large amount of data in incorrect format, then request the data source owners to provide the data in expected format or to have a custom automation in place to detect the incorrectly formatted data and correct it as expected and then process through ADF. Unfortunately, I don't see any out of box ADF feature that can handle such data related issues.

PRODUCT FEATURE REQUEST:

We request to please go ahead and log a feature request in the feedback portal here: https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c

Product group does monitor and review the requests and based on the priority of requests, they can plan for implementation in future. Once you log the feature request you will also be notified on the status of the request when respective product acknowledges it.

Once you have logged the feature request in the above feedback portal, please do share link for the same here, so that we can share it with internal teams to review it.

Thank you
Paul Simon 5 Reputation points

2023-02-16T10:19:48.8666667+00:00

An example row that is causing a problem is

""" BORA "" 2 LTD","13220580","","","26 CARMICHAEL CLOSE","WINSTANLEY ESTATE","LONDON","BATTERSEA","ENGLAND","SW11 2HS","PRI/LTD BY GUAR/NSC (Private, limited by guarantee, no share capital)","Active","United Kingdom","","23/02/2021","28","2","30/11/2023","28/02/2022","MICRO ENTITY","23/03/2022","","0","0","0","0","47820 - Retail sale via stalls and markets of textiles, clothing and footwear","","","","0","0",http://business.data.gov.uk/id/company/13220580,"","","","","","","","","","","","","","","","","","","","","08/03/2023","22/02/2022"

Answer 1

Hi Kranthi

Firstly, I would regard this as a bug rather than a feature request. If a free product like Notepad++ can open and correctly parse this CSV file then surely Microsoft's rolls-royce ETL product, ADF should be able to do this?

This is a CSV file that has been produced by Companies House which holds records on every UK company. It could probably be better formatted and I have raised that with them. However, I am not holding out much hope for a speedy solution from either Companies House or Microsoft. We need to have Company Registration Numbers to comply with a government return in two weeks time.

The key issue seems to be that the file has used "" when a company name contains ". The overall number of double quotes in a field is still balanced, ie there will always be an even number of double quotes.

I have tried accessing the link to post a feature request.

https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c

However, it will not allow me to login. If I click Sign In it immediately brings up a dialog box telling me that I need to register but my email address is already registered and therefore I cannot register with that email address, but if it is already registered then I should be able to sign in with that email address.

If you can resolve that issue then I will be happy to post a feature request

Regards

Paul Simon

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2023-02-23T09:14:28.7733333+00:00

@Paul Simon Appreciate much for trying to log the feedback in feedback portal and sorry for your experience. I just tried to login to the feedback portal and was able to login without issues. Have you tried logging into the feedback portal from different browser and see if that allows you to login? If you are still blocked, please share the feedback verbatim here and I can log it on behalf of you and share the link here so that other users with similar feedback can up-vote and/or comment on it

Share via

Azure Data Factory Copy Data is not parsing CSV correctly. It is splitting at ',' within double quotes.

1 answer

Your answer