1

I have a data file with many lines,and the field list may vary. Following is the sample line format. Each field is delimited by @@@:

runAs="X094174"@@@format="excel2007"@@@path="/Path1"@@@name="X143122"@@@name="X182881"@@@name="X094174"@@@address="t.l@yahoo.com"@@@address="s.k@yahoo.com"@@@AgentLoc="/loc1"

[![enter image description here][1]][1]

I want to get data in this format, more like a database table - column/row format.

runAs      format       path       AgentLoc    name      address    
X094174    excel2007    /Path1     /loc1       X143122   t.l@yahoo.com   
X094174    excel2007    /Path1     /loc1       X182881   s.k@yahoo.com
X094174    excel2007    /Path1     /loc1       X094174

Can you please suggest, how can I achieve using file read loop and with awk.

if it is easy to produce data in following format, it is ok too

runAs      format       path       AgentLoc    name      address    

X094174    excel2007    /Path1     /loc1       X143122      
X094174    excel2007    /Path1     /loc1       X182881   
X094174    excel2007    /Path1     /loc1       X094174
X094174    excel2007    /Path1     /loc1                 t.l@yahoo.com
X094174    excel2007    /Path1     /loc1                 s.k@yahoo.com
4
  • Welcome to the site. Could you explain (ideally on the sample input/output you provide) what you mean by "new lines should have nonrepeating values along with repeating values in seperate lines". What would be the "nonrepeating" and "repeating" values, and why/how should they be treated differently? – AdminBee Jan 22 at 11:35
  • Thanks for looking into.From the sample data.. runAs,format,path,AgentLoc fields will have one value/non-repeating fields.Where as name and address fields can have multiple values/repeating fields. So, when represented in table format..rest of the field values need to be repeated for every value of name and address fields.Please let me know if I miss any details . – Kris Jan 22 at 15:40
  • So if I understand you correctly: (1) the first 4 columns have the same value for all rows, but the 5th and 6th have individual values. (2) if a field value is not speficied for columns 1-4, we print the last value specified, but if a field value is not specified for columns 5 and 6, that field is to be left empty (see "address" field in the 3rd data row)? How would the input line look if there was a 4th data line, where the "address" field does have an entry again? How to determine what constitutes "a new row"? – AdminBee Jan 22 at 16:07
  • if It make the logic simpler to produce data in the following format that is ok as well. repeat runAs,format,path and AgentLoc values for every value of name and address. runAs format path AgentLoc name address X094174 excel2007 /Path1 /loc1 X143122 X094174 excel2007 /Path1 /loc1 X182881 X094174 excel2007 /Path1 /loc1 X094174 X094174 excel2007 /Path1 /loc1 t.l@yahoo.com X094174 excel2007 /Path1 /loc1 s.k@yahoo.com – Kris Jan 22 at 19:23
2

This awk generates the desired table form.

$ cat dat
runAs="X094174"@@@format="excel2007"@@@path="/Path1"@@@name="X143122"@@@name="X182881"@@@name="X094174"@@@address="t.l@yahoo.com"@@@address="s.k@yahoo.com"@@@AgentLoc="/loc1"

First, put the data in a one-entry-per-line form:

$ awk -F '@@@' '{ for(i=1;i<=NF;i++){ print $i } }' dat > tmp.dat

Then, generate the table and clean up the line endings:

$ awk -F '=' '{
    head[$1]++;
    dat[$1,head[$1]]=$2
  } END{
    max=0;
    for(i in head){
      printf i"\t"
    }
    print "";
      for(i in dat){
        split(i, arr_i, SUBSEP);
        if(arr_i[2]>max){
          max=arr_i[2]
        }
      }
      for(j=1;j<=max;j++){
        for(i in head){
          if(head[i]==1){
            printf dat[i,1]"\t"
          }else{
            printf dat[i,j]"\t"
          }
        }
        print ""
      }
  }' tmp.dat | awk -F '\t' '{ for(i=1;i<NF;i++){ printf $i"\t" } print $NF }' > dat.xls

$ cat dat.xls
runAs   format  address AgentLoc        name    path
"X094174"       "excel2007"     "t.l@yahoo.com" "/loc1" "X143122"       "/Path1"
"X094174"       "excel2007"     "s.k@yahoo.com" "/loc1" "X182881"       "/Path1"
"X094174"       "excel2007"             "/loc1" "X094174"       "/Path1"

After importing e.g. into excel, choosing TAB-stop delimiter:

enter image description here

Keep in mind that the order of appearance of the values determines the association of the lines within the table.

The above can be done in one step, thus avoiding the temporary file tmp.dat, by using pipes.

1

Since Java 11 there is this neat feature for running Java code directly from a text file, so why not solve this with Java.

Assuming you have Java 11 installed, save the code below as ConvertToTable.java, then run it in a terminal session like this:

java ConvertToTable.java < /path/to/input.txt | tee /path/to/output.txt

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * <p>
 * Reads STDIN, converts it to the desired table representation, and writes the result to STDOUT.
 * </p>
 * <p>
 * Assumes that the incoming data uses the system character set and the system line separator.
 * </p>
 * <p>
 * Requires Java 11. Example invocation in Bash:
 * </p>
 *
 * <pre>
 * java ConvertToTable.java < /path/to/input.txt | tee /path/to/output.txt
 * </pre>
 *
 * @author eomanis
 */
public class ConvertToTable {

    private static final Pattern FIELD_SEPARATOR_INPUT = Pattern.compile( "@@@", Pattern.LITERAL );
    private static final Pattern KEY_VALUE_SEPARATOR = Pattern.compile( "=", Pattern.LITERAL );
    private static final Pattern VALUE_IN_DOUBLE_QUOTES = Pattern.compile( "^\"(.*)\"$" ); // Captures the value in capturing group 1

    private static final String FIELD_SEPARATOR_OUTPUT = "\t";

    public static void main( String[] args ) {
        Matcher matcherValueInDoubleQuotes = VALUE_IN_DOUBLE_QUOTES.matcher( "" );
        Map<String, List<String>> keysAndValues = new LinkedHashMap<>(); // A map that maps a key to a list of values
        boolean firstLine = true;
        String line;
        String[] keyAndValue;
        String key;
        String value;
        int outputLinesCount;

        try (BufferedReader reader = new BufferedReader( new InputStreamReader( System.in ) )) {

            // Read and convert the incoming data, one line at a time
            while ((line = reader.readLine()) != null) { // For each line...

                // Discard the previous line's data
                keysAndValues.values().stream().forEach( List::clear );

                // Collect the line's keys and values into the map
                for (String field : FIELD_SEPARATOR_INPUT.split( line )) { // For each key=value in the text line...
                    keyAndValue = KEY_VALUE_SEPARATOR.split( field, 2 ); // Split key=value into key and value
                    key = keyAndValue[0];
                    value = keyAndValue[1];

                    // Strip the double quotes from the value
                    if (matcherValueInDoubleQuotes.reset( value ).matches()) {
                        value = matcherValueInDoubleQuotes.group( 1 );
                    }

                    // Add the value to the key's list of values
                    if (!keysAndValues.containsKey( key )) { // If required, create a new empty list in the map for the key
                        keysAndValues.put( key, new ArrayList<>() );
                    }
                    keysAndValues.get( key ).add( value );
                }

                // First line: Generate and write the column headers (assume that the first line contains all possible keys)
                if (firstLine) {
                    firstLine = false;
                    String columnHeaders = keysAndValues.keySet().stream().collect( Collectors.joining( FIELD_SEPARATOR_OUTPUT ) );
                    System.out.println( columnHeaders );
                }

                // Figure out how many output lines we will be writing for the single input line
                outputLinesCount = keysAndValues.values().stream().mapToInt( List::size ).max().getAsInt();
                // Write the output line(s)
                for (int index = 0; index < outputLinesCount; index++) {
                    int indexFinal = index;
                    String outputLine = keysAndValues.values().stream() //
                            .map( list -> getValue( indexFinal, list ) ) //
                            .collect( Collectors.joining( FIELD_SEPARATOR_OUTPUT ) );
                    System.out.println( outputLine );
                }
            }
        } catch (IOException e) {
            throw new RuntimeException( e );
        }
    }

    /**
     * @return The value for the given index, with certain workarounds
     */
    private static String getValue( int index, List<String> values ) {

        if (values.isEmpty()) {
            // The text line did not contain the key at all
            return "";
        } else if (values.size() == 1) {
            // Value of a key that occurred exactly once in the text line: These are repeated on all output rows
            return values.get( 0 );
        } else {
            // Value of a key that occurred multiple times in the text line: Only print them for their respective output row
            return (index < values.size()) ? values.get( index ) : "";
        }
    }
}
1
  • Wow, thanks for the effort.I will test and post the results.Really appreciate. – Kris Jan 23 at 0:45

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.