0

I have a text file that has data written to it in the format below, its thousands of rows.

  1. The new record always begins with WHATEVER.RDNDISPLAY or WHATEVER.DSPLY_NAME.
  2. The name of the record is WHATEVER
  3. The row ends with " followed by blank, means no data or by some text, date or string or number +100 or -100
  4. ignore the spaces below cause there is not, this is just saw that I can display it properly on this site. so the flat file has thousands of rows each record.

Scheme

  Field separator
       v           v quote followed by blank or data  (numbers or text or even + or -)
TEST_AP.RDNDISPLAY "
   ^       ^ Field Name
Record name

Example

---------------
TEST_AB.RDNDISPLAY "
TEST_AB.DSPLY_NAME "
TEST_AB.TIMACT "
TEST_AB.NETCHNG_1 "
TEST_AB.TRADE_DATE "
TEST_AB.ACTIV_DATE "
TEST_AB.BID "
TEST_AB.ASK "
TEST_AB.MATUR_DATE "
TEST_AB.COUPN_RATE "
TEST_AB.MID_PRICE "
TEST_AB.MKT_MKR_NM "
TEST_AB.RECORDTYPE "
TEST_AB.SETTLEDATE "
TEST_AB.BID_YIELD "
TEST_AB.ASK_YIELD "
TEST_AB.GEN_VAL1 "
TEST_AB.GEN_VAL2 "
TEST_AB.GEN_VAL3 "
TEST_AB.GEN_VAL4 "
TEST_AB.SPARE_NM1 "
TEST_AB.SPARE_NM2 "
TEST_AB.SPARE_NM3 "
TESTRICU=L.DSPLY_NAME "TEST
TESTRICU=L.TIMACT "
TESTRICU=L.TRDPRC_1 "
TESTRICU=L.CURRENCY "GBP
TESTRICU=L.TRADE_DATE "
TESTRICU=L.TRDTIM_1 "
TESTRICU=L.OPEN_PRC "
TESTRICU=L.HST_CLOSE "
TESTRICU=L.BID "
TESTRICU=L.ASK "0
TESTRICU=L.BIDSIZE "
TESTRICU=L.ASKSIZE "
TESTRICU=L.YIELD "
TESTRICU=L.PERATIO "
TESTRICU=L.PCTCHNG "
TESTRICU=L.CLOSE_BID "
TESTRICU=L.CLOSE_ASK "
TESTRICU=L.STRIKE_PRC "
TESTRICU=L.MATUR_DATE "31 Dec 1906
TESTRICU=L.COUPN_RATE "+4
TESTRICU=L.OFFCL_CODE "1003
TESTRICU=L.HSTCLSDATE "
TESTRICU=L.BOND_TYPE "
TESTRICU=L.BCKGRNDPAG "
TESTRICU=L.ISSUE_DATE "01 Jan 2004
TESTRICU=L.PUTCALLIND "
TESTRICU=L.NAVALUE "
TESTRICU=L.NAV_NETCHN "
TESTRICU=L.MID_PRICE "
TESTRICU=L.EUROCLR_NO "
TESTRICU=L.CEDEL_NO "
TESTRICU=L.VALOREN_NO "100
TESTRICU=L.NAVDATE "
TESTRICU=L.NAVALUE_1 "
TESTRICU=L.NAVDAT_1 "
TESTRICU=L.PRTY_PRICE "
TESTRICU=L.ISSUE_PRC "

I want to be able to extract this file in table format that can be read into excel in Column format with data values populating in rows. So out put would look like

RECORDNAME       RDNDISPLAY  DSPLY_NAME CURRENCY TIMACT NETCHNG_1 TRADE_DATE ACTIV_DATE BID ASK MATUR_DATE COUPN_RATE OFFCL_CODE ISSUE_DATE VALOREN_NO  .... so on
TEST_AB          ;
TESTRICU=L       ;          ;TEST      ;  GBP    ;       ;             ;             ;  ;   0   31 Dec 1906 ;  +4      ; 1003    ;  01 Jan 2004 ; 100     .... so on

So maybe the code would look for every possible field name then read the file in again search for RDNDISPLAY or DSPLY_NAME to know the beginning and end of the record (start of another record) extract the record name e.g WHATEVER and put in row format under each field header. do this for every record, if the raw name does not exist in a file is replaced by ; so that once imported into excel it can be easy done.

  • Can the data part contain spaces, quotes or dots? – Hauke Laging Feb 6 '15 at 18:39
  • Some example of the data MATUR_DATE "45371 – Sal Allan Feb 6 '15 at 18:46
  • MID_PRICE "+1.783 or ROW80_3 "06 FEB 2015 or GN_TXT24_3 "28 Jun 10 13:31 or "Open End Turbo Call on EUR/USD or CH264445=U or test_test_test or Open End T or (usa/gbp) but no it does NOT contain " in the data – Sal Allan Feb 6 '15 at 18:50
2

There are a couple of tricky things with CSV: a field contains an embedded field separator, or a field contains embedded quotes. I added 2 lines to your sample data:

TEST_AB.foo " with "embedded quotes" here
TESTRICU=L.foo " with an inner; semicolon

And a scary perl solution is: save this in a file named "text2csv.sh"

#!/bin/sh
perl -lne '
      @F = split /\s*"\s*/, $_, 2;
      ($record, $field) = split /\./, $F[0];
      $fields{$field} = 1;
      $records{$record} = 1;
      $data{$record}{$field} = $F[1];
  } END {
      print join ";", "RECORDNAME", keys %fields;
      for my $rec (keys %records) {
          print join";", $rec, map {
                                   $q=0;
                                   if (/"/) {s/\"/\"\"/g; $q=1}
                                   if (/;/) {$q=1}
                                   $q ? qq{"$_"} : $_
                               } @{$data{$rec}}{keys %fields};
      }
' "$1" > "$1.csv"

And run it like this:

sh text2csv.sh /path/to/myfile.txt
cat /path/to/myfile.txt.csv
RECORDNAME;PERATIO;NAVALUE_1;ISSUE_PRC;BCKGRNDPAG;GEN_VAL2;SPARE_NM2;GEN_VAL3;COUPN_RATE;DSPLY_NAME;CLOSE_BID;NAVALUE;VALOREN_NO;TRDTIM_1;PRTY_PRICE;ISSUE_DATE;RECORDTYPE;OFFCL_CODE;MID_PRICE;BID;TRDPRC_1;ASK;ACTIV_DATE;STRIKE_PRC;HSTCLSDATE;ASK_YIELD;MATUR_DATE;NAV_NETCHN;NAVDATE;PCTCHNG;TRADE_DATE;BIDSIZE;NAVDAT_1;ASKSIZE;MKT_MKR_NM;foo;OPEN_PRC;NETCHNG_1;BID_YIELD;RDNDISPLAY;YIELD;CURRENCY;TIMACT;GEN_VAL1;HST_CLOSE;PUTCALLIND;CLOSE_ASK;SPARE_NM3;BOND_TYPE;SPARE_NM1;SETTLEDATE;EUROCLR_NO;GEN_VAL4;CEDEL_NO
TESTRICU=L;;;;;;;;+4;TEST;;;100;;;01 Jan 2004;;1003;;;;0;;;;;31 Dec 1906;;;;;;;;;"with an inner; semicolon";;;;;;GBP;;;;;;;;;;;;
TEST_AB;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;"with ""embedded quotes"" here";;;;;;;;;;;;;;;;;;
  • Hi Glen, thank you. This does look like what I want but how do I run the code above and where do I put input file and output file. thank you. I will run it on linux redhat – Sal Allan Feb 6 '15 at 19:19
  • answer updated. – glenn jackman Feb 6 '15 at 20:20
  • The quotes and inner separator are normally easy compared to the newline that can occur in a (quoted) field. Can you include in the answer if your code handles that or not? – Anthon Feb 11 '15 at 6:33
  • This answer does not handle quoted newlines. – glenn jackman Feb 18 '15 at 12:34
1

If you like awk:

awk -F' *[."]' '
{
    FName[$2]=1
    RName[$1]=1
    Data[$1,$2]=$3
}
END{
    printf("%s;","RECORDNAME")
    for (f in FName)
        printf ("%s;",f)
    print ""
    for (i in RName){
        printf ("%s",i)
        for (j in FName)
            printf ("%s;",Data[i,j])
        print ""
        }
}' text.file

Outputs:

RECORDNAME;BID_YIELD;PCTCHNG;NAVALUE_1;EUROCLR_NO;ACTIV_DATE;MKT_MKR_NM;PRTY_PRICE;NAV_NETCHN;ASKSIZE;TRDTIM_1;HST_CLOSE;CLOSE_BID;SPARE_NM1;CURRENCY;SPARE_NM2;SPARE_NM3;TRDPRC_1;NAVDATE;DSPLY_NAME;CLOSE_ASK;OPEN_PRC;MATUR_DATE;BCKGRNDPAG;STRIKE_PRC;OFFCL_CODE;ASK_YIELD;ISSUE_PRC;VALOREN_NO;BOND_TYPE;ISSUE_DATE;PUTCALLIND;RDNDISPLAY;BID;MID_PRICE;COUPN_RATE;RECORDTYPE;ASK;NAVALUE;TIMACT;YIELD;NETCHNG_1;PERATIO;SETTLEDATE;HSTCLSDATE;NAVDAT_1;GEN_VAL1;GEN_VAL2;CEDEL_NO;GEN_VAL3;GEN_VAL4;BIDSIZE;TRADE_DATE;
TEST_AB;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
TESTRICU=L;;;;;;;;;;;;;GBP;;;;;TEST;;;31 Dec 1906;;;1003;;;100;;01 Jan 2004;;;;;+4;;0;;;;;;;;;;;;;;;;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.