Answered by:
SQLServerBulkCopy not working for "|" delimiter

Question
-
Anyone used the SQLServerBulkCopy to load a "|" delimiter into SQLServer? I tried both CSV format and "|" delimiter format. CSV format works. But "|" delimiter format is not working. Please see my code and test results below.
-------------------------------
package com.jdbc.bulkcopy;
import java.sql.*;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCSVFileRecord;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
public class bulkcopy_test1 {
public static void main(String[] args)
{
String connectionUrl = "jdbc:sqlserver://myserver.com\\dev_server";
String userid = "xxxxxxxx";
String passwd = "xxxxxxxxx";
SQLServerBulkCSVFileRecord fileRecord = null;
Connection con = null;
try
{
con = DriverManager.getConnection(connectionUrl, userid, passwd);
String delimitor;
delimitor="|";
String decode=null;
fileRecord = new SQLServerBulkCSVFileRecord("C:\\aa\\ccc.bcp",decode,delimitor, false);
fileRecord.addColumnMetadata(1, null, java.sql.Types.NVARCHAR, 20, 0);
fileRecord.addColumnMetadata(2, null, java.sql.Types.NVARCHAR, 50, 0);
SQLServerBulkCopy bulkCopy = new SQLServerBulkCopy(con);
bulkCopy.setDestinationTableName("tempdb..Table_2");
// Write from the source to the destination.
bulkCopy.writeToServer(fileRecord);
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
finally {
if (fileRecord != null) try { fileRecord.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
System.out.println("done....");
}
}Table has two columns:
tempdb..Table_2 (
name(nvarchar(20, null),
address(nvarchar(50), null)
)
File ccc.bcp:
-----------
cat|aaaaaaa
dog|bbbbbbb
After run the program
Select * from tempdb..Table_2
name address
--------------------------
c a
d o
It bcp 1 character of the first filed to each column.
Thursday, June 23, 2016 3:42 PM
Answers
-
Hi Sam,
Both ways are working fine. Thank you very much for your help.
I have another thread regarding "
sqljdbc42.jar not working in Unix"
No sure if you have any suggestions.
Thanks again!
- Marked as answer by SZZhang Wednesday, July 6, 2016 4:47 PM
Wednesday, July 6, 2016 4:47 PM
All replies
-
Is it possible to provide the two original files?
- Edited by Riaon Friday, June 24, 2016 10:05 AM
Friday, June 24, 2016 10:05 AM -
Hi Riaon,
The csv file will be like this (comma divided):
-------------------------
cat,aaaaaaa
dog,bbbbbbb
-----------------------
And I used the following and it works fine:
fileRecord = new SQLServerBulkCSVFileRecord("C:\\aa\\ccc.csv", false);
Select * from tempdb..Table_2
name address
----------------------
cat aaaaaaa
dog bbbbbbb
Tuesday, June 28, 2016 2:21 AM -
I tested with other delimitors like @ or &. They both working fine. Why the | not working ?!
Tuesday, June 28, 2016 4:18 PM -
-
Hi Sam,
Both ways are working fine. Thank you very much for your help.
I have another thread regarding "
sqljdbc42.jar not working in Unix"
No sure if you have any suggestions.
Thanks again!
- Marked as answer by SZZhang Wednesday, July 6, 2016 4:47 PM
Wednesday, July 6, 2016 4:47 PM -
SZZhang, actually you should mark the reply from Sam instead of marking your own reply. It will help others find the correct reply directly in the Answers field.Thursday, July 7, 2016 1:55 AM