!******************************************************************************!
! Create a T5000 file from a CSV file and a TBL file describing which columns
! in the CSV file form a given column in the new T5000 file
!
! Arguments: CSV file name (.csv file)
!            grouping table (.tbl file)
!            output file name (5000 file)
!            [OPTIONAL] type_500x=   DEFAULT [5000]
!            [OPTIONAL] RF=          DEFAULT [None (U)]   Reference Frame
!            [OPTIONAL] XU=          DEFAULT [None (U)]   Abscissa Units
!
! This macro takes in a csv file and a tbl file and generates the matching T500x file
! Note: Neither spaces or commas are allowed in the column names
! The form of the tbl file:
!    T5000_COL_NAME={A_CSV_COL_NAME,...,ANOTHER_CSV_COL_NAME}
! (See bottom of file for example input files)
!
!   Author : Jeanette McCall
!
!------------------------------------------------------------------------------!
! Create a T5000 file from a CSV file and a TBL file describing which columns
! in the CSV file form a given column in the new T5000 file
startmacro fn:csv_file fn:grouping_tbl  fn:t5_out_name s:type_500x["5000"] s:rf["None (U)"] s:XU["None (U)"]
  res needexit false

  !
  ! GET NEW T5000 FILE'S COMPONENTS INFORMATION
  !
  ! read in groupings / configuration information describing which of the csv file
  ! columns are to be combined to form the new T5000 file columns
  table t5groupings CREATE "^grouping_tbl"
  
  if needexit eqs false then
  ! grab the components information from this grouping table including data format 
    ! and units information
    res s:components ""
    foreach key INTABLE t5groupings
      res components components.concat("^{key},") ! separate components list with commas
    endfor
    res components components.substring(0,components.length - 1) ! strip off comma after last component

    !
    ! CONVERT THE CSV FILE INTO A TABLE 
    !
    res csv_info "^{csv_file}{HEADERROWS=1}"
    CONVERT csv_info csv2tbl DATA_TBL

    ! 
    ! CREATE THE OUTPUT T5000 FILE - HEADER INFORMATION, BUT NO DATA YET
    !
    HEADERMOD/CREATE ^t5_out_name type=^type_500x COMPONENTS=(^{components}) rf=rf xu=xu
 
    ! 
    ! OPEN THE NEW T5000 FILE IN ORDER TO APPEND THE DATA FROM THE CSV FILE 
    ! (now in the table DATA_TBL)
    !
    file open/d/a df ^t5_out_name

    ! GRAB ONE VALUE/ROW AT A TIME AND PLACE THE DATA IN THE NEW T5000 ACCORDING
    ! TO THE GROUPINGS SPECIFIED IN THE GROUPING TABLE
    foreach VAL INTABLE/V DATA_TBL

      ! MAKE A TABLE (in String form) THAT CONTAININS THE DATA/INFORMATION NEEDED FOR ONE ROW OF THE T5000
      ! It will take the form:
      ! {T5COL1_NAME=(1stDataValue,...,LastDataValue),....,T5COLn_NAME=(1stDataValue,...,LastDataValue)}
      ! where there could be 1 (Scalar) or many values per column
      res s:table_set_string "{"
      foreach key INTABLE/K t5groupings
        ! GET NEXT T5000 COLUMN NAME
        ! Only grab the name portion (strip off the data format & unit information
        ! of the T5000 column names from the grouping/configuration table
        res l:flags_index key.indexOf("/")
        if ^flags_index gt 0 THEN
          res s:set_data_table_key key.substring(0,^{flags_index})
        else
          res s:set_data_table_key ^{key} ! If no data format & unit information, then whole key is column name
        endif
        ! GET VALUES GROUPED INTO THAT COLUMN
        res s:value_str ""
        foreach subkey INTABLE/K t5groupings.^{key}
          res value ^val.^subkey                     ! get the next value for this T5000 col
          res value_str value_str.concat("^value,")  ! append it to the column information
        endfor
        ! Trim off the extra comma and then add this column's information to the
        ! the rest of the string defining this row of the T5000
        res value_str value_str.substring(0,value_str.length - 1) ! trim off the extra 
        res table_set_string table_set_string.concat("^{set_data_table_key}=(^{value_str}),")

      endfor !(end foreach key INTABLE/K t5groupings) 
      ! Complete string/table describing this row of the T5000 by getting rid of the
      ! extra comma and adding the closing brace
      res table_set_string table_set_string.substring(0,table_set_string.length -1)
      res table_set_string table_set_string.concat("}")
      ! The string/table describing this row of the T5000 is complete and ready to be inserted

      ! WRITE OUT THIS ROW TO THE T5000 FILE
      invoke result df.writeDataTable(^table_set_string) 

    endfor 

    !close file
    file close df

  endif

endmacro

!****************** example input files ***********************************
! (Note: The data is for example purposes only & not intended to be accurate)
!
! Grouping table file contents:
!
!   ! Grouping table
!   POSITION/VD/CARTESIAN/DISTANCE_M={POSX,POSY,POSZ}
!   CITY_STATE/4A/UNIDENTIFIED/NONE={CITY,STATE}
!   ZIP_CODE/SX/UNIDENTIFIED/NONE={ZIPCODE}
!   ROADS_TO/5L/SCALAR/NONE={RD1,RD2,RD3,RD4,RD5}
!
! Matching CSV File:
!
!  "POSX" ,    "ZIPCODE", "POSY",  "POSZ",     "CITY",   "STATE","RD1", "RD2", "RD3", "RD4", "RD5"
!  024.43333,   23173,   054.65000, 200.0,   "Richmond", "VA",   64, 95, 195, 295, 301
! -034.94639,   24060,   138.52944, 201.0,   "Blacksburg","VA",  460, 685, 603, 11, 81
! -028.01667,   75201,   120.53333, 202.0,   "Dallas",    "TX",  35, 30, 75, 635, 45
!
! example command:
! %CONVERT_CSV_TO_T5000 ellipsetimecpy.csv ellipseflagstime5000grp.tbl myfile.tmp rf=ecr
!
!  ! Grouping table
!  POS/VD/GEODETIC/DISTANCE_M={ALT,LAT,LON}
!  ELPS/VD/UNDEFINED/NONE={EMAJ,EMIN,ETILT}
!  NAME/9A/UNIDENTIFIED/NONE={NAME}
!  TIME/SD/SCALAR/TIME_S={TIME}
!  FLAG/8B/UNIDENTIFIED/NONE={F1,F2,F3,F4,F5,F6,F7,F8}
!
!  Matching CSV file
!  "ALT","LAT","LON","EMAJ","EMIN","ETILT","NAME","TIME","F1","F2","F3","F4","F5","F6","F7","F8"
!  0.0,0.0,45.0,     300000.0,70000.0,0.0,  JEREMY,2.22447013119E9,   8,0,6,2,-9,1,4,4
!  0.0,0.0,45.0,     300000.0,70000.0,15.0, JEREMY,2.22447013112E9,   0,0,3,2,-9,1,4,4
!  0.0,0.0,45.0,     300000.0,70000.0,30.0, JEREMY,2.22447013117E9,   0,0,4,2,-9,1,4,4
!  0.0,0.0,45.0,     300000.0,70000.0,45.0, JEREMY,2.22447013118E9,   0,0,2,2,-9,1,4,4
!  0.0,375.0,420.0,  300000.0,70000.0,60.0, JEFF,2.22447013115E9,     8,0,2,2,-9,1,4,4
!  0.0,-165.0,-285.0,300000.0,70000.0,75.0, JAY,2.22447013116E9,      5,0,4,2,-9,1,4,4
!  0.0,15.0,87.0,    300000.0,70000.0,90.0, ANDREW,2.22447013120E9,   8,0,3,2,-9,1,4,4
!  0.0,15.0,105.0,   300000.0,70000.0,105.0,JENNIFER,2.22447013114E9, 10,0,5,2,-9,1,4,4
!  0.0,60.0,0.0,     300000.0,70000.0,45.0, HI,2.22447013113E9,       4,0,3,2,-9,1,4,4
!
!*****************************************************************************

!******************************************************************************!
! If an unexpected exception is caught, mark test as failed, unless it is expected
!******************************************************************************!
procedure processException m:emsg
  if expectedException REXISTS then
    assert/text="^reg.main.name: Expected ERROR starts with: ^{expectedException}" expectedException EQSS emsg.data.message
    remove expectedException
  else
    say "An error occured when running this macro, please make sure the following are valid"
    say "csv file name: ^csv_file"
    say "grouping table name: ^grouping_tbl"
    say "output file name: ^t5_out_name"
    table mytable create grouping_tbl
    res mytable /all
    res needexit true
    if "Trace" SUBS ENV.DEBUG then invoke ,, emsg.data.printStackTrace()
  endif
return
