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.

Answer 1

Is it possible to provide the two original files?

Answer 2

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

Answer 3

I tested with other delimitors like @ or &. They both working fine. Why the | not working ?!

Answer 4

Hi SZZhang,

Based on my research, parameter delimiter represents a regular expression. You could use pipe (|) as below.

delimiter = "\\|";

// OR
import java.util.regex.Pattern;

delimiter = Pattern.quote("|");

---

Sam Zha
TechNet Community Support

Answer 5

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!

Answer 6

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.