Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

I'm trying to make batches from a CSV file written using CSVWriter of opencsv as: CSVWriter writer = new CSVWriter(new FileWriter(filePath+createFileName), ',', CSVWriter.DEFAULT_QUOTE_CHARACTER);

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.

    Method1: private List<BatchInfo> createBatchesFromCSVFile(RestConnection connection,
            JobInfo jobInfo, String csvFileName) throws Exception {
        List<BatchInfo> batchInfos = new ArrayList<BatchInfo>();
        BufferedReader rdr = new BufferedReader(new InputStreamReader(
                new FileInputStream(csvFileName)));

        // read the CSV header row
        String hdr = rdr.readLine();
        byte[] headerBytes = (hdr + "\n").getBytes("UTF-8");
        int headerBytesLength = headerBytes.length;
//      I was making use of the following code which I replaced with the next line of code.
//      File tmpFile = File.createTempFile("bulkAPIInsert", ".csv");
        File tmpFile = new File("bulkAPIInsert.csv");
        // Split the CSV file into multiple batches
        try {
            FileOutputStream tmpOut = new FileOutputStream(tmpFile);
            int maxBytesPerBatch = 10000000; // 10 million bytes per batch
            int maxRowsPerBatch = 10000; // 10 thousand rows per batch
            int currentBytes = 0;
            int currentLines = 0;
            String nextLine;

            while ((nextLine = rdr.readLine()) != null) {
                byte[] bytes = (nextLine + "\n").getBytes("UTF-8"); //TODO
                if (currentBytes + bytes.length > maxBytesPerBatch
                        || currentLines > maxRowsPerBatch) {
                    createBatch(tmpOut, tmpFile, batchInfos, connection, jobInfo);
                    currentBytes = 0;
                    currentLines = 0;
                }
                if (currentBytes == 0) {
                    tmpOut = new FileOutputStream(tmpFile);
                    tmpOut.write(headerBytes);
                    currentBytes = headerBytesLength;
                    currentLines = 1;
                }
                tmpOut.write(bytes);
                currentBytes += bytes.length;
                currentLines++;
            }

            if (currentLines > 1) {
                createBatch(tmpOut, tmpFile, batchInfos, connection, jobInfo);
            }
        } finally {
            if(!tmpFile.delete())
                tmpFile.deleteOnExit();
            rdr.close();
        }
        return batchInfos;
    }

/**
     * Wait for a job to complete by polling the Bulk API.
     */
    Method2: private void awaitCompletion(RestConnection connection, JobInfo job,
            List<BatchInfo> batchInfoList) throws AsyncApiException { 
        try{
            /****
            Some code
            **/
                BatchInfo[] statusList = connection.getBatchInfoList(job.getId())
                .getBatchInfo();
                for (BatchInfo b : statusList) {
                    if (b.getState() == BatchStateEnum.Completed) {
                        if (incomplete.remove(b.getId())) 
                            //Do Something
                    }
                    else if(b.getState() == BatchStateEnum.Failed){ 

                        System.out.println("Reason: "+b.getStateMessage()+".\n  " +
                                "Number of Records Processed: "+b.getNumberRecordsProcessed());
                        throw (new Exception(""));
                    }
                }
            }
        }catch(Exception ex){log.debug(" Exception occurred.");}
    }

The getStateMessage() method of BatchInfo gives the discussed error messages.

share|improve this question
    
Instead of deleting the temp file (tmpFile) can you leave it and then inspect the contents to make sure that it has valid CSV data? That error sounds like you have an opening quote that is never closed, so the processing that is looking for the closed quote finds the End of the File (EOF) and then generates the error. –  Peter Knolle Jan 29 '13 at 23:46
    
Yes, I can understand but how can I make sure from CSV file of around 28k records to find missing closed quote of any cell. Or you can suggest me for efficient way of finding it. –  sanchit Jan 30 '13 at 3:36
    
Won't it be the last one since it hits the end of the file? You should be able to search backwards. You could search the file (e.g., grep) or open it in some text editor that can handle 28k and search backwards or cut the last part of the file into another file and search it. –  Peter Knolle Jan 30 '13 at 11:13
    
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". –  sanchit Jan 30 '13 at 13:32
    
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 "1234","Test Test Test 1234 Test Test Test, Test 432145","ABCD-1234". 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" –  sanchit Feb 1 '13 at 11:10
show 1 more comment

2 Answers 2

Did you tried Marty Y. Chang CSVReader? I have also a process that reads a csv file using a batch apex process and I didn't get any issue with this code. Just take into account:
1. The scope argument in the execute method should be a List<String> where each String would be a whole row of your file. With this data you can call easily
List<list> csvLines = CSVReader.readCSVFile(csvFile,m_parser);
2. To get this result, you would also need to call a method that returns the List that should be in a new class that implements Iterator and Iterable.

share|improve this answer
    
I'm trying to do the CSV operations using Java application to upload batch of records for an object using Bulk API. Does it mean, I should make use of CSVReader instead of BufferedReader or I should use this code in some other way out? –  sanchit Jan 30 '13 at 9:26
add comment
up vote 0 down vote accepted

Thanks for all helping me out thinking out-of-box.

Answer

The problem has been resolved by removing line-breaks for each cell.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.