Loading CSV data using SQLServerBulkCSVFileRecord with enclosing and nulls

c# csv jdbc sqlbulkcopy sql-server


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

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.

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