Here's my version of "Dr. Strangepipe, or, How I'm Learning To Stop Worrying and Love TPIPE."
I've used JPSoft software since the 4DOS days. But my last employer's policy towards non-corporate software was strictly "George Orwell": Anything not specifically permitted was forbidden. So I was stuck with using CMD BAT files at my last job, and I've been away from Take Command for about 20 years. Now I'm back. I'm gradually remembering things. And then I encountered... TPIPE.
I've written a .BTM that processes two big .CSV files, eliminates unnecessary fields and arranges them so the resulting files' formats are consistent, then concatenates them, sorts them and eliminates duplicates.
I started out using FOR /f loops on the files, which goes line by line, writing only the needed fields, in the correct order, to another file. It worked, but it was very slow. At which point, I bit the bullet and decided to learn TPIPE.
After scratching my head about the TPIPE syntax for a couple of days, I printed out the help file. I numbered with red pencil the parameters for actions I needed, and things started to make sense. What I've come up with for my BTM works, but I think I might be able to do things better/more efficiently.
Here's a snip of the code in question:
The rest of the BTM concatenates the 2 files, sorts, eliminates dupes and adds header.
Here's a sample of the file I'm processing above (the elipsis just shows where I skipped (many) lines to show the progression
When all is done, the result looks like this.
Here are several things I don't yet understand:
1. For some reason, I have not been able to combine multiple TPIPE actions in one statement, or use TPIPE in a pipe. Each time I've tried, I get nothing in the output. So I've written one "filter" or action on each line, writing each result to a temporary file. So I end up with a lot of temp files (which of course, I delete when finished). So, my question: Are there rules about what you can combine in a single TPIPE command, and how, that I've missed? Maybe show me an example or two?
2. I don't quite get how subfilters work and how to specify them. Again, an example or two would help.
3. TPIPE doesn't seem to handle empty fields in a comma or semicolon delimited file properly. When I attempt to delete a field, if a preceding field is empty, TPIPE appears to get confused and deletes nothing. See the first file snippet above--if the name field is blank, I end up with the number field as the third field rather than a blank field. So, for example:
65837;KG7DSB;3116414;;192
...would end up, after deleting fields 1 and 5, as this:
KG7DSB;3116414;192
...instead of this:
KG7DSB;3116414;;
I've gotten around this by first doing a /replace and adding a couple of underscores to the empty fields, replacing ";;" with ";__;". Is there a better way to deal with this?
Further Background, if you care: The input files are lists of amateur radio callsigns, their ID numbers for the DMR (Digital Mobile Radio) system, names, locations, and in one case, how long since the station was last heard on a particular network. We need such a list in our radios, with only IDs callsigns and first names. The problem is that there are about 215,000 users worldwide, and no radio can fit the entire global database anymore. My radio can only handle about 48000 records. So what I'm doing is pulling all the records for my state and its neighbors from the complete list, then adding as many records as will fit into the radio from the other file, which lists stations in order of "days since last heard." I end up with a smaller database that has a reasonable chance of containing most people I contact on the radio.
Thanks for any insights!
--Peter
I've used JPSoft software since the 4DOS days. But my last employer's policy towards non-corporate software was strictly "George Orwell": Anything not specifically permitted was forbidden. So I was stuck with using CMD BAT files at my last job, and I've been away from Take Command for about 20 years. Now I'm back. I'm gradually remembering things. And then I encountered... TPIPE.
I've written a .BTM that processes two big .CSV files, eliminates unnecessary fields and arranges them so the resulting files' formats are consistent, then concatenates them, sorts them and eliminates duplicates.
I started out using FOR /f loops on the files, which goes line by line, writing only the needed fields, in the correct order, to another file. It worked, but it was very slow. At which point, I bit the bullet and decided to learn TPIPE.
After scratching my head about the TPIPE syntax for a couple of days, I printed out the help file. I numbered with red pencil the parameters for actions I needed, and things started to make sense. What I've come up with for my BTM works, but I think I might be able to do things better/more efficiently.
Here's a snip of the code in question:
Echo * Processing %MaxLines Global "Last Heard" records...
:: Add underscores to blank fields
tpipe /input=user_by_lh.csv /replace=0,0,0,0,0,0,0,0,0,";;",";__;" >temp$11.txt
:: remove field 5
tpipe /input=temp$11.txt /selection=7,0,5,5,0,2,"",1 >temp$12.txt
:: remove field 1
tpipe /input=temp$12.txt /selection=7,0,1,1,0,2,"",1 >temp$13.txt
:: Move Field 1 to position 2, exchanging ID with callsign to match user.csv format
tpipe /input=temp$13.txt /selection=10,0,1,1,2,2,"",1 >temp$14.txt
:: Change semicolons to commas to match user.csv format
tpipe /input=temp$14.txt /replace=0,0,0,0,0,0,0,0,0,";","," >temp$15.txt
:: Remove header line, include only %MaxLines lines
:: We didn't remove header earlier because the /selection statements use it!
tpipe /input=temp$15.txt /head=1,0,1 >temp$16.txt
tpipe /input=temp$16.txt /head=0,0,%MaxLines >temp$17.txt
The rest of the BTM concatenates the 2 files, sorts, eliminates dupes and adds header.
Here's a sample of the file I'm processing above (the elipsis just shows where I skipped (many) lines to show the progression
num;callsign;dmrid;name;age
1;DG5NEK;2628601;Heribert;0
2;F6ASS;2080924;Pascal;0
3;T77CD;2920016;Giovanni;0
...
16481;W6JNG;3167861;Jing;6
16482;KC5FM;3140620;Lloyd A;6
...
81999;ZS1TR;6551006;Trevor;365
82000;IU5JJV;2225251;Cafarro70;365
82001;BG7FCK;4607087;Jianyu;365
When all is done, the result looks like this.
RADIO_ID,CALLSIGN,FIRST_NAME
1023001,VE3THW,Wayne
1023003,VE3QC,Guy
1023008,VE3JMR,Mark
Here are several things I don't yet understand:
1. For some reason, I have not been able to combine multiple TPIPE actions in one statement, or use TPIPE in a pipe. Each time I've tried, I get nothing in the output. So I've written one "filter" or action on each line, writing each result to a temporary file. So I end up with a lot of temp files (which of course, I delete when finished). So, my question: Are there rules about what you can combine in a single TPIPE command, and how, that I've missed? Maybe show me an example or two?
2. I don't quite get how subfilters work and how to specify them. Again, an example or two would help.
3. TPIPE doesn't seem to handle empty fields in a comma or semicolon delimited file properly. When I attempt to delete a field, if a preceding field is empty, TPIPE appears to get confused and deletes nothing. See the first file snippet above--if the name field is blank, I end up with the number field as the third field rather than a blank field. So, for example:
65837;KG7DSB;3116414;;192
...would end up, after deleting fields 1 and 5, as this:
KG7DSB;3116414;192
...instead of this:
KG7DSB;3116414;;
I've gotten around this by first doing a /replace and adding a couple of underscores to the empty fields, replacing ";;" with ";__;". Is there a better way to deal with this?
Further Background, if you care: The input files are lists of amateur radio callsigns, their ID numbers for the DMR (Digital Mobile Radio) system, names, locations, and in one case, how long since the station was last heard on a particular network. We need such a list in our radios, with only IDs callsigns and first names. The problem is that there are about 215,000 users worldwide, and no radio can fit the entire global database anymore. My radio can only handle about 48000 records. So what I'm doing is pulling all the records for my state and its neighbors from the complete list, then adding as many records as will fit into the radio from the other file, which lists stations in order of "days since last heard." I end up with a smaller database that has a reasonable chance of containing most people I contact on the radio.
Thanks for any insights!
--Peter