BQ load from csv file stored in storage using java client

Question

Tried to load into existing BQ table from csv file stored in the bucket using google BQ client but ran into this error. Not sure how can I figure out this position in the csv file. I replaced all the NULL strings with "" values and also surrounding each column with double quote. Thougth I solved all issues but ran into this issue and don't know how to get around it. Any help would be appreciated.

"com.google.cloud.bigquery.BigQueryException: Error while reading data, error message: Error detected while parsing row starting at position: 178031. Error: Data between close double quote (") and field separator. File: gs://merch-meter/Merch-Meter-jira-issues-03-31-2023"

This is the code that generates a row in the csv file

public static String createCsvRecord(Issue issue) {
        String sprintstartdate = (issue.getSprintstartdate() == null) ? "" : DateUtil.getUtcDatetime(issue.getSprintstartdate());
        String sprintenddate = (issue.getSprintenddate() == null) ? "" : DateUtil.getUtcDatetime(issue.getSprintenddate());
        String createddate = (issue.getCreateddate() == null) ? "" : DateUtil.getUtcDatetime(issue.getCreateddate());
        String completeddate = (issue.getCompleteddate() == null) ? "" : DateUtil.getUtcDatetime(issue.getCompleteddate());
        String lastupdateddate = (issue.getLastupdateddate() == null) ? "" : DateUtil.getUtcDatetime(issue.getLastupdateddate());
        String issueName = Utils.escapeSpecialCharacters(issue.getIssueName());
        String parentId = StringUtils.isNotBlank(issue.getParentId())? issue.getParentId():"";
        String parentType = StringUtils.isNotBlank(issue.getParentType())? issue.getParentType():"";
        String lastUpdatedUser = StringUtils.isNotBlank(issue.getLastupdateduser()) &&
                !issue.getLastupdateduser().equalsIgnoreCase("null")? issue.getLastupdateduser():"";
        String labels = Utils.escapeSpecialCharacters(issue.getLabels());
        if(StringUtils.isNotBlank(issueName) && StringUtils.contains(issueName, ","))
            issueName = issueName.replace(",", " ");

        StringBuilder csvStringBuilder = new StringBuilder();
        csvStringBuilder.append("\"").append(issue.getTeamid()).append("\",");
        csvStringBuilder.append("\"").append(issue.getJiraboardid()).append("\",");
        csvStringBuilder.append("\"").append(issue.getIssueid()).append("\",");
        csvStringBuilder.append("\"").append(issue.getIssuetype()).append("\",");
        csvStringBuilder.append("\"").append(issue.getSprintId()).append("\",");
        csvStringBuilder.append("\"").append(issue.getStatus()).append("\",");
        csvStringBuilder.append("\"").append(createddate).append("\",");
        csvStringBuilder.append("\"").append(completeddate).append("\",");
        csvStringBuilder.append("\"").append(sprintstartdate).append("\",");
        csvStringBuilder.append("\"").append(sprintenddate).append("\",");
        csvStringBuilder.append("\"").append(lastUpdatedUser).append("\",");
        csvStringBuilder.append("\"").append(lastupdateddate).append("\",");
        csvStringBuilder.append("\"").append(issue.getIssuepoints().intValue()).append("\",");
        csvStringBuilder.append("\"").append(issue.getCreator_emailAddress()).append("\",");
        csvStringBuilder.append("\"").append(issue.getCreator_displayName()).append("\",");
        csvStringBuilder.append("\"").append(issue.getCreator_accountId()).append("\",");
        csvStringBuilder.append("\"").append(issue.isCreator_isActive()).append("\",");
        csvStringBuilder.append("\"").append(issue.getComponents()).append("\",");
        csvStringBuilder.append("\"").append(labels).append("\",");
        csvStringBuilder.append("\"").append(issueName).append("\",");
        csvStringBuilder.append("\"").append(parentId).append("\",");
        csvStringBuilder.append("\"").append(parentType).append("\",");
        csvStringBuilder.append("\"").append(issue.isDeleted()).append("\",");
        csvStringBuilder.append("\"").append(DateUtil.getUtcDatetime(new Date())).append("\"");
        csvStringBuilder.append("\n");
        return csvStringBuilder.toString();
    }

Let me know if you need the csv file and will upload it to G drive after redacting it.

score 1 · Accepted Answer · answered Apr 05 '23 at 19:34

Issue is with escaping double quotes in the csv file. They need to be escaped with another double quotes for double quotes inside the csv field.

"PagerDuty - Research adding attachments to \"Create PD Incident\" shortcut"

they have to be replaced with the below field

"PagerDuty - Research adding attachments to ""Create PD Incident"" shortcut"

BQ load from csv file stored in storage using java client

1 Answers1