Post reply

Parse txt file

  • May 3, 2022 at 1:59 pm

    #4025997

    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.

  • May 3, 2022 at 2:08 pm

    #4026008

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

  • May 3, 2022 at 2:10 pm

    #4026009

    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.

  • May 3, 2022 at 5:11 pm

    #4026111

    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.

  • May 5, 2022 at 4:27 pm

    #4027487

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

    Thanks.

  • May 5, 2022 at 5:07 pm

    #4027503

    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.

  • May 5, 2022 at 6:30 pm

    #4027550

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

    $qyarray1 = @("insert_job")

  • May 7, 2022 at 1:03 pm

    #4028643

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

  • May 8, 2022 at 11:20 pm

    #4029175

    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.

    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)

  • May 8, 2022 at 11:30 pm

    #4029178

    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

     

  • May 9, 2022 at 1:42 am

    #4029218

    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.

    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)

  • May 9, 2022 at 3:46 am

    Answer

    #4029248

    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.

    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)

  • May 9, 2022 at 12:35 pm

    #4029429

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

    Yeah the dynamic exclude is big piece..

     

    Your retiring no way man!!!

    Many thanks..

  • May 9, 2022 at 5:45 pm

    #4029616

    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.

    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)

  • May 10, 2022 at 1:30 am

    #4029771

    Is there some magic that pulls in the file?

    Thx.

    • This reply was modified 1 year, 11 months 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