Post reply

Bulk insert text terminator and tab delimiter

  • January 9, 2006 at 2:42 pm

    #167579

    I am just learning SQl Server.

    I am trying to import a text file using Bulk Insert.

    The file has " around each field and \t (tab) delimiter. I can't figure out how to have 2 delimiters or terminators in the bulk insert command.

    I am currently using SQL Server 2000 Query analyzer. I plan to make this a stored procedure when i get it working.

    Any help would be appreciated.

    Russ

  • January 10, 2006 at 6:39 am

    #613781

    >>I can't figure out how to have 2 delimiters or terminators in the bulk insert command.

    Don't bother then, just load up your text in notepad and do a find replace on "

  • January 10, 2006 at 6:40 am

    #613782

    Hi Russ,

    I'm fairly new to bulk inserts myself, but have you tried using a format file? If you use the commandline utility BCP.exe you can create a format file in which you can define how each field is delimited. Perhaps that can help you.

    Yours Sincerely,

    Koen

    The Netherlands

  • January 11, 2006 at 6:04 am

    #614027

    Thank you for the information. I will check out the format file.

    Russ

  • January 11, 2006 at 8:26 am

    #614081

    Hi Russ,

    Format file is defiantely the way to go, I`ve had great success importing all kinds of data files with them.

    This is Not tested but your formatfile would look something like this:

    8.0       

    8       

    1 SQLCHAR 0 0 "'" 0 x Latin1_General_CI_AS

    2 SQLCHAR 0 0 "'\t'" 1 col2 Latin1_General_CI_AS

    3 SQLCHAR 0 0 "'\t'" 2 col3 Latin1_General_CI_AS

    4 SQLCHAR 0 0 "'\t'" 3 col4 Latin1_General_CI_AS

    5 SQLCHAR 0 0 "'\t'" 4 col5 Latin1_General_CI_AS

    6 SQLCHAR 0 0 "'\t'" 5 col6 Latin1_General_CI_AS

    7 SQLCHAR 0 0 "'\t'" 6 col7 Latin1_General_CI_AS

    8 SQLCHAR 0 0 "'\n" 7 col8 Latin1_General_CI_AS

    NOTES:
    The first column is not imported (hence the column no. of 0) this is because it is using the ' as a delimiter, which will be the first char on a row and therefore will give an empty string (IE everything before the ').
     
    The last column ends with '\n (\n = new line), this will give the data before the last '. You might need to use \r\n (erm or is it \n\r) instead.
     
    The other columns use '\t' as the delimiter so that you get eveything from after the previous ' to before the next '.
     
    Hope this helps.
    Giles
     
    "Don`t try to engage my enthusiasm, I don`t have one."

    (Marvin)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply