0

We get a tab delimited CSV from COGNOS External system in a public folder. This fails to upload to Salesforce via Dataloader CLI.

com.salesforce.dataloader.exception.DataAccessRowException: Error reading row #0: the number of data columns (98) exceeds the number of columns in the header (97)

But if you open the csv in MS Excel, and save as a new CSV (UTF-8) and then pass it to data loader CLI it works without any issue.

The difference in EXCEL converted file seems to be it's Comma separated instead of Tab.

Then I tried to convert Original Tab Delimited CSV to Comma separated CSV using below command,

import-csv source.csv -delimiter "`t" | export-csv target.csv -notype

But the output of this has quotes, Data Loader now runs with the File, but imports nothing into Salesforce, it seems it's not able to identify field-names properly.

Then I tried below command to remove the double quotes,

import-csv source.csv -delimiter "`t" | export-csv target.csv -notype
(Get-Content target.csv) | Foreach-Object {$_ -replace '"', ''}|Out-File target.csv

But this resulted in an Index out of range error, which is not clear.

What would be the best approach to do this conversion for Data Loader CLI? What can make this conversion same as EXCEL's conversion?

Highly appreciate Any suggestions, thoughts, help to achieve this.

Thanks!

Pasan Eeriyagama
  • 277
  • 1
  • 4
  • 15

1 Answers1

1

SalesForce has strict rules for CSV files. Also, on this page it says that no more than 50000 records can be imported at one time.

Main thing here is that the file MUST be in UTF8 format. The quotes around the values are needed.

This should do it (provided you do not have more than 50000 records in the Csv):

Import-Csv -Path 'source.csv' -Delimiter "`t" | Export-Csv -Path 'target.csv' -Encoding UTF8 -NoTypeInformation

(source.csv is the TAB-delimited file you receive from COGNOS)

Theo
  • 57,719
  • 8
  • 24
  • 41
  • Hi @Theo, Thanks for the suggestion, I tried it. It's now throwing me error csv.CSVFileReader readHeaderRow (CSVFileReader.java:274) - Error getting header row from the CSV file. com.sforce.async.CSVReader$CSVParseException: Found unescaped quote. A value with quote should be within a quote at com.sforce.async.CSVReader.nextRecordLocal(CSVReader.java:234) . But as far as I can see there seems no unescaped quote in the header row. – Pasan Eeriyagama Aug 04 '20 at 12:58
  • @PasanEeriyagama Could you show us the first 3 or 4 lines of the source.csv file (open in Notepad and copy/paste in the question, not in a comment)? Of course, change personal data into fake data.. – Theo Aug 04 '20 at 13:12
  • BTW, I found [this link](https://developer.salesforce.com/forums/?id=9060G000000MTcmQAG) with the same issue, but unfortunately it was not answered. – Compo Aug 04 '20 at 13:20
  • @PasanEeriyagama Hm.. seems to be a bug in the SalesForce bulk upload with csv files then.. Have you tried importing records as XML? There is a sample file [here](https://developer.salesforce.com/docs/atlas.en-us.198.0.api_asynch.meta/api_asynch/datafiles_xml_sample_file.htm) – Theo Aug 04 '20 at 13:29
  • 1
    @PasanEeriyagama Could it be perhaps the SalesForce csv loader does not understand the UTF8 ByteOrderMark? If you have PowerShell 7, `Export-Csv` supports value `utf8NoBOM` for the `Encoding` parameter. Otherwise, you can try cutting off the first three bytes of the file. (you can use [Notepad++](https://www.hesk.com/knowledgebase/index.php?article=87) for that to test. – Theo Aug 04 '20 at 14:22
  • All the fields get this error, but surprising thing is if you open this source csv from EXCEL and save as CSV-utf8 and then feed to data loader cli, it is getting imported via data loader even without double quotes. – Pasan Eeriyagama Aug 04 '20 at 21:18