Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

CSV data format manipulation

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting CSV data format manipulation
# 1  
Old 09-17-2012
CSV data format manipulation
Hi There

I need a script which will pick up the data from a .CSV file and reformat it as per the requirement and write it to another .CSV file.

I am using an application that will only take data in a particular format and need something that will convert without manual intervention.

The original data is in sample 1.csv and final output should look like something in sample 2.csv

The very first line should start with a "ONE record," and should break after 9 commas. Then a "Child Record" should be created followed by the renaming characters from 9th comma on.

It should repeat as "Child record" from 9th comma on as long as the names (milani, DRS, etc) are the same. But if it encounters a new name, it should create a new "One Record" and the process should follow till it hits the end of the csv file.

Samples are attached

Appreciate all the help... Smilie
Macro Project.zip (1.5 KB) 
# 2  
Old 09-17-2012
The big problem around CSV is that it is considered to be a simple delimited format, like tab-separated-text, but it has quoting and quote escaping as well. Some older versions of MS Access botch CSV quote handling. If the only commas are separators and there are no quotes, you can be simple about it. The only way to handle it robustly is to convert it to some more easily dealt with sort of matrix, like a spreadsheet, database table or if it is free of tabs, tab separated text.

That being said, simple in shell is: You can change IFS in a subshell, and then it will 'read' the comma separated fields as if they were space separated: (IFS=, ; while read a b c d e f;do . . . done) You can process one file and write another.
# 3  
Old 09-17-2012
Thank you for the quick reply!

It is only separated by commas so I am hoping the complications are eliminated.
# 4  
Old 09-17-2012
You can get jdbc jars that read csv files as database tables, and work them with SQL.
# 5  
Old 09-17-2012
Code:
 
awk -f a.awk s1.csv

where a.awk:

Code:
 
{
 FS=",";
 if ($1 != lr) {
    printf "ONE Rec";
    for (i=1; i<=NF; i++) {
     printf "," $(i);
     if (i > 11) break;
    }
    print ",";
 }
 printf "Child Record";
 for (i=13; i<=NF; i++) {
  printf "," $(i);
 }
 print "";
 lr=$1;
}

using input file attached...

output:

Code:
ONE Rec,Milani,3501,REST,HINAM-FAST,HINAM & CO,222 Snooker GREEN STATION,Mumbai MA  10274,,,2,NY,13-2555119,
Child Record,4/17/2012,1.277,3/15/2012,57.08,59.04,0,,S,SHORT,Y,HINAM,4,10274
Child Record,6/26/2012,0.7321,6/15/2012,33.71,34.11,0,,S,SHORT,Y,HINAM,4,10274
Child Record,7/31/2012,0.3811,6/15/2012,18.41,17.77,0,,S,SHORT,Y,HUG J MCMENAMIN,2,23060-7241
Child Record,5/1/2012,13,,593.54,,0,,,,N,MAR J STRANGER,4,53963-2286
Child Record,3/27/2012,23,,"1,020.55",,0,,,,N,BAR KSHINAP,1,54457-9291
Child Record,2/14/2012,503.7475,,"22,918.40",,0,,,,N,EDW A DIGGS,1,46805-2652
ONE Rec,DRS,54029,55277PDRS,LARRY C KISSIME & SMUTTY S KISSIME TR,UA 04 02 03,LARRY C & SMUTTY S KISSIME LIV TRUST,442 NINJAY VALLEY RD,MOUNT HOREB WI  53572-3016,,,WI,XXX-XX-3555,
Child Record,6/19/2012,170,,"8,049.45",,0,,,,N,LAR C KISSIME,2,53572-3016
Child Record,5/15/2012,4.4864,,205.75,,0,,,,N,JUL NONO,1,07045-9563

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Log file data into CSV format

I m looking for help here!!! Can we filter the below log data into CSV format ? 1 2 3 4 5 6 7 8 timestamp INFO <text > - Some text (1 Reply)
Discussion started by: MohSalNiz
1 Replies

2. Shell Programming and Scripting

Can we filter the below log data into CSV format?

HI , I m looking for help here!!! Can we filter the below log data into CSV format ? timestamp INFO <text > - Some text Drive .. Need a format of separate field such as 1 2 3 4 ... (2 Replies)
Discussion started by: MohSalNiz
2 Replies

3. Shell Programming and Scripting

Convert csv data to html format

I am new to html and need to convert the attached csv file data to html format ; running into issues. please assist. #!/bin/ksh echo "<html>" ; echo "<head><style> table {border-collapse: collapse;} table, td, th {border: 1px solid black;} </style></head>" echo "<title> REPORT </title>" echo... (0 Replies)
Discussion started by: archana25
0 Replies

4. Shell Programming and Scripting

LDAP data in CSV format - Part II

User Aia had created this below script to help translate LDIF files to CSV format, and it works very well (thanks again Aia if you are reading this) Here is the original thread. https://www.unix.com/shell-programming-and-scripting/265753-ldap-data-csv-format.html?referrerid=302170129 However... (2 Replies)
Discussion started by: tfm217
2 Replies

5. Shell Programming and Scripting

LDAP data in CSV format

Hi all, I am new here, please don't eat me alive.. I am trying to find a good community to learn and participate in unix / linux discussions to help me improve in my current job. That being said, I have a problem which I didn't expect to challenge me, but I can't seem to find a viable... (8 Replies)
Discussion started by: tfm217
8 Replies

6. Shell Programming and Scripting

Data Manipulation on a .csv file

Hallo Friends, I need you help. My file has 5000 or so lines and currently looks like below(sample). Service Type,Origin,Destination,Rate Per Minute,Minimum Charge,Time Based Rate,Time Based From Day,Time Based To Day,Time Based From Time,Time Based To Time,Destination Prefix List,, VoIS... (3 Replies)
Discussion started by: kekanap
3 Replies

7. Shell Programming and Scripting

Conversion of xhtml data into csv format using dump utility

Hi Unix Gurus, I tried to convert the attached xhtml table content into csv file using unix shell script (lynx -dump filename) and got the below results: Title ID Owner Priority Estimate Project Change Date Changed By Complexity Create Date Created By Detail Estimate Total De tail... (6 Replies)
Discussion started by: bi.infa
6 Replies

8. Shell Programming and Scripting

Retaining the Unix CSV format in Excel format while exporting

Hi All, I have created a Unix Shell script whch creates a *.csv file and export it to Excel. The problem i am facing is that Users wants one of the AMOUNT field in comma separted values. Example : if the Amount has the value as 3000000 User wants to be in 3,000,000 format. This Amount format... (2 Replies)
Discussion started by: rawat_me01
2 Replies

9. UNIX for Advanced & Expert Users

shell script to format .CSV data

Hi all, I have written a shell script to search a specified directory (e.g. /home/user) for a list of specific words (shown as ${TMPDIR}/wordlist below). The script works well enough, but I was wondering if there was a way to display the line number that the word is found on? Thanks! cat... (1 Reply)
Discussion started by: tmcmurtr
1 Replies

10. Shell Programming and Scripting

Shell script to format a .CSV data

Hi There I needed to write a Unix shell script which will pick up the data from a .CSV file and reformat it as per the requirement and write it to another .CSV file. Currently I am in the proess of Data Import to "Remedy System" (A one kind of incident mangement Application) and this... (8 Replies)
Discussion started by: Uday1982
8 Replies
Login or Register to Ask a Question