0

I have to generate the file dynamically from the source file based on the below control file.

control_file.txt 
1,3,5,-1,8,-1,4

The control file contains the position of columns which I require from the source file.

Example: 1 column, 3 column, 5 column, blank column (-1 indicates blank column) and so on..

I have written a shell to read the position from the control file, one by one, to generate multiple files. Then finally use the paste command to generate new files, incase of -1 values I created touch files. Based on sequence of file order, it is pasted by ls-v option.

So my existing shell looks like below:

if [ position != -1 ]
then
cut -d, -f$position > file_$var.csv 
else
touch file_$var.csv
fi
paste -d, $(ls -v file_*.csv) > newe_file.csv

I hope there is a way to minimize the I/O of files, I am looking for something below:

cut -d, -f1,3,5 > file1.csv
touch file2.csv
cut -d, -f8 > file3.csv
touch file4.csv
cut -d, -f4 > file5.csv

Or better solution will be great.

The number of columns in the source files will be in the hundreds.

Expected results:

input-file is sample.csv

col1,col2,col3,col4,col5,col6,col7,col8
1,2,3,4,5,6,7,8
9,10,11,12,13,14,15,16

output.csv:

col1,col3,col5,-1,col8,-1,col4
1,3,5,,8,,4
9,11,13,,16,,12

output.csv is based on controlfile.txt

  • this look like code golf, can you show use expected result ? – Archemar Sep 18 '14 at 7:08
  • @Archemar please find the expected details. If required i will share the entire script which i have written.Thanks – William R Sep 18 '14 at 7:18
  • CSV files can have newlines in the elements, elements can be quoted or not and the separater is can default to a different character than ,. All in all best handled with a programming language like python/ruby/perl with an appropriate CSV handling library. – Anthon Sep 18 '14 at 10:34
1

Another answer, a little simpler than the others:

#!/bin/bash
fields=$(sed -r -e 's/-1/ /g' -e 's/,/ FS /g' \
  -e 's/([0-9]+)/\$\1/g' control_file.txt)
awk -F, "{print ${fields}}" $1

The first command converts control_file.txt into a suitable awk command:

$1 FS $3 FS $5 FS FS $8 FS FS $4

to run it:

$ ./script.sh input.csv
col1,col3,col5,,col8,,col4
1,3,5,,8,,4
9,11,13,,16,,12

On your other sample:

$ ./script.sh sample.csv
BP ID,CurrentMonetary balance ,Provider contract id,,End Date,,charge Plan names 
1100001538,251,00000000000000000141,,18-Oct-12,,[B2] R2 LTE CHARGE PLAN 
1100003404,45.22,00000000000000009349,,23-Nov-13,,B0.3 ECS_CHARGE_PLAN DROP1 V3
|improve this answer|||||
  • $Sebastian fields are assigned with values as mentioned but awk is not printing the desired values. plz help me.. – William R Sep 18 '14 at 9:59
  • what is the error? have you called ./script.sh input.csv with the input.csv as provided above? – Sebastian Sep 18 '14 at 10:03
  • BP ID,Prepaid Account No,CurrentMonetary balance ,charge Plan names ,Provider contract id,Contract Item ID,Start Date,End Date 1100001538,001000002506,251,[B2] R2 LTE CHARGE PLAN ,00000000000000000141,[B2] R2 LTE CHARGE PLAN _00155D10E20D1ED39A8E146EA7169A2E00155D10E20D1ED398FD63624498DB4A,16-Oct-12,18-Oct-12 1100003404,001000004029,45.22,B0.3 ECS_CHARGE_PLAN DROP1 V3,00000000000000009349,B0.3 ECS DROP2 V0.2_00155D10E20D1ED39A8E146EA7169A2E00155D10E20D1ED398FD63624498DA2E,16-Nov-13,23-Nov-13 My sample data can you please test with this ? – William R Sep 18 '14 at 10:20
  • 18ct-12 1100003404 values are wrong right also , i am getting answer like below, sh test1.sh control.txt sample.csv ,,Provider contract idbalance ,End Date ,,0000000000000000014112 ,,00000000000000009349v-13 ,,00000000000000010477Nov-13 – William R Sep 18 '14 at 10:37
  • another thing: check if there is really a ascii comma in your file: The one between 16-Nov-13 and ​23-Nov-1 is not! it's hexdump is c32c c2a2 c280 c38c c2a2 c280 0a8b – Sebastian Sep 18 '14 at 11:23
0

the awk file

BEGIN { sp="-1" ;  FS="," ; }
FILENAME == "control.txt" { for (i=1;i<=NF;i++) col[i]=$i ;  next ;}
FILENAME != "control.txt" {
    comma="" ;
    for (i=1;i<NF;i++) {
            c=col[i] ;
            if (col[i]!=-1) printf "%s%s",comma,$c ;
            else printf "%s%s",comma,sp ;
            comma="," ;
    }
    printf "\n" ;
    sp="" ;
    }

the run

 awk -f a.awk control.txt  sample.txt
 col1,col3,col5,-1,col8,-1,col4
 1,3,5,,8,,4
 9,11,13,,16,,12

basically, first line in control.txt get wich column to print.

|improve this answer|||||
0
echo '1,2,3,4,5,6,7,8' |
sed 's/[^,]*//7;s///6;s///2;s/,,/,/
    s/\([^,]*\),\([^,]*\),,.*,\(.*\)/\2,,\3,,\1/'

That removes the contents of fields 7,6,2. Next it removes the first blank field. Then it swaps the last three nonempty fields like 2,,3,,1.

OUTPUT

1,3,5,,8,,4

And run on your sample data:

BP ID,CurrentMonetary balance ,Provider contract id,,End Date,,charge Plan names 
1100001538,251,00000000000000000141,,18-Oct-12,,[B2] R2 LTE CHARGE PLAN 
1100003404,45.22,00000000000000009349,,23-Nov-13,,B0.3 ECS_CHARGE_PLAN DROP1 V3

Then again, as long as you can verify your delimiter, you can just use the shell:

set -f; IFS=,
set -- $(cat file; echo "$IFS")
while [ $# -gt 8 ]
do printf %s\\n "$1,$3,$5,,$8,,$4"
   shift "$(($#>7?8:$#))"
done

With the shell option the fields can contain anything but the delimiter. As long as you have a delimiter that is other than <space><tab><newline> empty fields will be preserved. Any characters they contain - to include any of those three whitespace characters - are preserved. Here I use a comma. You need only enough memory to fit the file.

|improve this answer|||||
  • wow :) yes, this matches my output. btw, if you use sed -r you can avoid some backslashes,e.g. \( \) becomes (). However, I doubt that this makes your regex more readable ;-) sed -r 's/[^,]*//7;s///6;s///2;s/,,/,/;s/([^,]*),([^,]*),,.*,(.*)/\2,,\3,,\1/' input.csv – Sebastian Sep 18 '14 at 10:06
  • @Sebastian - dont use sed -r - if you must use -E - it will soon be POSIX i think. but if the cost is a couple of backslashes typed and the purchase is the script working or not working... i'll pay the backslashes. know what i mean? and its pretty readable - remove fields 7 6 2, remove first blank field, swap last nonempty three fields - 2 3 1 – mikeserv Sep 18 '14 at 10:08
  • point accepted. – Sebastian Sep 18 '14 at 10:10
  • @Sebastian but, to be fair, there is a lot of \\\///,,,((())) 8 in between. It reads like a lunch menu to me now, but i remember what it used to look like too. – mikeserv Sep 18 '14 at 10:17
  • Will it works for any file ? means any number of fields ? – William R Sep 18 '14 at 10:24

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.