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.
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:
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... Maybe someone of you can help
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
$ 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.
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.
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.
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)