Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Save output of updated csv file as csv file itself

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Save output of updated csv file as csv file itself
# 1  
Old 03-30-2015
Save output of updated csv file as csv file itself
Hi, all

I want to sort a csv file based on timestamp from oldest to newest and save the output as csv file itself. Here is an example of my csv file.

test.csv
Code:
SourceFile,DateTimeOriginal
/home/intannf/foto/IMG_0739.JPG,2015:02:17 11:32:21
/home/intannf/foto/IMG_0749.JPG,2015:02:17 11:37:28
/home/intannf/foto/IMG_0759.JPG,2015:02:17 11:49:09
/home/intannf/foto/IMG_0771.JPG,2015:02:17 11:51:22
/home/intannf/foto/IMG_0745.JPG,2015:02:17 11:36:01
/home/intannf/foto/IMG_0763.JPG,2015:02:17 11:49:45
/home/intannf/foto/IMG_0666 (1).JPG,2015:02:17 09:59:48
/home/intannf/foto/IMG_0782.JPG,2015:02:17 11:54:16
/home/intannf/foto/IMG_0750.JPG,2015:02:17 11:37:29
/home/intannf/foto/IMG_0762.JPG,2015:02:17 11:49:24
/home/intannf/foto/IMG_0752.JPG,2015:02:17 11:37:40
/home/intannf/foto/IMG_0684.JPG,2015:02:17 10:54:51
/home/intannf/foto/IMG_0687.JPG,2015:02:17 10:56:43
/home/intannf/foto/IMG_0777.JPG,2015:02:17 11:53:43
/home/intannf/foto/IMG_0756.JPG,2015:02:17 11:45:21
/home/intannf/foto/IMG_0746.JPG,2015:02:17 11:37:18
/home/intannf/foto/IMG_0743.JPG,2015:02:17 11:34:26

Output required
Code:
SourceFile,DateTimeOriginal
/home/intannf/foto/IMG_0666 (1).JPG,2015:02:17 09:59:48
/home/intannf/foto/IMG_0684.JPG,2015:02:17 10:54:51
/home/intannf/foto/IMG_0687.JPG,2015:02:17 10:56:43
/home/intannf/foto/IMG_0739.JPG,2015:02:17 11:32:21
/home/intannf/foto/IMG_0743.JPG,2015:02:17 11:34:26
/home/intannf/foto/IMG_0745.JPG,2015:02:17 11:36:01
/home/intannf/foto/IMG_0746.JPG,2015:02:17 11:37:18
/home/intannf/foto/IMG_0749.JPG,2015:02:17 11:37:28
/home/intannf/foto/IMG_0750.JPG,2015:02:17 11:37:29
/home/intannf/foto/IMG_0752.JPG,2015:02:17 11:37:40
/home/intannf/foto/IMG_0756.JPG,2015:02:17 11:45:21
/home/intannf/foto/IMG_0759.JPG,2015:02:17 11:49:09
/home/intannf/foto/IMG_0762.JPG,2015:02:17 11:49:24
/home/intannf/foto/IMG_0763.JPG,2015:02:17 11:49:45
/home/intannf/foto/IMG_0771.JPG,2015:02:17 11:51:22
/home/intannf/foto/IMG_0777.JPG,2015:02:17 11:53:43
/home/intannf/foto/IMG_0782.JPG,2015:02:17 11:54:16

and this is my script:
Code:
sort --field-separator=',' --key=2 -n test.csv > test.csv

Unfortunately, when i checked them out, there is no data in the test.csv. It is blank. Or it should be saved as another csv file? But i need to save it as csv file itself.

Please help me how to solve it. Thanks in advance.

Regards,
Intan
# 2  
Old 03-30-2015
If you read from a file and write to the same file in one operation, you'll end up corrupting the file. Work-around is to write to a temp file and then rename the temp file.
Code:
sort .... test.csv > temp
mv temp test.csv

This User Gave Thanks to balajesuri For This Post:
refrain
# 3  
Old 03-30-2015
The sort utility actually has an option to let you put the output in one of the input files... Try:
Code:
sort --field-separator=',' --key=2 -n -o test.csv test.csv

Note that since : is not a numeric character, using the -n option to sort means you are only sorting on the year in the 2nd field with your secondary sort key being the filename. If you want the month, day, hour, minute, and second portions of the 2nd field on these lines to participate in your primary sort key; remove the -n option.
This User Gave Thanks to Don Cragun For This Post:
refrain
# 4  
Old 03-30-2015
or:
Code:
awk -F, '{t=$2;gsub("[^0-9]","",t);print t, $0}' OFS=, test.csv | sort -n -k1,1 | cut -d, -f2-

This User Gave Thanks to vgersh99 For This Post:
refrain
# 5  
Old 03-30-2015
Quote:
Originally Posted by vgersh99
or:
Code:
awk -F, '{t=$2;gsub("[^0-9]","",t);print t, $0}' OFS=, test.csv | sort -n -k1,1 | cut -d, -f2-

This is extremely complicated for data that is in a form where a string comparison will work just fine (and, it doesn't solve the problem of redirecting the output wiping out the input). Just using standard sort utility options, the following does everything that was requested:
Code:
sort -k2,2 -o test.csv -t, test.csv

# 6  
Old 03-31-2015
Thanks for your helps, balajesuri, Don Cragun and vgersh99! Smilie

Now I can save my csv file as csv file itself. and personally, i like one line code because it looks simple and not complex. So, i prefer to use Don Cragun's code. I used this code:
Code:
sort --field-separator=',' --key=2 -n -o test.csv test.csv

Because when i tried to run this code:
Code:
sort -k2,2 -o test.csv -t, test.csv

the output that i got is like this:
Code:
/home/intannf/foto/IMG_0666 (1).JPG,2015:02:17 09:59:48
/home/intannf/foto/IMG_0684.JPG,2015:02:17 10:54:51
/home/intannf/foto/IMG_0687.JPG,2015:02:17 10:56:43
/home/intannf/foto/IMG_0739.JPG,2015:02:17 11:32:21
/home/intannf/foto/IMG_0743.JPG,2015:02:17 11:34:26
/home/intannf/foto/IMG_0745.JPG,2015:02:17 11:36:01
/home/intannf/foto/IMG_0746.JPG,2015:02:17 11:37:18
/home/intannf/foto/IMG_0749.JPG,2015:02:17 11:37:28
/home/intannf/foto/IMG_0750.JPG,2015:02:17 11:37:29
/home/intannf/foto/IMG_0752.JPG,2015:02:17 11:37:40
/home/intannf/foto/IMG_0756.JPG,2015:02:17 11:45:21
/home/intannf/foto/IMG_0759.JPG,2015:02:17 11:49:09
/home/intannf/foto/IMG_0762.JPG,2015:02:17 11:49:24
/home/intannf/foto/IMG_0763.JPG,2015:02:17 11:49:45
/home/intannf/foto/IMG_0771.JPG,2015:02:17 11:51:22
/home/intannf/foto/IMG_0777.JPG,2015:02:17 11:53:43
/home/intannf/foto/IMG_0782.JPG,2015:02:17 11:54:16
SourceFile,DateTimeOriginal

the header is written below the data. Would you tell me why it can be like that?
# 7  
Old 03-31-2015
Sorry, I didn't notice the heading line before. Doing a numeric sort, the string DateTimeOriginal evaluates to 0 and sorts before 2015; while doing an alphanumeric sort, numbers sort before letters. So, you need the -n option to make the heading come out first. If you use numeric sorting, you need to make the sort key more complex to use the each year, month, day, hour, minute, and second field separate keys to avoid just using the year as your sort key:
Code:
sort -n -k2.1,2.4 -k2.6,2.7 -k2.9,2.10 -k2.12,2.13 -k2.15,2.1 -k2.18,2.19 -o test.csv -t, test.csv

But with your data we can use numeric sorting for the year and use alphanumeric sorting for the month, day, hour, minute and second as a single field, which siimplifies the command to just:
Code:
sort -k2.1,2.4n -k2.6,2 -o test.csv -t, test.csv

Your relatively simple sort seems to work because the image numbers in your pathnames are monotonically increasing as the time stamp increases AND all of your image files are in a single directory. If your real image files could be in more than one directory, might have different numbers of digits in the sequence numbers, or might come from different cameras with different image ranges, one of the above sort commands should sort correctly by date and time for you.
This User Gave Thanks to Don Cragun For This Post:
refrain
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Save output of updated csv file as csv file itself, part 2

Hi, I have another problem. I want to sort another csv file by the first field. result.csv SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw /home/intannf/foto5/2015_0313_090651_219.JPG,0.,-7.77223,110.37310,30.75,996.46,148.75,180.94,182.00,63.92 ... (2 Replies)
Discussion started by: refrain
2 Replies

2. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

3. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

4. Shell Programming and Scripting

Match list of strings in File A and compare with File B, C and write to a output file in CSV format

Hi Friends, I'm a great fan of this forum... it has helped me tone my skills in shell scripting. I have a challenge here, which I'm sure you guys would help me in achieving... File A has a list of job ids and I need to compare this with the File B (*.log) and File C (extend *.log) and copy... (6 Replies)
Discussion started by: asnandhakumar
6 Replies

5. Shell Programming and Scripting

FILE_ID extraction from file name and save it in CSV file after looping through each folders

FILE_ID extraction from file name and save it in CSV file after looping through each folders My files are located in UNIX Server, i want to extract file_id and file_name from each file .and save it in a CSV file. How do I do that? I have folders in unix environment, directory structure is... (15 Replies)
Discussion started by: princetd001
15 Replies

6. Shell Programming and Scripting

need to save the space when converting to CSV file

Hi, I have a text file with the following format. Some of the fields are blank. 1234 3456 23 45464 327837283232 343434 5654353 34 34343 3434345 434242 .... .... .... I need to convert this file to a CSV file, like 1234, ,23, ... (3 Replies)
Discussion started by: wintersnow2011
3 Replies

7. UNIX for Dummies Questions & Answers

CSV file:Find duplicates, save original and duplicate records in a new file

Hi Unix gurus, Maybe it is too much to ask for but please take a moment and help me out. A very humble request to you gurus. I'm new to Unix and I have started learning Unix. I have this project which is way to advanced for me. File format: CSV file File has four columns with no header... (8 Replies)
Discussion started by: arvindosu
8 Replies

8. Shell Programming and Scripting

select data from oracle table and save the output as csv file

Hi I need to execute a select statement in a solaris environment with oracle database. The select statement returns number of rows of data. I need the data to be inserted into a CSV file with proper format. For that we normally use "You have to select all your columns as one big string,... (2 Replies)
Discussion started by: rdhanek
2 Replies

9. Shell Programming and Scripting

Data fetched from text file and save in a csv file

Hi i have wriiten a script which fetches the data from text file, and saves in the output in a text file itself, but i want that the output should save in different columns. I have the output like: For Channel:response_time__24.txt 1547 data points 0.339 0.299 0.448 0.581 7.380 ... (1 Reply)
Discussion started by: rohitkalia
1 Replies
Login or Register to Ask a Question