• Post Reply Bookmark Topic Watch Topic
  • New Topic

Creation of Batches from CSV resulting in erroneous state  RSS feed

 
sanchit dua
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm trying to make batches from a CSV file written using CSVWriter of opencsv as:


And BufferedReader to read the written file. The Csv file is written and I think read operation also goes well. So, far its working good. But when I chose particular data to be written to Csv using the same operations, creation of batches comes under error out of it.
An Exception is coming stating "Failed to parse CSV. Found unescaped quote. A value with quote should be within a quote" which is making the Application to not behave in a manner expected.

After going through this error it seems there's some ""(double quote) or "(double quote) symbol present in the data. (I 've the data in form of "asdf","1.0","",,"def").
As far as my understanding I tried to apply Regex to find double quotes but couldn't find any, as after examining the file it doesn't contain the repeated double quotes. The link I followed is:http://stackoverflow.com/questions/3180842/regular-expression-to-find-and-replace-unescaped-non-successive-double-quotes-in

Thereafter in the code, I'm making use of: File tmpFile = File.createTempFile("bulkAPIInsert", ".csv"); to hold the data in a temporary file and then deleting it.

After replacing the above code with the following I somehow handled the coming exception but it futher lead to another one stating "Failed to parse CSV. EOF reached before closing an opened quote".
File tmpFile = new File("bulkAPIInsert.csv");


I don't think the above workaround should be followed as it would be performance issues with the application.

By going through the CSVReader class I found a custom exception defined stating exactly the same Exception as I got. But I think it comes when a double quote is found within some double qoute (the cell value of CSV File). I referred the link as: https://github.com/mulesoft/salesforce-connector/blob/master/src/main/java/com/sforce/async/CSVReader.java

Can anybody suggest me where I'm doing wrong or any workaround for this Problem?

I'm sharing you the code snippet as:
Method1 then Method2 is called.



The getStateMessage() method of BatchInfo gives the discussed error messages.
 
Ulf Dittmer
Rancher
Posts: 42972
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sounds more like a data problem than a code problem. Post the particular line of data that is causing the problem.
 
sanchit dua
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your reply Ulf Dittmer.
Yes, exactly it is data problem. I'm not able to discover what is causing behind this. Can you please let me know how can I perform its search from a CSV file of 28k records.
 
Paul Clapham
Sheriff
Posts: 22521
43
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, you could change the code to output the line which was just read, when the exception is thrown. Or you could run the code in debug mode and watch the lines being read. Or you could print the lines out as they are read, and then look at the last one when the exception is thrown. There's all kinds of possibilities. But the general principle behind all of them is this: When an exception message says there's a problem with X, then you should do something to allow you to look at X. Trying to guess the problem is much less useful than actually looking at the thing which causes the problem.
 
sanchit dua
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I tried deleting some csv records from bottom, up-till a point came when I delete that record, a batch gets created with "numberRecordsProcessed=0" and it waits saying "waiting results-1" and then nothing happens. The record also doesn't look malicious.. Its something like "3745","TEST TEST1
12345 TEST TEST
TEST, TEST 43215","DPPI-3745"
And if I don't delete this record the error message still comes stating "Failed to parse CSV. Found unescaped quote. A value with quote should be within a quote"
 
Ulf Dittmer
Rancher
Posts: 42972
73
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
CSV files can contain newlines (\n) in data cells (as your data obviously has); does the CSV parser account for that? That also explains why removing a single line doesn't improve matters - one CSV line is split over multiple text lines.
 
sanchit dua
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But the parsing logic for other files having newlines (\n) in data is working fine. Before writing data to csv file, I've assured there are no " (quotes) present. There is something malicous going on in Writing of file, Reading of file or Creating Temporary file. Regarding Manual Search of "qoute:
From last searching backwards for few records manually, I 've not found missing quotes.
Regarding Programmatical Search of "qoute:
For searching "qoute within quote, I'm not able to find them normally, for this I need to manually insert \ before "qoute. e.g "abc"",,"def" will not be searched instead by doing "abc\"",,"def".
 
Ulf Dittmer
Rancher
Posts: 42972
73
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If this was my problem I'd use a CSV library to read the data instead of rolling my own. You're already using CSVWriter; why not use CSVReader as well?
 
sanchit dua
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for all helping me out thinking out-of-box. The problem has been resolved by removing line-breaks for each cell.
Cheers
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!