Loading CSV data using SQLServerBulkCSVFileRecord with enclosing and nulls

c# csv jdbc sqlbulkcopy sql-server

Question

I'm trying to load CSV data using the SQLServerBulkCSVFileRecord, however it seems that this doesn't allow for enclosed fields (stripping quotes) and supply a value for NULL.

Any suggestions on how to get this working? Seems like such basic functionality needed for loading a CSV file.

Code i'm currently using:

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

Interestingly, the source for SQLServerBulkCSVFileRecord has a 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.

that means you will have to parse each row in your code to unquote quoted values.

As far as supplying a value for NULL, you can do it in your code our supply a DEFAULT value in the table. Your choice.

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