1

I need help writing a script that would extract from a master file the oldest records and save them in a separate file. The file contains hundreds of ISIN codes and additional data. For each, there are daily records. The perfect script would take out the whole oldest row and save it in a separate file named by that ISIN code. So I am guessing split and grep combinations?

Example:

Column A - ISIN code (i.e. XX1234567891) <- 2 letters and 10 numbers

Column C - Date - 08/04/2019

Column B & D-I - relevant data.

Any suggestions ? Please keep in mind that I am really at the beginning of my journey with bash.

Thank you in advance for any help

Below is the link to the example file: https://drive.google.com/file/d/1Q3qhrVlIMA7cJhDVxjxoHCipEl8sV-xo/view?usp=sharing

example with 3 different ISIN's: example with 3 different ISIN's

One file with the oldest record: One file with the oldest record

Second file with the oldest record: Second file with the oldest record

3rd one with the oldest records - both with same date: 3rd one with the oldest records - both with same date

Dates are in DD/MM/YYYY format (now that I look at it in Libre I can see that the date format changed to MM/DD/YYYY - but that I can adjust later).

  • 1
    How fields are separated, by space or some char? Is there a header with column names? You can edit a question and place few lines of file as example – LeonidMew Apr 8 '19 at 10:13
  • Date, is it Day/Month/Year or Month/Day/Year? – LeonidMew Apr 8 '19 at 10:14
  • I'm writing script. Can you format a date with leading zeros like 01/01/2019 but not like 1/1/2019? – LeonidMew Apr 8 '19 at 10:35
  • The original file has the dates with leading zeros. As I am now using a different device while writing this post the dates just changed(due to the system settings ?) – Tom Lemke Apr 8 '19 at 10:41
  • 1
    please do not post image of text also! please edit your question and post them as a text, please post your input + output (or outputs) you expect in text format – αғsнιη Apr 8 '19 at 10:53
0

Try this,

tail -n+2 file \
| sort -n -k 3.7,3.10 -k 3.4,3.5 -k 3.1,3.2 \
| awk '!s[$1]++{print > $1}'
  • tail -n+2 file just removes the header line
  • sort -n -k 3.7,3.10 -k 3.4,3.5 -k 3.1,3.2 sorts the date.
  • awk '!s[$1]++{print > $1}' print only first row for each ISIN

If you had YYYY/MM/DD as date format, you could just do sort -k3.


awk only solution:

awk '
  NR==1{next}
  {
    split($3,d,"/")
    t=mktime(d[3]" "d[2]" "d[1]" 00 00 00")
    if(!s[$1]||t<s[$1]){
      s[$1]=t
      r[$1]=$0
    }
  }
  END {
    for (i in r) { print r[i] > i}
  }' file
0

You show examples in GUI interface, in plain text there should be separators between columns, I used space as separator, replace it in script with actual one.

Here a file what I used as example:

$ cat ISIN 
XX1234567890 Bcolumn 08/04/2019 Dcolumn
XX2234567890 Bcolumn 09/03/2019 Dcolumn
XX3234567890 Bcolumn 07/05/2019 Dcolumn
XX3234567890 Bcolumn 07/05/2018 Dcolumn
XX3234567890 Bcolumn 07/05/2016 Dcolumn
XX3234567890 Bcolumn 07/05/2017 Dcolumn
XX1234567890 Bcolumn 07/05/2015 Dcolumn

Here is a script what process oldest 3 lines from input file, you can adjust this number:

#!/usr/bin/env bash

# replace ISIN with actual file name
# head -n 3 results in 3 oldest rows
# note the field separator, replace it with actual
result=$(sort --field-separator=' ' --key=3.7,3.10 --key=3.4,3.5 --key=3.1,3.2 ISIN| head -n 3)
# if input contain header line following line will take that into account, 
# uncomment it, and comment the above line
#result=$(tail -n+2 ISIN| sort --field-separator=' ' --key=3.7,3.10 --key=3.4,3.5 --key=3.1,3.2| head -n 3)

# read output line by line
# and delete exising files named as ISIN
# may be left from previous script run
while IFS='' read -r i || [[ -n "$i" ]]; do
  # extracting first column
  first=${i%% *}
  rm $first &>/dev/null
done <<<$result

# read output line by line
while IFS='' read -r i || [[ -n "$i" ]]; do
  echo "output: $i"
  # extracting first column
  first=${i%% *}
  echo "Writing to file $first"
  # append to a file with first column as a name
  echo "$i" >>$first
done <<<$result

Adjust --field-separator=' ' with real separator, may be it ',' or ';' and change head -n 3 to desired number of old rows. Also script reads input from ISIN file, replace it with your actual file name.

Update: Script append lines to ISIN files, so on second run - duplicate lines can appear. I added a loop what deletes this old ISIN files, and creates again, so its possible to run more then once with a correct result. Output files contain one or more lines with same ISIN.

  • Thx - I am going to try it out. I have made a test file and concatenated the columns with a space separator. I will get back to you how did it went. – Tom Lemke Apr 8 '19 at 11:17

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.