1

So far I was only reading, but now I have to start with a complicated question. I was googling crazy but I can't find an answer and it must be in BASH. (Thank you for the ideas like Perl, not an option unfortunately.)

I have a text file where the data is separated with | character like this:

DETAIL||||||||||103|line1  
DETAIL||||||||||103|line2  
DETAIL||||||||||105|line3  
DETAIL||||||||||433|line4  
DETAIL||||||||||433|line5 

I managed to split it to new files by the 11th key field using this:

cat extract_GL2_*.txt | grep DETAIL | awk -F\| '{print>>"SPLIT/"$11".txt"}'

There are two problems with this:
1. I would need to have the names assigned from an another file called Company.txt (placed in the parent folder of SPLIT) having the values of the key column like this (so basically I need to replace the number with something meaningful):

Company.txt:

103|US100E1  
104|US100E1  
105|US100E1  
433|EMEAE1
  1. As you can see from the example the key is not unique, multiple values might be merged to one based on the keys above Note: I'd prefer to have this file without the ".txt" which is needed for the output but I am happy to rework the Company.txt if the script is easier that way.

It is possible to have a second step which finds the filename by key and does the merger deleting the old files, but it would be more elegant to do in first step "simply" replacing the target file name from the second file. I failed both methods, but I'm fine with whichever is simpler/faster.

So the split must be based on the value from column 11 of the original file(s) and the filename on the second file. There can be more source files, they must append, and the split files may contain more than one of the key fields depending on the filename assignment.

Company.txt and extract_GL2* files are in the same folder, the split files need to go to a SPLIT subfolder.

A little code (the part I am unable to do are only pseudo, but can't test the rest either):

#!/bin/bash  
while read line; do  
 company="${line|awk -F\| '{print $11}'}"  
 newfilename="${cat Company.txt | grep $company | awk -F\| '{print $2}' | head -1}" + ".txt"  
 _replace chr(34) to space in $line_  
 _replace ,  to space in $line_  
 _replace | to , in $line_  
 echo "$line" >> "SPLIT\$newfilename.txt"  
done < "extract_GL2_*.txt"  

Many thanks: Tamas

  • 1
    Please edit your question and add examples of the original files with relative paths and an example of the desired result. It is not clear what you want. – Raffa Jan 7 at 20:53
  • Brainstorming: you need something to rename like while IFS='|' read first second; do rename "s/$first/$second/" $first.txt; done < Company.txt then to combine while IFS='|' read first second; do cat $first.txt >> $second.txt; done < Company.txt then to clean up while IFS='|' read first second; do rm $first.txt; done < Company.txt – Raffa Jan 8 at 0:32
  • many thanks but this is not based on the original file extract_GL2*.txt and I don't get what are first and second are for... – Tamas Balint Jan 8 at 16:10
0

Place your files in the parent directory of the SPLIT directory and create a script file following the steps below and call it my_script.sh so the structure of this directory will be like this:

Company.txt
extract_GL2_1.txt
extract_GL2_23.txt
my_script.sh
SPLIT/

IMPORTANT:

  • Files in the SPLIT directory will get created and deleted, DO NOT PLACE ANY FILES IN THE SPLIT DIRECTORY.

  • Run the script ONLY ONCE or otherwise you will get duplicate entries in the result files.


To create and use the script file, please follow these steps:

  1. Create and edit the script file in your parent directory by running the following command in the terminal (cd to the directory first):

    nano my_script.sh

  2. Copy and paste the following code into the editor:

#!/bin/bash

echo
echo "splitting files ..."
echo
cat extract_GL2*.txt |
        while read line
                do
                        echo "$line" | grep DETAIL | awk -F\| '{print >> "SPLIT/"$11".txt"}'
                done


echo "renaming splitted files ..."
echo
        while IFS='|' read f1 f2
                do
                        mv -n "SPLIT/$f1.txt" "SPLIT/$f2.txt" 2> /dev/null
                done < "Company.txt"


echo "combining splitted files ..."
echo
        while IFS='|' read f1 f2
                do
                        cat "SPLIT/$f1.txt" >> "SPLIT/$f2.txt" 2> /dev/null
                done < "Company.txt"

echo "cleaning up ..."
echo
        while IFS='|' read f1 f2
                do
                        rm "SPLIT/$f1.txt" 2> /dev/null
                done < "Company.txt"

  1. Save the script file and exit the editor by pressing Ctrl + X then press Y.

  2. Make the script file executable by running the following command in the terminal:

    chmod +x my_script.sh

  3. Run the script by running the following command in the terminal:

    bash my_script.sh

Done

Best of luck

  • Hello Raffa, thank you very much but it seems there is something I did not emphasize. The company.txt can't be the key as there are more companies in it than we have data for. Isn't it possible to do the way I suggested? (Substitute the file name from the company.txt and pipe that name instead of split then merge?) (Apologies for the delay, but this issue kind of lost urgency to other tasks...) – Tamas Balint Jan 16 at 7:43
  • Also I don't understand why to rename the file and combine files in separate steps. If I have the proper new filename in a variable >> would write to to the proper output file at once. – Tamas Balint Jan 16 at 10:22
  • @TamasBalint Did you try the script? It should work as you requested in your question. You do not need to split first. The script will do everything for you. Please try it and report back. – Raffa Jan 16 at 21:42
  • Hello Raffa, it was hard to tell seemed wrong with all the 0 files, but it seems we are getting closer. There are two issues left, one I guess I haven't told yet: if the company txt doesn't contain the reference the data is deleted - should be retained. Also it does not contain the text transformations I mentioned with "replace"... – Tamas Balint Jan 20 at 13:22
  • @TamasBalint I wrote the script based on the examples you provided in your question. Please update and expand the examples in your question so I can reproduce the results and issues you had running the script and update my answer to solve them. Thank you – Raffa Jan 23 at 18:58

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.