Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need to modify csv-file with bash script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to modify csv-file with bash script
# 1  
Old 02-11-2010
Need to modify csv-file with bash script
Hi Guys,

I need to write a script, that exports the "moz_places" table of the "places.sqlite"-file (firefox browser history) into a csv-file. That part works. After the export, my csv looks like this:

Code:
...
4429;http://www.sqlite.org/sqlite.html;"Command Line Shell For SQLite";gro.etilqs.www.;3;0;0;98;410;1265905218764118
4562;https://www.unix.com/shell-programming-scripting/127699-question-about-function-calls.html#post302386693;"Question about Function calls - The UNIX and Linux Forums";moc.xinu.www.;1;0;0;182;47;1263646391422534
...

Now I want to extract just the visited URL and the date of this visit for all visits.

I did this like this:

Code:
awk -F ';' '{print $10, $2}' temp.csv

this works and the result looks like this

Code:
1263646291226002 https://www.unix.com/cfmgoogle.php?cx=partner-pub-6323928554267084%3Absye1r5tx7j&cof=FORID%3A10&q=xml&sa=Grep&siteurl=www.unix.com%2F
1263646297314065 https://www.unix.com/cfmgoogle.php?cx=partner-pub-6323928554267084%3Absye1r5tx7j&cof=FORID%3A10&q=xml&sa=Grep&siteurl=www.unix.com%2F#1333
1263646362729683 https://www.unix.com/cfmgoogle.php?cx=partner-pub-6323928554267084%3Absye1r5tx7j&cof=FORID%3A10&q=xml+shell&sa=Grep&siteurl=www.unix.com%252F

Okay, and here's my problem. I don't need a timestamp in front of the visited URL, I need a date. It should look somehow like this:

Code:
...
Do 11. Feb 19:00:23 CET 2010 www.google.com
...

(Sorry, my date is in german)

I know that I have the reduce the timestamp which I get of firefox to the first 10 characters, because the rest doesn't belong to the date. I already found out how to do this with awk:

Code:
awk -F ';' '{print((substr($10,1,10)))}' < temp.csv

but how can I convert this

Code:
1265911223 www.google.com

into this

Code:
Do 11. Feb 19:00:23 CET 2010 www.google.com

I know how I can convert one single date in commandline

Code:
date -d @1265911223

but how can i do this for the whole csv. I tried to store the timestamp into a variable, but i don't know how to do this for line after line. As I tried, it stored all timestamps in a long sting in the variable. I thought it would be easier to do this... Smilie Maybe someone of you can helpSmilie

Greetings

Sebi
# 2  
Old 02-11-2010
Code:
echo "1265911223 www.google.com"|awk '{str="date -d @"$1; print system(str)}'

# 3  
Old 02-12-2010
Hey,

thanks for the answer. That works, for one single line and for a right formated "timestamp". I think, my document has about 2000 lines, so how can I transfer your idea to my document? And another problem is the " timestamp. Firefox stores the last_visit_date-entry in a string which looks like this

Code:
1263818137802359

but the timestamp is only the first 10 characters of this sting

Code:
1263818137

I don't know what the other 6 characters are for, and that does not matter for me at the moment. So, I know that I can reduce the string to the first 10 characters with the substr command, for example

Code:
substr($1,1,10)

which will do exactly what I discribed. The Problem ist the csv, that you can see in the "Question-post" at the beginning of this thread. I export a sqlite-database to csv. This csv, hast 10 values, separated by ";" in each line. But I only need the second and 10th value of each line. The 10th value is this (somehow) strange timestamp, that must be reduced to the first ten characters and changed into a real date. After all I want to store the date followed by the visited URL in a new document that should look like this

Code:
"date" "URL"
"another date" "another URL"
...
...
...

How can I do this?SmilieSmilieSmilie
# 4  
Old 02-12-2010
Try this:
Code:
awk '{
  "date -d @ "substr($10,1,10) | getline date
  print date, $2
}' temp.csv

# 5  
Old 02-12-2010
What about this:
Code:
$ sqlite3 places.sqlite << EOF | while read datum url; do echo $(date -d @$datum) $url; done
> .header off
> .mode csv
> .separator " "
> select substr(h.visit_date,0,10),p.url from moz_places as p, moz_historyvisits as h \
> where p.id=h.place_id order by h.visit_date limit 10;
> EOF

If it works as intended, remove the line limit, and save the SQL statements to a file, sourcing that instead.
# 6  
Old 02-12-2010
@Franklin52

Thanks for this fast answer,

but I get a very long error message, which has something to do with the "date" command. At the beginning it says

Code:
sh: Syntax error: Unterminated quoted string

sh: Syntax error: Unterminated quoted string


The rest of the message is in german, so I will try to translate one of the error massages (they have all the same content, just the argument is different):
Code:
date: The argument ""1263646291"" hasn't got the leading "+".
When an option is given, to specify the date, every argument, that isn't an option, must be an formatstring, that begins with an "+".
"date --help" gives more Information.

Hope you can understand this translation... Smilie
# 7  
Old 02-12-2010
Quote:
Originally Posted by Sebi0815
@Franklin52

Thanks for this fast answer,

but I get a very long error message, which has something to do with the "date" command. At the beginning it says

Code:
sh: Syntax error: Unterminated quoted string

sh: Syntax error: Unterminated quoted string


The rest of the message is in german, so I will try to translate one of the error massages (they have all the same content, just the argument is different):
Code:
date: The argument ""1263646291"" hasn't got the leading "+".
When an option is given, to specify the date, every argument, that isn't an option, must be an formatstring, that begins with an "+".
"date --help" gives more Information.

Hope you can understand this translation... Smilie
Sorry, I forgot the fieldseparator:
Code:
awk -F ";" '{
  "date -d @ "substr($10,1,10) | getline date
  print date, $2
}' temp.csv


BTW: Did you get the correct output with the command provided by linuxpenguin?
Code:
echo "1265911223 www.google.com"|awk '{str="date -d @"$1; print system(str)}'

Last edited by Franklin52; 02-12-2010 at 07:04 AM.. Reason: Adding question
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Sed, awk or another bash command to modify string with the content of another file

Hello everybody, I would like modify some strings using sed or another command line with the content file. For example: - {fqdn: "server-01" , ip: "server-01"} - {fqdn: "server-02" , ip: "server-02"} - {fqdn: "server-03" , ip: "server-03"} - {fqdn: "server-04" , ip: "server-04"} My... (4 Replies)
Discussion started by: dco
4 Replies

2. Shell Programming and Scripting

Modify csv-files with awk

Hello everyone! I have thousands of csv files I have to import into a Database table. As usually the files aren't perfect. For example they have a different number of columns and some weird columns. The second problem is, that I have to add 3 parts of the filename into 3 rows in the... (6 Replies)
Discussion started by: elRonaldo
6 Replies

3. Shell Programming and Scripting

Modify CSV file

Hi, I would like to change my CSV file by adding " and : and moving some of the information around. the CSV file looks as follows: 501254424;500440257;PE PACKS;300467279;PREP;;276476070;655031001867176;Two Block;Olga;25/12/2015 00:00:00;Olga I would like to move the field 7 to the front "... (13 Replies)
Discussion started by: omuhans123
13 Replies

4. Shell Programming and Scripting

Csv download in a bash script

I am attempting to download a url in csv format. When I download this url in a browser excel opens up and automatically populates with comma separated values. When I try to use curl or wget I get nothing or garbage. This on the command line just hangs wget -b... (2 Replies)
Discussion started by: bash_in_my_head
2 Replies

5. Shell Programming and Scripting

Perl script to modify csv file

Hi Friends, I want to convert a csv file into a ordinary .txt file. I am able to convert but I want the output to look as shown below in the .txt file table findhost= { {"xyz","abc"}, {"rxz","mmz"}, {"vrr","nnz"}, } default={"NONE"} My current perl script #!/usr/bin/env perl... (12 Replies)
Discussion started by: dbashyam
12 Replies

6. Shell Programming and Scripting

Bash script help - removing certain rows from .csv file

Hello Everyone, I am trying to find a way to take a .csv file with 7 columns and a ton of rows (over 600,000) and remove the entire row if the cell in forth column is blank. Just to give you a little background on why I am doing this (just in case there is an easier way), I am pulling... (3 Replies)
Discussion started by: MrTuxor
3 Replies

7. Shell Programming and Scripting

Calling Pl/sql function in shell script to modify csv

I need to 1.Open a csv 2.Process the csv i.e. Modify 2 column in the csv. To modify the column the value needs to be passed to a pl/sql function and the return value should be updated For eg: If column 2 E,then E will be passed in database function which will return Employee. 3. Write a... (5 Replies)
Discussion started by: Chinky23
5 Replies

8. Shell Programming and Scripting

Read data from .csv file through shell script & modify

I need to read data from a file called "test.csv" through shell script where the file contains values like name,price,descriptor etc. There are rows where descriptor (& in some rows name) are written as string & other characters like "car_+" OR "bike*" etc where it should contains strings like... (3 Replies)
Discussion started by: raj100
3 Replies

9. Shell Programming and Scripting

Bash script to reorder csv

hi guys, im fairly new to unix and bash scripts and therefore your help would really be appreciated. i need to write a bash script that will take a csv file, and reorder the data and output to another csv file. The source csv file will look something like this: HEAD,671061,Add,SS... (3 Replies)
Discussion started by: daz_20
3 Replies

10. Shell Programming and Scripting

Bash Script: modify bash

Hey guys, i'm having trouble complete one of my bash scripts I'm hoping to --- 1. Modify bash so that then the user types "ls" the command that is executed is "ls -al" 2. Modify the point of entry in bash when the user accesses it, moving the initial location to /var I've somewhat done #2,... (9 Replies)
Discussion started by: LibRid
9 Replies
Login or Register to Ask a Question