CODEWITHSUNDEEP
javasql-serversqlbulkcopy
Michael Robinson
Michael Robinson

Reputation: 1142

Trouble with importing CSV values into a SQL Server DECIMAL field

I'm using SQLServerBulkCopy to import CSV files into a database. The code is:

private void bulkCopy(String tableName, String csvFileName) throws Exception {
    Integer rowCount = 0;        
    Instant copyStart = Instant.now();

    try {
        Integer column = 0;
        SQLServerBulkCopy copier = new SQLServerBulkCopy(dbConnection);
        SQLServerBulkCSVFileRecord fileRecord = new SQLServerBulkCSVFileRecord(csvFileName, true);
        Statement statement = dbConnection.createStatement();

        // Set the metadata for the column to be copied, as well as mapping the columns.
        for (String thisColumn : commonColumns) {
            column += 1;
            Integer index = tableData.columns.indexOf(thisColumn);
            Integer size = tableData.sizes.get(index);
            Integer type = getType(tableData.types.get(index));
            
            fileRecord.addColumnMetadata(index + 1, thisColumn, type, size, 0);
            copier.addColumnMapping(thisColumn, thisColumn);
        }
        // System.exit(0);

        copier.setDestinationTableName(tableName);
        copier.writeToServer(fileRecord);
        rowCount = getRowCount(statement, tableName);
        copier.close();
    }
    catch (SQLException e){
        throw e;
    }
    Instant copyEnd = Instant.now();
    Duration elapsed = Duration.between(copyStart, copyEnd);
    logger.info("DEBUG: file is " + csvFileName);

    logger.info("It took " + utils.formatDuration(elapsed) + " to write " + rowCount + " rows to the " + tableName + " table.");
}

The problem is a DECIMAL(16,2) field with a 100 in the CSV file. When I try to run my code, I get this error:

com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred while converting the '"100"' value to JDBC data type DECIMAL.

I have no idea how to keep this error from happening.

Upvotes: 0

Views: 422

Answers (2)

MNB
MNB

Reputation: 549

I got around the same problem by adding a line of code to the setup of the SQLServerBulkCSVFileRecord object

SQLServerBulkCSVFileRecord fileRecord = new SQLServerBulkCSVFileRecord(csvFileName, true);
fileRecord.setEscapeColumnDelimitersCSV(true);

i.e. turning on escaping activates the handling of quotes, and conformance to RFC4180 on parsing the CSV data.

thanks to Andreas Radauer in the other answer (see here) for inspiring this answer.

Upvotes: 0

Andreas Radauer
Andreas Radauer

Reputation: 1133

I think the problem is that your number is not a number in your file. It is a String with the value 100.

Can you change the CSV File to have 100 instead of "100" or better can you activate the escaping of field delimters in this SQLServerBulkCopy Object? Is it this? https://github.com/microsoft/mssql-jdbc/blob/17569c6aa1347d1c691581055c4f7a98fc0ab813/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerBulkCSVFileRecord.java#L555

Upvotes: 2

Related Questions