Question
I run into some issues trying to map my column metadata with SQL Server Bulk Copy API and SQLServerBulkCSVFileRecord. Just for test purposes I made a table consisting of only nvarchar(500) columns and add the metadata like this:
fileRecord = new SQLServerBulkCSVFileRecord(csvPath, false);
for(int i=1; i<=colCount; i++) {
fileRecord.addColumnMetadata(i, null, java.sql.Types.NVARCHAR, 500, 0);
}
I get the following stacktrace after using the Microsoft SQL bulk copy API with JDBC and I can't find any documentation on SQLServerBulkCSVFileRecord. I don't know what the parameters in addColumnMetaData stand for: I just assumed looking at this example that the first parameter stands for column index and then obviously the third one for data-type, the fourth being byte count of the column(?).
com.microsoft.sqlserver.jdbc.SQLServerException: Unicode data is odd byte size for column 1. Should be even byte size.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:251)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:81)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:36)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.doInsertBulk(SQLServerBulkCopy.java:1433)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.access$200(SQLServerBulkCopy.java:41)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy$1InsertBulk.doExecute(SQLServerBulkCopy.java:666)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.sendBulkLoadBCP(SQLServerBulkCopy.java:699)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:1516)
at com.microsoft.sqlserver.jdbc.SQLServerBulkCopy.writeToServer(SQLServerBulkCopy.java:616)
I read that blank lines, non CRLF line endings, encoding etc. could have an impact but I feel like I've exhausted those options.
Finally here's a little sample of my CSV file:
column1|test|1|testtest|test3
column2|test|2|testt46426est|test346
column3|test|3|test4test|test3426234
Accepted Answer
You are not specifying the pipe character as your field delimiter. Note that it needs to be escaped as "\\|" because, according to the documentation:
The delimiter specified for the CSV file should not appear anywhere in the data and should be escaped properly if it is a restricted character in Java regular expressions.
I just tried the following code and it worked for me:
String csvPath = "C:/Users/Gord/Desktop/sample.txt";
SQLServerBulkCSVFileRecord fileRecord =
new SQLServerBulkCSVFileRecord(csvPath, null, "\\|", false);
int colCount = 5;
for (int i = 1; i <= colCount; i++) {
fileRecord.addColumnMetadata(i, null, java.sql.Types.NVARCHAR, 50, 0);
}
try (SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(conn)) {
bulkCopy.setDestinationTableName("dbo.so41144967");
try {
// Write from the source to the destination.
bulkCopy.writeToServer(fileRecord);
} catch (Exception e) {
// Handle any errors that may have occurred.
e.printStackTrace();
}
}