Parse txt file

  • Well it sounds like ultimately you are loading it into a SQL table.  So you can either filter out the fields you don't care about when reading the file or just not load those fields to SQL.

  • How could I put the ones I want in an array then only select them?

  • that's what I stumped with how to introduce that in script. I found that 2 tags Command and Description only on some records

    are causing the issue with full file.

  • So I was thinking of doing something like:

    $qyarray1 = @("insert_job")

    Putting all of the tags I want to capture from the file extraction then inserting a condition based lookup and load to SQL.

    I need help where to put this logic, abd the SQL Insert piece.

    Many Thanks.

  • Any help much appriacted trying to just filter on Tags needed.

    Thanks.

  • The code provided is creating an array with all the key/value pairs in the file in it.  You can either filter at the point of the array creation or only pull out the ones you care about when loading to SQL.

  • I tried using one example, but it isn't doing the filtering against array from your script.

    $qyarray1 = @("insert_job")

  • Would you be able to show me in script where to put this logic in to filter by tags and the SQL Insert?

     

    Many Thanks..

  • Bruin wrote:

    How could original script posted by zzartin, be modified to just pickup these tags during extraction? Currently it's finding ALL which do some testing with the file might be the issue.

    Thanks.

    Insert_Job

    job_type

    days_of_week

    start_times

    run_calendar

    start_mins

    machine

    watch_file

    box_name

    Just an observation...

    The file you previously attached had one "data record" (for lack of a better term) that contained all but the last two tags.  The second data record also contained all of those plus the "watch_file" flag.  Now you're saying that it's also possible to contain a "box_name" flag.

    You also appear to be having some issues with what each line of the file may end with.

    Is it possible for you to attach a REAL, ORIGINAL, UNTOUCHED, WHOLE FILE that you haven NOT modified or resaved in any way, shape, or form that I could import?  I'm thinking that there's actually a fairly easy way to do this all without PoSh, Python, or anything else other than good ol' T-SQL but I need the REAL raw material to work with.

    --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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

  • Jeff,

    Thanks for update, and yes each data record could contain ALL of the tags I'm looking for, or maybe just 3-4 of them there is no set

    data record that ends a grouping.

    The data record zzartin was keying on always start a new record...do to some restrictions I apologize I can't send a true data file. I know my example data records aren't many, but it's a true representation of what the data looks like..

    Can it be handled straight from SQL?

    many thanks for replies to ALL

     

  • Ok.  Totally understood on not being about to provide the full monty in an original file.  Just to make really sure, the file located at...

    https://www.sqlservercentral.com/wp-content/uploads/2022/04/sample.txt

    ... is a correct representation of the data you actually have to deal with, for sure, correct? 

    And understood on the no "end of group record"... we don't actually need one.  It's always the start of the next group or the end of the file.

    Bruin wrote:

    Can it be handled straight from SQL?

    I currently don't see why not but I'm willing to give it the good ol' "college try".  What version of SQL Server are you using?

     

    --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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

  • Never mind... I tried to write it for 2012 and above.  If you have 2017 or above, we can do a little optimization.  If you have less than 2012, then I urge you to upgrade sooner than later.

    I simulated importing the Sample.txt file you provided.  If the output from the following code is what you want, the we'll work on importing the file, which is also pretty easy.

    Here's the code.  It'll be apparent where I simulated importing the file into a table.

       WITH 
    cteGroup AS
    (--==== First, we have to group the lines from the file. (THIS IS THE KEY TO EVERYTHING!)
    SELECT df.RowNum
    ,DataGroup = COUNT(CASE
    WHEN LEFT(df.DataFromFile,2) = '/*'
    THEN 1
    END) OVER (ORDER BY df.RowNum ROWS UNBOUNDED PRECEDING)
    ,DataFromFile = CONVERT(VARCHAR(500),df.DataFromFile)
    FROM (VALUES --This simulates the file import of the Sample.txt file previously provided.
    ( 1,'/* ----------------- ACCTS_UPDATES ----------------- */')
    ,( 2,' ')
    ,( 3,'insert_job: ACCTS_UPDATES job_type: CMD ')
    ,( 4,'machine: svrprod1 ')
    ,( 5,'days_of_week: mo,tu,we,th,fr ')
    ,( 6,'start_mins: 0,30 ')
    ,( 7,'run_window: "05:58 - 20:09" ')
    ,( 8,' ')
    ,( 9,' ')
    ,(10,'/* ----------------- BOL_FW ----------------- */ ')
    ,(11,' ')
    ,(12,'insert_job: BOL_FW job_type: FW ')
    ,(13,'machine: svrprod2 ')
    ,(14,'days_of_week: mo,tu,we,th,fr,sa ')
    ,(15,'start_mins: 0,5,10,15,20,25,30,35,40,45,50,55 ')
    ,(16,'run_window: "04:30 - 07:30" ')
    ,(17,'watch_file: "\\svrprod2\updfiles\TRDET.TXT" ')
    ,(18,' ')
    )df(RowNum,DataFromFile)
    )
    ,cteLeftTag AS
    (
    SELECT DataGroup
    ,Tag1 = SUBSTRING(DataFromFile,1,CHARINDEX(':',DataFromFile)-1)
    ,Data1 = SUBSTRING(DataFromFile,CHARINDEX(':',DataFromFile)+2,500)
    FROM cteGroup
    WHERE DataFromFile > ''
    AND LEFT(DataFromFile,2)<>'/*'
    )
    SELECT DataGroup
    ,insert_job = RTRIM(MAX(CASE WHEN Tag1 = 'insert_job'
    THEN SUBSTRING(Data1,1,ISNULL(ca.JtPos-1,500))
    ELSE '' END))
    ,job_type = RTRIM(MAX(CASE WHEN Tag1 = 'insert_job' AND ca.JtPos > 0
    THEN SUBSTRING(Data1,ca.JtPos+10,500)
    ELSE '' END))
    ,job_type = RTRIM(MAX(CASE WHEN Tag1 = 'job_type' THEN DATA1 ELSE '' END))
    ,days_of_week = RTRIM(MAX(CASE WHEN Tag1 = 'days_of_week' THEN DATA1 ELSE '' END))
    ,start_times = RTRIM(MAX(CASE WHEN Tag1 = 'start_times' THEN DATA1 ELSE '' END))
    ,run_calendar = RTRIM(MAX(CASE WHEN Tag1 = 'run_calendar' THEN DATA1 ELSE '' END))
    ,start_mins = RTRIM(MAX(CASE WHEN Tag1 = 'start_mins' THEN DATA1 ELSE '' END))
    ,machine = RTRIM(MAX(CASE WHEN Tag1 = 'machine' THEN DATA1 ELSE '' END))
    ,watch_file = RTRIM(MAX(CASE WHEN Tag1 = 'watch_file' THEN DATA1 ELSE '' END))
    ,box_name = RTRIM(MAX(CASE WHEN Tag1 = 'box_name' THEN DATA1 ELSE '' END))
    FROM cteLeftTag
    CROSS APPLY (VALUES(NULLIF(CHARINDEX('job_type',Data1),0)))ca(JtPos)
    GROUP BY DataGroup
    ;

    Here's the result.  Yeah, we can dynamically exclude columns that have no entries but, because I'm getting close to retiring, I may have to ask you for a donation to my retirement fund for that.  But, first, is this ok?

    --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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

  • Thats Cool.. just like I was thinking output should look.

    Yeah the dynamic exclude is big piece..

     

    Your retiring no way man!!!

    Many thanks..

  • So, the next step is to include only those tags that you want and you listed those at the following post...

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1

    We don't actually need to do that step... The original code I provided above actually does that filtering.  That's part of the "magic" in that code.

    --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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

  • Is there some magic that pulls in the file?

    Thx.

    • This reply was modified 8 months, 3 weeks ago by  Bruin.

Viewing 15 posts - 16 through 30 (of 55 total)

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