none
SQLServerBulkCopy not working for "|" delimiter RRS feed

  • 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 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

    • Proposed as answer by Riaon Monday, July 4, 2016 9:57 AM
    Monday, July 4, 2016 9:37 AM
    Moderator
  • 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