Post reply

Delimited file import

  • November 30, 2009 at 9:21 am

    #137168

    Hi,

    Is it possible to import a pipe | delimited file int a table. The format of the file is as below:

    Data||||||Data|||||Data

    Data|||Data|||||||||||||||||Data

    Data||Data|||||||Data

    Any help would be appreciated.

    Thanks

    Bob

  • November 30, 2009 at 9:29 am

    #1085494

    try this..

    BULK INSERT testtbl

    FROM 'f:\orders\lineitem.txt'

    WITH

    (

    FIELDTERMINATOR =' |',

    ROWTERMINATOR =' |'

    );

  • November 30, 2009 at 9:31 am

    #1085496

    easily bob;

    if the table exists, or if you create a staging table, there's no porblem; you want to use BULK INSERT command to do it. your example seemed to be a 23 column table with many nulls. which is fine, my example is just a two column table.

    CREATE TABLE BULKSTUFF(COLUMNLIST VARCHAR (10),COL2 INT)

    BULK INSERT BULKSTUFFFROM 'c:\Export_o.txt'

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = '|',

    ROWTERMINATOR = '{slash-n}', --the forum will not display FIRSTROW = 1

    )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 1, 2009 at 12:46 am

    #1085668

    Hi

    Thanks, that works great.

    Bob

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

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