Csv to json

I am able to convert csv to json. But with some csv they contain escape character in column value(), due to which it is giving error. Below is the sample csv content, you can just copy and paste in notepad and save it as .csv
Please advice the corrections.

“Id”,“IsDeleted”,“MasterRecordId”,“Salutation”,“FirstName”,“LastName”,“MiddleName”,“Title”,“Company”,“Street”,“City”,“State”,“PostalCode”,“Country”,“Latitude”,“Longitude”,“GeocodeAccuracy”,“Phone”,“MobilePhone”,“Fax”,“Email”,“Website”,“Description”,“LeadSource”,“Status”,“Industry”,“Rating”,“AnnualRevenue”,“NumberOfEmployees”,“OwnerId”,“HasOptedOutOfEmail”,“IsConverted”,“ConvertedDate”,“ConvertedAccountId”,“ConvertedContactId”,“ConvertedOpportunityId”,“IsUnreadByOwner”,“CreatedDate”,“CreatedById”,“LastModifiedDate”,“LastModifiedById”,“SystemModstamp”,“LastActivityDate”,“DoNotCall”,“HasOptedOutOfFax”,“LastTransferDate”,“Jigsaw”,“JigsawContactId”,“EmailBouncedReason”,“EmailBouncedDate”,“IndividualId”,“CurrentGenerators__c”,“NumberofLocations__c”,“Primary__c”,“ProductInterest__c”,“SICCode__c”,“Requested_On__c”,“Decision_Date_Expected__c”,“Sent_On__c”,“et4ae5__HasOptedOutOfMobile__c”,“et4ae5__Mobile_Country_Code__c”,“Close_Date__c”,“Program_Interest__c”,“Details__c”
“00Q4a000001BtwDEAS”,“0”,"","",“John”,“Gribble”,"",“Business Development Consultant”,“Grid Group","","","","","","","","","",“0400156456”,"","john_gribble@bigpond.com”,"","",“Website”,“New”,“Grid Group offers sanitisation expertise to various markets. “,””,"","",“0054a000000Hh1DAAS”,“0”,“0”,"",“000000000000000AAA”,“000000000000000AAA”,“000000000000000AAA”,“1”,“2020-05-20 01:55:48”,“0050o00000WINgGAAX”,“2020-05-20 01:55:48”,“0050o00000WINgGAAX”,“2020-05-20 01:55:48”,"",“0”,“0”,“2020-05-20 01:55:48”,"","","","",“000000000000000AAA”,"","","","","","","","",“0”,"","","",""

Hi @munish ,

I think that the main issue with this sample csv content is the “ ” quote signs.

For that reason, I have tried to replace all “ ” with image and upload the file in SnapLogic.

In this way, I didn’t get any errors and the file is successfully parsed.

Hope this helps.

Regards,
Spiro Taleski

@Spiro_Taleski
commas might be change while coping. thats not the actual case. I am re-copying the csv content
I have change the csv to xls as it was not accepting csv. So now just download the file and just rename with .xls and try again.

samplefile1.xls (1.5 KB)

@munish

You can open the file with Notepad ++ and save it as a csv.

Then, you will notice that there is a special character under “Industry” column:

image

I assume that if you remove that sign, you should be able to successfully parse the file.

Regards,
Spiro Taleski

I know that’s the issue. And the this data is just extracted from very huge csv file. Also there are plenty of other csv files, so practically it is not possible to manually remove this character. That’s why I need you guys advice to resolve without removing it.

@munish

Can you please try to upload the same csv file and try to parse it by setting up a single quote(’) in the “Quote Character” field of the CSV Parser snap.

image

Regards.