Loading CSV data using SQLServerBulkCSVFileRecord with enclosing and nulls

c# csv jdbc sqlbulkcopy sql-server

Question

I'm attempting to import CSV data using the SQLServerBulkCSVFileRecord, however it seems that this does not support enclosed fields (stripping quotes) and does not provide a value for NULL.

Any ideas on how to make this work? It seems that loading a CSV file requires just the most basic capabilities.

I'm now using this code:

final SQLServerBulkCopy copymanager = new SQLServerBulkCopy(connection);
final SQLServerBulkCSVFileRecord csv = new SQLServerBulkCSVFileRecord(datafile.toString(), true);

final ResultSet resultSet = connection.createStatement().executeQuery(
        String.format("select * from [%s].[%s]", getSchema(), tableName));
for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
    csv.addColumnMetadata(i, resultSet.getMetaData().getColumnName(i), resultSet.getMetaData().getColumnType(i),
            resultSet.getMetaData().getPrecision(i), resultSet.getMetaData().getScale(i));
}

copymanager.setDestinationTableName(tableName);
copymanager.writeToServer(csv);
1
0
8/16/2018 1:16:19 PM

Accepted Answer

The code for SQLServerBulkCSVFileRecord contains an interesting comment.

  • Both BCP and BULK INSERT considers double quotes as part of the data and throws error if any data (say "10") is to be
  • inserted into an numeric column. Our implementation does the same.

Therefore, in order to remove quoted values from each row in your code, you must parse each row.

You can either offer a DEFAULT value in the table or do it in your code to supply a value for NULL. Your decision.

0
8/16/2018 1:18:40 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow