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);
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.