Text to CSV


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Text to CSV
# 1  
Old 09-03-2009
Text to CSV

Hi,
My access log looks like this...

Code:
 192.168.50.184 - - [31/Aug/2009:11:56:59 +0530] "GET /ATIM_LATEST/ABC/ HTTP/1.1" 200 522 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13" 
192.168.50.184 - - [31/Aug/2009:11:56:59 +0530] "GET /ATIM_LATEST/ABC/ATIM/syncdepot.php HTTP/1.1" 200 1463 "http://192.168.50.209/ATIM_LATEST/ABC/" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13"
192.168.50.184 - - [31/Aug/2009:11:56:59 +0530] "GET /ATIM_LATEST/ABC/js/ajax.js HTTP/1.1" 200 2623 "http://192.168.50.209/ATIM_LATEST/ABC/ATIM/syncdepot.php" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13"

I want to insert the data in MySQL
1) Is there any script that will do it automatically.
2) If no, then How do I convert the format to either comma separated text file or fields delimited by " "?

For e.g.

Code:
 192.168.50.184, -, -, [31/Aug/2009:11:56:59 +0530], "GET /ATIM_LATEST/ABC/ATIM/syncdepot.php HTTP/1.1", 200, 1463, "http://192.168.50.209/ATIM_LATEST/ABC/", "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13"

OR

Code:
 "192.168.50.184" "-" "-" "[31/Aug/2009:11:56:59 +0530]" "GET /ATIM_LATEST/ABC/ATIM/syncdepot.php HTTP/1.1" "200" "1463" "http://192.168.50.209/ATIM_LATEST/ABC/" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13"

# 2  
Old 09-03-2009
It looks like an http apache access log. Wouldn't it be easier to change the log format and to use another character than space as separator?

Look for the CustomLog and LogFormat directive in the apache.conf config file.
Log Files - Apache HTTP Server

You can use tab as separator. It is easy to parse and to read on a console.

Have also a look at mod_log_sql module for apache
http://freshmeat.net/projects/mod_log_sql/
# 3  
Old 09-03-2009
Why not cut the middle man: mod_log_sql
# 4  
Old 09-03-2009
I have changed the LogFormat of httpd
But the %t display the date in the following format.

[31/Aug/2009:11:56:59 +0530]


MySQL does not understand this format and needs to be formatted something lie this...
2009-09-03 12:58:43

I need to change the %{format}t But I am not sure about the syntax. Any help?
# 5  
Old 09-03-2009
%{format}t uses the same operators as the PHP strftime function which, in turn, is similar to the usual format options for date:

Code:
LogFormat "%h %l %u %{[%Y-%m-%d %H:%M:%S %z]}t \"%r\" %>s %b" MyLogFormat

Why don't you use the mod_log_sql module as suggested above?
# 6  
Old 09-10-2009
MySQL Correct syntax

Thanks a lot for that syntax. I requested the change and I am now getting the correct results.
I can not use the module because I have only the access_log text files. Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert text to csv

Hi, Is there somebody there to post an idea on how to convert this 5 liner row to 1 liner or tab delimiter to be import to database. Here the text file format: Description: Description1 Link: https://www.google.com Date: June 2, 2018 Time: 00:07:44 Age: 1 days ago Description:... (2 Replies)
Discussion started by: lxdorney
2 Replies

2. Shell Programming and Scripting

CSV from Text file

Gents, Attached there is a input.txt and code.txt file. I use that code to create a csv file from txt file. Is there the possibility to generate other code more faster to generate the CVS file using the input file. I have deleted many data in the input file to avoid load a lot data. ... (10 Replies)
Discussion started by: jiam912
10 Replies

3. Shell Programming and Scripting

Create csv from text file

Gents, I am trying to create a csv file using the file attached. I have a problem to get all information required because the rows are not continues. Here is my code till now. awk ' /"ffid"/{if(s){print s;s=$NF}else{s=$NF}} /"LineNumber"/{s=s $NF} /"PointNumber"/{s=s $NF}... (4 Replies)
Discussion started by: jiam912
4 Replies

4. Shell Programming and Scripting

TEXT to CSV using Perl

Hi Folks Need some help with this and my Perl isnt the hottest I also have text::csv installed on my perl install The large text with a few million entries is in a format below example text file Fig Leafs Cake No: 0000001 Author: King s. Record No: 995-34343-232-232... (5 Replies)
Discussion started by: messiah1
5 Replies

5. Shell Programming and Scripting

Convert text to CSV

Hi Gurus I need urgent help to convert a flat log file into csv format to load into database. Log looks like: a=1 b=2 c=3 a=4 b=5 c=6 Only the values at right side of = will come into csv and it should create a new line once it receives "a" field. (8 Replies)
Discussion started by: sandipjee
8 Replies

6. Shell Programming and Scripting

text to csv conversion

Thank u every body ......just need a help so that a text file needs to be converted into CSV............. my log file is as follows Host scsi3: usb-storage Vendor: Maxtor Product: OneTouch III Serial Number: 044303E5 Protocol: Transparent SCSI Transport: Bulk ... (4 Replies)
Discussion started by: tangotango
4 Replies

7. Shell Programming and Scripting

Can't use Text::CSV... any sample code out there?

I was looking at a library that lets me parse comma delimited csv files without a problem, but unfortunately, I'm not allowed to make use of that library. I understand that the library probably does something similar to a string split(), but I don't quite know what kind of regular expression is... (1 Reply)
Discussion started by: mrwatkin
1 Replies

8. Shell Programming and Scripting

Text to csv

Hi, I have a document with a lot of data, it is structured like this, UNIQUESTRING To be acquited of a crime is to be deemed to be innocent of the charges after a court hearing. This is different from a <a href=lawglos_Discharge.html>Discharge</a>, where the case is never heard. In... (1 Reply)
Discussion started by: lawstudent
1 Replies

9. UNIX for Advanced & Expert Users

how to convert text/csv to excel

Hello All, I have a sql report with 50 columns and 1000 rows result in a file ( txt / csv). is there is any way that we can move them to excel in KSH. Thanks, Sateesh (7 Replies)
Discussion started by: kotasateesh
7 Replies

10. Shell Programming and Scripting

converting text to csv format

I am trying to check each line and based on first two digits, the comma needs to be place. I checked in the earlier post where the text is converted to csv with a tab delimited. Here is the test file that needs to be changed to csv 11 051701 22 051701 330123405170105170112345... (13 Replies)
Discussion started by: gthokala
13 Replies
Login or Register to Ask a Question