Post reply

select from flat file

  • October 1, 2017 at 2:45 pm

    #340575

    How do select from a flat file?

    SELECT Field1
    ,Field2
    ,Field3
      FROM OPENROWSET(BULK 'C:\Test.txt' ,
    FORMATFILE='C:\Test.txt') AS Test
    WHERE Field1 <> 'N/A'

    This is the error.
    Msg 9422, Level 16, State 48, Line 33
    XML parsing: line 2, character 0, incorrect document syntax

    Or can this be refined to a select statement with a where clause

    BULK INSERT [dbo].[Format]
     FROM 'C:\Test.txt'
     WITH
      (
       ROWTERMINATOR ='\n'
      )

    The flat file looks like this.

    Field1Field2Field3
    dog2NY
    cat3CT
    fish7MA
    N/A0FL

  • October 1, 2017 at 3:34 pm

    #1961795

    MinhL7 - Sunday, October 1, 2017 2:45 PM

    How do select from a flat file?

    SELECT Field1
    ,Field2
    ,Field3
      FROM OPENROWSET(BULK 'C:\Test.txt' ,
    FORMATFILE='C:\Test.txt') AS Test
    WHERE Field1 <> 'N/A'

    This is the error.
    Msg 9422, Level 16, State 48, Line 33
    XML parsing: line 2, character 0, incorrect document syntax

    Or can this be refined to a select statement with a where clause

    BULK INSERT [dbo].[Format]
     FROM 'C:\Test.txt'
     WITH
      (
       ROWTERMINATOR ='\n'
      )

    Without knowing what the file layout spec is, you won't get much help here.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 1, 2017 at 4:06 pm

    #1961796

    The flat file looks like this.

    Field1Field2Field3
    dog2NY
    cat3CT
    fish7MA
    N/A0FL

  • October 1, 2017 at 5:02 pm

    #1961802

    I need a bit more info to help.  Are the columns separated by TABs or are they positional in that each field starts at the same character position for every row?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 1, 2017 at 5:41 pm

    #1961805

    It's separated by tab, thank you.

  • October 1, 2017 at 7:43 pm

    #1961815

    If your file is in good shape, then the following will work...

    Create a staging table like this...


     CREATE TABLE dbo.Test
            (
             Field1 VARCHAR(10)
            ,Field2 INTEGER
            ,Field3 CHAR(2)
            )
    ;

    Run the following code to import the file into the staging table.  Like this...


       BULK INSERT dbo.Test
       FROM 'C:\Test.txt'
       WITH
            (
             FIRSTROW           = 2
            ,MAXERRORS          = 0
            ,FIELDTERMINATOR    = '\t'
            ,TABLOCK
            )
    ;

    Then validate or do whatever you were going to do with the data in the staging table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 1, 2017 at 8:32 pm

    #1961817

    It's not a good file and I need to filter the insert. Can you select certain columns and add a where clause?

  • October 2, 2017 at 5:53 am

    #1961848

    MinhL7 - Sunday, October 1, 2017 8:32 PM

    It's not a good file and I need to filter the insert. Can you select certain columns and add a where clause?

    Perhaps you could upload a sample copy of the file? This would make things (a lot) easier for everyone else.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • October 2, 2017 at 7:56 am

    #1961866

    MinhL7 - Sunday, October 1, 2017 8:32 PM

    It's not a good file and I need to filter the insert. Can you select certain columns and add a where clause?

    Do that in the staging table.  It'll be much faster there.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

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