Hi All,

i have a requirement where i need to format the input RAW file ( which is CSV) by using another mapping file(also CSV file). basically i am getting feed file with dynamic headers by using mapping file (in that target field is mapped with source filed) i have to convert the raw file into a standard format (target format).

currently i am using ETL tool to achieve this but it's taking long time.
i am trying to write generic shell script for this. could anyone help me in a high level view. i am new to scripting. the target file also be a CSV file.

thanks in advance

Ravi

Provide sample input and target files.

I suspect we need some idea of what the one csv file's table does to the other csv file. For instance, an associative array can deal with a two column translate. PERL might be a good choice, as it has CSV handlers. Shell can only deal with "well behaved" CSV, e.g., no quoted commas. PERL can draw on many resources to deal with your problem, can call compiled code and has much of the speed of compiled code.

Hi all,

Thanks for the prompt response. Below is the sample data for both files

mapping file:

Header: AU_Template_Field,RawFile_Field

Data:
VENDOR_NAME,VENDOR NAME
SHIP_TO_PARTY_NAME, PARTY_NAME
SHIP_TO_PARTY_ADDRESS1,PARTY_ADDRESS1
SHIP_TO_PARTY_ADDRESS2,PARTY_ADDRESS2
SHIP_TO_PARTY_CITY,PARTY_CITY
SHIP_TO_PARTY_STATE,PARTY_STATE
SHIP_TO_PARTY_ZIP,PARTY_ZIP

Raw File:

Header: VENDOR NAME,PARTY_NAME,PARTY_ADDRESS1,PARTY_ADDRESS2,PARTY_CITY,PARTY_STATE,PARTY_ZIP

data: abc,xyz,aaa,lny,london,london,abc123

output file:

header: VENDOR_NAME,SHIP_TO_PARTY_NAME,SHIP_TO_PARTY_ADDRESS1,SHIP_TO_PARTY_ADDRESS2,SHIP_TO_PARTY_CITY,SHIP _TO_PARTY_STATE,SHIP_TO_PARTY_ZIP

the out file strcture is standard but raw file structure might vary each time that the reason business is providing mapping file to map the columns for target file.

i hope this explanation would be clear to all.

awaiting for the responses.

Thanks in advance

Ravi

Put the input keys in a simple array, then for each record, put each field in an associative array keyed to the input name, then write the output using the unchanging ordered list of input names for each output field to fetch the fields from the associative array.

Hi,

Thanks for the prompt response. if you don't mind could you please provide an sample code for overview so that i can use to rewrite my script.

i am new to the scripting hence i am requesting to provide a sample snippet.

Thanks in advance.

Ravi

Bash, PERL, awk, c, c++, JAVA, ???