Solved

sorting csv files

Posted on 2008-10-21
5
348 Views
Last Modified: 2013-12-27
I have a csv files which is produced dynamically
based on first record I need to sort the data and make different csvfiles

for example, in the file below,I need three different files with the records starting with
ACCNT_REVN_GROWTH, ACCNT_SURVEY_TYPE, ACCOUNT_ASSIGN_ROLE

ACCNT_REVN_GROWTH,5 - 10%,5 - 10%,English-American,,3,Y,Y,N,All
ACCNT_REVN_GROWTH,10 - 15%,10 - 15%,English-American,,4,Y,Y,N,All
ACCNT_REVN_GROWTH,15 - 20%,15 - 20%,English-American,,5,Y,Y,N,All
ACCNT_REVN_GROWTH,> 20%,>20%,English-American,,6,Y,Y,N,All
ACCNT_SURVEY_TYPE,1,1,English-American,,1,Y,Y,N,All
ACCNT_SURVEY_TYPE,2,2,English-American,,2,Y,Y,N,All
ACCNT_SURVEY_TYPE,3,3,English-American,,3,Y,Y,N,All
ACCNT_SURVEY_TYPE,4,4,English-American,,4,Y,Y,N,All
ACCOUNT_ASSIGN_ROLE,Primary,Primary,English-American,,1,Y,Y,N,All
ACCOUNT_ASSIGN_ROLE,Secondary,Secondary,English-American,,2,Y,Y,N,All
ACCOUNT_ASSIGN_ROLE,Tertiary,Tertiary,English-American,,3,Y,Y,N,All

exmple output

file1.csv

ACCNT_REVN_GROWTH,5 - 10%,5 - 10%,English-American,,3,Y,Y,N,All
ACCNT_REVN_GROWTH,10 - 15%,10 - 15%,English-American,,4,Y,Y,N,All
ACCNT_REVN_GROWTH,15 - 20%,15 - 20%,English-American,,5,Y,Y,N,All
ACCNT_REVN_GROWTH,> 20%,>20%,English-American,,6,Y,Y,N,All

file2.csv

ACCNT_SURVEY_TYPE,1,1,English-American,,1,Y,Y,N,All
ACCNT_SURVEY_TYPE,2,2,English-American,,2,Y,Y,N,All
ACCNT_SURVEY_TYPE,3,3,English-American,,3,Y,Y,N,All
ACCNT_SURVEY_TYPE,4,4,English-American,,4,Y,Y,N,All

file3.csv

ACCOUNT_ASSIGN_ROLE,Primary,Primary,English-American,,1,Y,Y,N,All
ACCOUNT_ASSIGN_ROLE,Secondary,Secondary,English-American,,2,Y,Y,N,All
ACCOUNT_ASSIGN_ROLE,Tertiary,Tertiary,English-American,,3,Y,Y,N,All
0
Comment
Question by:conversekid
  • 3
  • 2
5 Comments
 
LVL 20

Assisted Solution

by:edster9999
edster9999 earned 500 total points
Comment Utility
Here is a script that splits them and names the files after that first field :


#!/bin/bash
 

cat test1.txt |while read line; do

        filename=`echo $line | awk -F, '{print $1}'`

        echo "$line" >> $filename.csv

done

Open in new window

0
 
LVL 20

Assisted Solution

by:edster9999
edster9999 earned 500 total points
Comment Utility
$ ls *.csv
ACCNT_REVN_GROWTH.csv  
ACCNT_SURVEY_TYPE.csv  
ACCOUNT_ASSIGN_ROLE.csv


example output file :

$ cat ACCNT_REVN_GROWTH.csv
ACCNT_REVN_GROWTH,5 - 10%,5 - 10%,English-American,,3,Y,Y,N,All
ACCNT_REVN_GROWTH,10 - 15%,10 - 15%,English-American,,4,Y,Y,N,All
ACCNT_REVN_GROWTH,15 - 20%,15 - 20%,English-American,,5,Y,Y,N,All
ACCNT_REVN_GROWTH,> 20%,>20%,English-American,,6,Y,Y,N,All
0
 

Author Comment

by:conversekid
Comment Utility
Great! your expertise is really appreciated....Its working but I am getting -ambiguous redirect- ..Is there any way we can avoid it?
0
 
LVL 20

Accepted Solution

by:
edster9999 earned 500 total points
Comment Utility
I based it on the file you gave.  
Are there other lines or blank line or anything like that ?

you could add in an echo line to show each line so you can spot which ones give errors and then put them up here and I'll adjust the file to fit that.

#!/bin/bash

 

cat test1.txt |while read line; do

        echo "Testing - $line"

        filename=`echo $line | awk -F, '{print $1}'`

        echo "$line" >> $filename.csv

done

Open in new window

0
 

Author Comment

by:conversekid
Comment Utility
Hey Thats fine..As long as it works..Because the files are randomly generated everyday..
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Let's say you need to move the data of a file system from one partition to another. This generally involves dismounting the file system, backing it up to tapes, and restoring it to a new partition. You may also copy the file system from one place to…
Over the years I've spent many an hour playing on hardened, DMZ'd servers, with only a sub-set of the usual GNU toy's to keep me company; frequently I've needed to save and send log or data extracts from these server back to my PC, or to others, and…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now