Split big file in multiple files based on column

user_11423137 0
Viewed 113

I have a file with a semicolon as delimiter and headers. I would like to split that file based on the date column. The file has dates in ascending order.

The name of the output file should be as follows: 01_XX_YYMMDD_YYMMDD.txt

e.g. 01_XX_210920_210920.txt

Example

Here's an example file:

--INPUT 
K;V1.00;;;;;;
P;01.01.2021 00:01;16;EXA;31;TESTA;95.9;XXXX
P;01.01.2021 00:02;33;EXA;31;TESTA;95.9;XYXY
P;02.01.2021 00:54;16;EXB;33;TESTB;94.0;DWAD
P;02.01.2021 00:56;11;EXB;33;TESTB;94.0;DADA
P;03.01.2021 01:00;16;EXC;32;TESTC;94.6;WEWEQ
P;03.01.2021 01:22;16;EXC;32;TESTC;94.6;QEQR
P;04.01.2021 02:39;16;EXD;33;TESTD;94.3;DFAG

The output should be as follows, while taking the previous file as example

--OUTPUT FILES
FILE1: 01_XX_210101_210101.txt
P;01.01.2021 00:01;16;EXA;31;TESTA;95.9;XXXX
P;01.01.2021 00:02;33;EXA;31;TESTA;95.9;XYXY

FILE2: 01_XX_210102_210102.txt
P;02.01.2021 00:54;16;EXB;33;TESTB;94.0;DWAD
P;02.01.2021 00:56;11;EXB;33;TESTB;94.0;DADA

FILE3: 01_XX_210103_210103.txt
P;03.01.2021 01:00;16;EXC;32;TESTC;94.6;WEWEQ
P;03.01.2021 01:22;16;EXC;32;TESTC;94.6;QEQR

FILE4: 01_XX_210104_210104.txt
P;04.01.2021 02:39;16;EXD;33;TESTD;94.3;DFAG

I tried AWK but no success because of the timestamp my file has… Thank you!

x

UPDATE: Solution

    awk -F';' '
NR > 1 {
   dt = substr($2,9,2) substr($2,4,2) substr($2,1,2)
   print > ("01_LPR_" dt "_" dt ".txt")
}' input
awk
3 Answers

You may try this awk:

awk -F';' '
NR > 1 {
   dt = substr($2,9,2) substr($2,4,2) substr($2,1,2)
   print > ("01_XX_" dt "_" dt ".txt")
}' input

For the updated requirements in comments below:

awk -F';' '
NR == 1 {
   hdr = $0
   next
}
{
   dt = substr($2,9,2) substr($2,4,2) substr($2,1,2)
}
dt != pdt {
   if (pdt) {
      print "END" > fn
      close(fn)
   }
   fn = "01_XX_" dt "_" dt ".txt"
   print hdr > fn
}
{
   print > fn
   pdt = dt
}
END {
   print "END" > fn
   close(fn)
}' input
user_5482250

With your shown samples, please try following awk code, this is using close function which will take care of avoiding too many opened files error too.

awk -F'\\.| |;' '
{
  outputFile="01_XX_"substr($4,3)$3 $2"_"substr($4,3)$3 $2".txt"
}
FNR>1{
  if(prev!=outputFile){
   close(prev)
  }
  print > (outputFile)
  prev=outputFile
}
'  Input_file
user_58665800

Try the following script:

while read; do
  day=${REPLY:2:2}
  month=${REPLY:5:2}
  year=${REPLY:10:2}
  echo "$REPLY" >> 01_XX_${year}${month}${day}_${year}${month}${day}.txt
done<inputfile.txt

or the same in "oneline":

while read do echo "$REPLY" >> 01_XX_${REPLY:10:2}${REPLY:5:2}${REPLY:2:2}_${REPLY:10:2}${REPLY:5:2}${REPLY:2:2}.txt; done<inputfile.txt
user_132163870
Related