none
Need help on importing the files with "Text"|"Text" format RRS feed

  • Question

  • Hi,

    Can you please help me how i can create the format files to import the data for below format?

    "ABC"|"CDF"

    Bit wondering how to load the files that are having double cotes and Pipe.

    Thanks in advance.


    • Edited by sat123456 Tuesday, June 20, 2017 2:57 AM
    Tuesday, June 20, 2017 2:56 AM

All replies

  • Pipe is your Delimiter

    Double quote is your Text Qualifier

    You should see those as options when you do an import

    Tuesday, June 20, 2017 4:00 AM
  • Please check whether the following satisfies your requirement:

    create table test(a varchar(10), b varchar(10))
    
    BULK INSERT test
    FROM 'C:\\Temp\\Sample.txt' -- Contains "ABC"|"CDF"
    WITH
    (
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n'
    )
    
    select * from employee
    Tuesday, June 20, 2017 5:05 AM
  • Hi,

    Make a complement about SQLNeophyte's reply. If you don't want to keep the double quotes after doing the bulk insert. You can use Replace() function to remove them. Like:

    Update test
    Set a= Replace(a,CHAR(34),'')

    Or you can use the Export/Import Wizard to load the file. For more details, please refer:

    How to remove unwanted Quotation Marks while importing a data file 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 20, 2017 7:23 AM
  • What do you want to use for the import? BCP/BULK INSERT? Or do you want help with the Export/Import wizard or SSIS? I can help you if you want help with BCP or BULK INSERT, but if that is not what you want, what I will say now will be of no use for you and you should stop reading.

    You will need to use a format file which specifies three fields like this:

    9.0
    3
    1 SQLCHAR 0 0 "\""     0 "" ""
    2 SQLCHAR 0 0 "\"|\""  1 col1 Latin1_General_CI_AS
    3 SQLCHAR 0 0 "\"\r\n" 2 col1 Latin1_General_CI_AS

    The idea is that the first field is a empty field before the first delimiter which is a single ".

    To learn more about the bewildering world of bulk load, I have an article on my web site:
    http://www.sommarskog.se/bulkload.html

    Tuesday, June 20, 2017 7:32 AM
  • What do you want to use for the import? BCP/BULK INSERT? Or do you want help with the Export/Import wizard or SSIS? I can help you if you want help with BCP or BULK INSERT, but if that is not what you want, what I will say now will be of no use for you and you should stop reading.

    You will need to use a format file which specifies three fields like this:

    9.0
    3
    1 SQLCHAR 0 0 "\""     0 "" ""
    2 SQLCHAR 0 0 "\"|\""  1 col1 Latin1_General_CI_AS
    3 SQLCHAR 0 0 "\"\r\n" 2 col1 Latin1_General_CI_AS

    The idea is that the first field is a empty field before the first delimiter which is a single ".

    To learn more about the bewildering world of bulk load, I have an article on my web site:
    http://www.sommarskog.se/bulkload.html

    That's an awesome resource page you have there!  Bookmarked for future reference.

    Tuesday, June 20, 2017 6:49 PM