Rearranging column/values in csv file based on input column order

i need your help in unix/awk to order the data in csv file (comma separated file) to certain order given in another file. my knowledge is limited on this.

example: File to rearrange.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

PRODUCTCODE	SITE	BATCHID	LV1P_DESCRIPTION
PRD-0989	Static	BATCH01	Estract 
PRD-0990	Static	BATCH02	Xstract 
PRD-0991	Static	BATCH03	8stract 
PRD-0992	Static	BATCH04	errs
PRD-0993	Static	BATCH05	dgerrr
PRD-0994	Static	BATCH06	Narros
PRD-0995	Static	BATCH07	sef3r

<!-- end snippet -->

and if pass input column order as [This could be stored in another file]

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

PRODUCTCODE,LV1P_DESCRIPTION,BATCHID,SITE

<!-- end snippet -->

then my output file should come as below

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

PRODUCTCODE	LV1P_DESCRIPTION	BATCHID	SITE
PRD-0989	Estract 	BATCH01	Static
PRD-0990	Xstract 	BATCH02	Static
PRD-0991	8stract 	BATCH03	Static
PRD-0992	errs	BATCH04	Static
PRD-0993	dgerrr	BATCH05	Static
PRD-0994	Narros	BATCH06	Static
PRD-0995	sef3r	BATCH07	Static

<!-- end snippet -->

Solution in TXR Lisp:

$ txr get.tl columns data
PRODUCTCODE LV1P_DESCRIPTION BATCHID SITE
PRD-0989 Estract BATCH01 Static
PRD-0990 Xstract BATCH02 Static
PRD-0991 8stract BATCH03 Static
PRD-0992 errs BATCH04 Static
PRD-0993 dgerrr BATCH05 Static
PRD-0994 Narros BATCH06 Static
PRD-0995 sef3r BATCH07 Static

Files look like:

$ cat columns
PRODUCTCODE,LV1P_DESCRIPTION,BATCHID,SITE
$ cat data
PRODUCTCODE SITE    BATCHID LV1P_DESCRIPTION
PRD-0989    Static  BATCH01 Estract
PRD-0990    Static  BATCH02 Xstract
PRD-0991    Static  BATCH03 8stract
PRD-0992    Static  BATCH04 errs
PRD-0993    Static  BATCH05 dgerrr
PRD-0994    Static  BATCH06 Narros
PRD-0995    Static  BATCH07 sef3r

Code in get.tl:

(match (@colfile @datafile) *args*
  (awk
    (:inputs datafile)
    (:let col-indices)
    ((= nr 1) (let ((wanted-cols (flow colfile
                                   file-get-lines first (spl ",")))
                    (col-index-hash (hash-zip f 0)))
                (set col-indices (mapcar col-index-hash wanted-cols))))
    (t [apply prn (mapcar f col-indices)])))

It looks as if the data might have been tab separated, and the tabs got converted to spaces, with a tab width of four. To output tab-separated, we can add a (:set ofs "\t") clause to the awk macro.

If:

• your CSV is simple (no quoted fields containing commas, one line per record...),
• and each record has exactly the same number of fields,
• and you specify the desired output order with exactly the same number of fields,

you can try (with any POSIX awk, tested with GNU awk and the awk that comes with macOS, output prefixed with -| ):

awk -F, -v OFS=, -v o='PRODUCTCODE,LV1P_DESCRIPTION,BATCHID,SITE' '
  BEGIN {n=split(o,h)}
  NR==1 {print o; for(i=1;i<=n;i++) a[$i]=i; next}
  {for(i=1;i<=n;i++) printf("%s%s", $(a[h[i]]), i==n?"\n":OFS)}
' foo.csv
-| PRODUCTCODE,LV1P_DESCRIPTION,BATCHID,SITE
-| PRD-0989,Estract,BATCH01,Static
-| PRD-0990,Xstract,BATCH02,Static
-| PRD-0991,8stract,BATCH03,Static
-| PRD-0992,errs,BATCH04,Static
-| PRD-0993,dgerrr,BATCH05,Static
-| PRD-0994,Narros,BATCH06,Static
-| PRD-0995,sef3r,BATCH07,Static

1. Set comma as input and output field separators (-F, -v OFS=,).
2. Pass desired output order as variable o (-v o='...').
3. In BEGIN block split variable o on input fields separator, store the result in array h and the number of columns in variable n.
4. For the first line store in array a the fields as keys and the field numbers as values (a[$i]=i). Go to next line (next).
5. For the other lines print the n first fields in desired order ($(a[h[i]])), followed by the output field separator except the last that is followed by a newline.

If the desired output order is stored in the first line of another file, say bar.csv:

awk -F, -v OFS=, '
  NR==1 {o=$0; n=split(o,h); next}
  NR!= FNR && FNR==1 {print o; for(i=1;i<=n;i++) a[$i]=i; next}
  {for(i=1;i<=n;i++) printf("%s%s", $(a[h[i]]), i==n?"\n":OFS)}
' bar.csv foo.csv

1. NR==1 is true only for the first line of the first file.
2. NR!= FNR && FNR==1 is true only for the first line of the second file.

$ head *file
==> input_file <==
PRODUCTCODE SITE    BATCHID LV1P_DESCRIPTION
PRD-0989    Static  BATCH01 Estract
PRD-0990    Static  BATCH02 Xstract
PRD-0991    Static  BATCH03 8stract
PRD-0992    Static  BATCH04 errs
PRD-0993    Static  BATCH05 dgerrr
PRD-0994    Static  BATCH06 Narros
PRD-0995    Static  BATCH07 sef3r

==> order_file <==
PRODUCTCODE,LV1P_DESCRIPTION,BATCHID,SITE

awk -F' *|,' '
    NR==FNR{
        for(i=1;i<=NF;i++) a[$i] = i
        next
    }
    FNR==1{
        for(i=1;i<=NF;i++) b[i] = a[$i]
    }
    {
        for (i=1;i<=length(b);i++) printf " %s", $(b[i])
        print ""
    }
 ' order_file input_file | column -t 

PRODUCTCODE  LV1P_DESCRIPTION  BATCHID  SITE
PRD-0989     Estract           BATCH01  Static
PRD-0990     Xstract           BATCH02  Static
PRD-0991     8stract           BATCH03  Static
PRD-0992     errs              BATCH04  Static
PRD-0993     dgerrr            BATCH05  Static
PRD-0994     Narros            BATCH06  Static
PRD-0995     sef3r             BATCH07  Static