Text file import

  • Wecks

    Say Hey Kid

    Points: 689

    Is there a script that scan take text that is not column delimited and transfer each line into a separate record and each line where it is separated by a number of spaces inserts into a separate column.

    For example the format of the txt file is as below but there are hundreds of lines.

    TS123    TS123456ght            TB12   TBH        0000

    AS234    AF23424322               RI      SSDFF    EDE           7898979879

    Each row maybe slightly different in the amount of data in it but i just want a way to import it.

    It maybe that i need to state the first lot of data on a line goes into the first column, the second into a second column etc.

     

     

     

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    I'd be tempted to import it into a single-column staging table and do the transformations from there.

    John

  • Wecks

    Say Hey Kid

    Points: 689

    Thanks for your reply, TBH I am no SQL developer and the issue is pretty much how i would extract the data either from a Text file or after it has been imported into the SQL and give some structure.

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    I'm not sure that a relational database is the place to store unstructured data like this.  The reason I say that is that you don't know what data is going to be in what column, and so you can't create your table with anything but the loosest of data types (varchar(1000) for everything, maybe).

    If you absolutely have to do it like that, and you're not experienced in T-SQL, then it's going to be a steep learning curve for you.  It's too much to cover in a forum topic, really.  You can use this technique to reduce the gap between each pair of elements to a single space.  After that, you'd probably need dynamic SQL to do the actual inserts into your destination table.

    John

  • Dinosaur

    SSC Enthusiast

    Points: 122

    Unless the data is delimited with the same number of spaces with the same type of data in the same columns, its not the simplest of tasks.  I agree with John Mitchell-245523, it’s probably too much to cover in a forum topic.

  • Jeff Moden

    SSC Guru

    Points: 996661

    Wecks wrote:

    Is there a script that scan take text that is not column delimited and transfer each line into a separate record and each line where it is separated by a number of spaces inserts into a separate column. For example the format of the txt file is as below but there are hundreds of lines. TS123    TS123456ght            TB12   TBH        0000 AS234    AF23424322               RI      SSDFF    EDE           7898979879 Each row maybe slightly different in the amount of data in it but i just want a way to import it. It maybe that i need to state the first lot of data on a line goes into the first column, the second into a second column etc.      

    This may be a whole lot easier than most would think.  Judging from the 2 examples that you have, this might be what is known as a "fixed field" file where each column (data missing or not) starts at a given character position on each and every line in the file and always has the same number of characters whether those characters be spaces or something else.

     

    Is that what you have?

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • pietlinden

    SSC Guru

    Points: 62811

    If these are EDI files, then you can't just import them. You have to parse those, because the different lines have different meanings/information. Do they have a consistent structure?

Viewing 7 posts - 1 through 7 (of 7 total)

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