extracting values from a text file

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Tourless
Pro Scripter
Posts: 69
Joined: Wed Jun 14, 2017 1:53 am
Location: NY

extracting values from a text file

Post by Tourless » Wed Oct 02, 2019 6:35 pm

Hi Folks,

Having a hard time getting my head around what is needed to accomplish extracting values from a txt file. The file in question is a result of an API GET call that dumps every field for every entry where I only need one. There is a field called idLandmark which shows up repeatedly in my txt file as

Code: Select all

"idLandmark":12345,
. Now the txt file is not formated as a single line per field, per entry. Rather it is one long continuous line of data regurgitation (lovely).

I've tried using the ReadFile command and hunk of code I found in another post regarding parsing values...

Code: Select all

HTTPRequest>c:\py\myfile.txt,,GET,,sVals
Separate>sVals,LF,valList
If>valList_count>0
  Let>k=0
  Repeat>k
    Let>k=k+1
    Let>this_pair=valList_%k%
    Separate>this_pair,=,parts
    Let>%parts_1%=%parts_2%
  Until>k=valList_count
Endif
And changed

Code: Select all

Separate>sVals,LF,valList
to

Code: Select all

Separate>sVals,,,valList
since my fields/values are separated by a comma, but no luck.

Here's a sample of what the raw data looks like...

Code: Select all

[{"Landmark_Style":"Customer Site","idLandmark":75705,"idCustomer":153,"idLandmark_Style":8,"Landmark_Category":1,"Created_On":"2019-10-01T20:38:29.357","Created_By":2772,
etc...

The values I'm looking to capture are the idLandmark fields, such as 75705. In my perfect world all the values would be extracted from my txt file and entered into a new excel sheet, or a txt file with values separated by line (CRLF).

I think the bigger peoblem is I don't have enough knowledge to GET only the field I want from my API call in the first place, but that's a post for another forum.

TIA!

User avatar
JRL
Automation Wizard
Posts: 3497
Joined: Mon Jan 10, 2005 6:22 pm
Location: Iowa

Re: extracting values from a text file

Post by JRL » Wed Oct 02, 2019 8:14 pm

I'd try Marcus' Most Used Regex. To parse. Then you use the produced array of data any way you'd like.

Code: Select all

Let>vData=[{"Landmark_Style":"Customer Site","idLandmark":75705,"idCustomer":153,"idLandmark_Style":8,"Landmark_Category":1,"Created_On":"2019-10-01T20:38:29.357","Created_By":2772,


//Use Marcus' "Most Used Regex"
//Let>pattern=(?<=TOKEN1).*?(?=TOKEN2)

//
Let>pattern=(?<="idLandmark":).*?(?=,)
Regex>pattern,vData,0,MatchArr,MatchNum,0

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts