none
Bulk Insert with Quotes On RRS feed

  • Question

  • Hello everyone i am having Bulk insert issue when migrating from Sybase SQL Anywhere 11 to SQL server 2008 r2 express. The problem is with single quotes around data column values(Not All but for VarChar data types).

    Here is Sybase Load Table command which inserts data with no singles quotes.

    LOAD TABLE "DBA"."GCK_KioskSetupOptions" ("SMTPIPAddress","SMTPPort","POP3IPAddress","POP3Port","AutoLogOffDelay","AllowUserToPrint","AllowAttachment","MaxMessageSize","BillingCodeOptionID","MaxMessage","AllowUserToImport","ProtectUserWithPassword","DefaultEncoding","EnableMessageLimit","GlobalMessageLimit","AllowSetMessagePriority")
     FROM 'c:/temp/709.dat'
     FORMAT 'TEXT' QUOTES ON
     ORDER OFF ESCAPES ON
     CHECK CONSTRAINTS OFF COMPUTES OFF
     STRIP OFF DELIMITED BY ','
     ENCODING 'windows-1252'
    go

    Quotes ON command above does the job of eliminating singles Quotes around column values appear as follows:

    127.0.0.1  25  127.0.0.1  110.........

    With Quotes OFF Column Values appear as follows:

    '127.0.0.1 '25' '127.0.0.1' '110' .........

    Now in the process of migration to SQL server 2008 r express iam using BULK insert to load the data

    BULK
    INSERT dbo.GCK_KioskSetupOptions FROM 'c:/temp/709.dat' 
    WITH
    ( FIELDTERMINATOR = ',', 
     ROWTERMINATOR = '\n' 
      
    )
    GO
    

    When i do that the Column Values appear as follows:

    '127.0.0.1'  '25'  '127.0.0.1'   '110' .........

    Expected Column values:

    127.0.1  25  127.0.0.1  110 ................

    THIS Issue seems to be happening for VarChar Datatypes only and i don't have issues with integer and other.

    Can any one help me out in resolving single quote issue?.What is quivalent Sybase Load Table Quotes ON command in SQL Server 2008 r2 express.

     

     

     


    Ram
    Tuesday, July 26, 2011 1:36 PM

Answers

  • Ram,  the format file is used by BCP (command line interface) and by BULK INSERT.  The format is the same, although there is an XML version and a flat file version.  I have been using the flat file version for years and never bothered to learn the XML version. I linked you to the definitions earlier. 

    Also a helpful link is "Understanding Non-XML Format Files": http://msdn.microsoft.com/en-us/library/ms191479.aspx 

    In terms of your particular issue, look at the sample here from Nigel Rivett: http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

    You will particularly find the "Quote delimitted fields in data file" topic interesting, I will paste part of the sample here and try to change it for you.

    -- create the format file
    10.0
    9
    1 SQLCHAR 0 0 "'" 0 xxx  Latin1_General_CI_AS
    2 SQLCHAR 0 0 "','" 1 SMTPIPAddress Latin1_General_CI_AS
    3 SQLCHAR 0 0 "','" 2 SMTPPort  Latin1_General_CI_AS
    4 SQLCHAR 0 0 "','" 3 POP3IPAddress Latin1_General_CI_AS
    5 SQLCHAR 0 0 "'," 4 POP3Port  Latin1_General_CI_AS
    6 SQLCHAR 0 0 "," 5 AutoLogOffDelay ""
    7 SQLCHAR 0 0 "," 6 AllowUserToPrint ""
    8 SQLCHAR 0 0 "," 7 AllowAttachment ""
    9 SQLCHAR 0 0 "," 8 MaxMessageSize ""
    ... --and so forth--
    
    


    Notice that this is 10.0 (SQL Server 2008 version number), there is a column 0 to give the beginning ' a place to be defined so that it is stripped from the first column, then you also see strings of ','  or ',  or , depending on how the break between columns are defined.

    RLF 



    • Edited by SQLWork Friday, July 29, 2011 9:44 PM code fix
    • Marked as answer by Stephanie Lv Wednesday, August 3, 2011 9:29 AM
    Friday, July 29, 2011 3:56 PM

All replies

  • You show the following string in your text above: 127.0.0.1  25  127.0.0.1  110

    I do not see any commas in this string.  What is the character in the blank space?  Is it a space?  A tab?  Something else?

    I suggest that you delimit columns by tabs instead of commas. Then you could (probably) totally drop the quotes and everything would 'just work', since the quotes are there to protect the string contents from getting parsed.  (That is, so that you can have 'Jones, Roy','Prop Forward' appear as only 2 columns instead of 3.)   See example 2 in the following link, which points out how to set the tab as the default delimiter:

    http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1270/html/iqref/X315750.htm

    If that works, you get rid of the single quotes and the commas all at the same time.  As long as your character strings do not contain tabs, then it should be just fine.

    RLF



    Tuesday, July 26, 2011 5:17 PM
  • Rusell Thanks for responding. Coming string 127.0.0.1 25... actually it is not a string the but output of a select query from sybase db table(GCK_KioskSetupOptions). When i unload the data from the sybase database using dbunload utility  it generates .dat files (each for one table ) and a schema file .  DAT file  has the table data with comma as delimiter(dbunload chooses comma as delimiter) and this is how it looks in the .dat file referring to GCK_KioskSetupOptions.

    '127.0.0.1','25','127.0.0.1','110',1,1,1,75000,1,250,1,'','',1,25,0

    Now when  i try to bulk insert the data from the .dat file into the SQL Server table (GCK_KioskSetupOptions) the column(VarChar Type) values are having single quotes around this is how the column values look like:

    '127.0.0.1'   '25'  '127.0.0.1' '110'   1 1 1 75000  1 250 1 ''  ''  1 25  0

    Basically what i want is stripping of single quotes of varchar type columns.

     

     


    Ram
    Tuesday, July 26, 2011 5:46 PM
  • FYI -

    "With the  Sybase SQL Anywhere 11 Unload utility, you can unload a database and put a set of data files in a named directory. The Unload utility creates an Interactive SQL command file to rebuild your database. It also unloads all of the data in each of your tables into files in the specified directory, in comma-delimited format. Binary data is properly represented with escape sequences. "

    http://dcx.sybase.com/1100/en/dbadmin_en11/dbunload.html


    Ram
    Tuesday, July 26, 2011 5:55 PM
  • OK, since Sybase will not give you a cleaner load tool, you can manage this by creating and using a format file.  (Sorry that I missed the significance of you showing the LOAD FILE command.)  The format file can explicitly define the delimiters for each column of data, giving you full control over the parsing of the data.  This requires every row to be of the same format, but this is the same rule for rows in a table.

    Starting with the overview of using format files: http://msdn.microsoft.com/en-us/library/ms190393.aspx

    Here is a description of the format file: http://msdn.microsoft.com/en-us/library/ms178129.aspx

    Once you have the proper format file defined, then you would:

    BULK
    INSERT dbo.GCK_KioskSetupOptions FROM 'c:/temp/709.dat' 
    WITH
    ( FORMATFILE = 'c:/temp/Kiosk.fmt' 
    )
    GO
    
    

    Of course, each table will need its own format file to match its definition. 

    RLF

    Tuesday, July 26, 2011 6:31 PM
  • Hey Russel if unloaded data file  from sybase has single quotes around column values which are of type VarChar like

    '127.0.0.1' ,'25', '127.0.0.1', '110',1,1,1,75000,1,250,1,'','',1,25,0

     then how would i remove these single quotes by using format file and Bulk Insert during SQL Server Migration?.

    Could you provide an example for format file for how to parse single quotes?.

    Thank you


    Ram
    Friday, July 29, 2011 2:36 PM
  • Ram,  the format file is used by BCP (command line interface) and by BULK INSERT.  The format is the same, although there is an XML version and a flat file version.  I have been using the flat file version for years and never bothered to learn the XML version. I linked you to the definitions earlier. 

    Also a helpful link is "Understanding Non-XML Format Files": http://msdn.microsoft.com/en-us/library/ms191479.aspx 

    In terms of your particular issue, look at the sample here from Nigel Rivett: http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

    You will particularly find the "Quote delimitted fields in data file" topic interesting, I will paste part of the sample here and try to change it for you.

    -- create the format file
    10.0
    9
    1 SQLCHAR 0 0 "'" 0 xxx  Latin1_General_CI_AS
    2 SQLCHAR 0 0 "','" 1 SMTPIPAddress Latin1_General_CI_AS
    3 SQLCHAR 0 0 "','" 2 SMTPPort  Latin1_General_CI_AS
    4 SQLCHAR 0 0 "','" 3 POP3IPAddress Latin1_General_CI_AS
    5 SQLCHAR 0 0 "'," 4 POP3Port  Latin1_General_CI_AS
    6 SQLCHAR 0 0 "," 5 AutoLogOffDelay ""
    7 SQLCHAR 0 0 "," 6 AllowUserToPrint ""
    8 SQLCHAR 0 0 "," 7 AllowAttachment ""
    9 SQLCHAR 0 0 "," 8 MaxMessageSize ""
    ... --and so forth--
    
    


    Notice that this is 10.0 (SQL Server 2008 version number), there is a column 0 to give the beginning ' a place to be defined so that it is stripped from the first column, then you also see strings of ','  or ',  or , depending on how the break between columns are defined.

    RLF 



    • Edited by SQLWork Friday, July 29, 2011 9:44 PM code fix
    • Marked as answer by Stephanie Lv Wednesday, August 3, 2011 9:29 AM
    Friday, July 29, 2011 3:56 PM