Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Text file -> fixed column -> MySQL DB

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Text file -> fixed column -> MySQL DB
# 1  
Old 02-03-2010
Text file -> fixed column -> MySQL DB
Hi !
I have searched in the forum, but i'm not able to combine various solution to help me !
My problem is very simple for a bash scripter or a old linux man, but not for me (novice and inexperienced).
I need to store in a mysql database the output of "last" command, but the table where i need to store the data have only few TEXT fields.
So, i must split the line in a few fixed fields, and store in my mysql db.
The output of "last" command
Code:
axel    pts/0        :0.0             Wed Feb  3 21:39   still logged in   
axel    tty7         :0               Wed Feb  3 21:39   still logged in   
mark    pts/2        :0.0             Wed Feb  3 21:37 - 21:38  (00:01)    
axel    tty7         :0               Wed Feb  3 21:34 - 21:38  (00:04)    
reboot  system boot  2.6.31-18-generi Wed Feb  3 21:33 - 00:23  (02:49)    
tucky   pts/0        :0.0             Wed Feb  3 21:18 - 21:33  (00:14)    
tucky   tty7         :0               Wed Feb  3 21:16 - down   (00:16)    
reboot  system boot  2.6.31-18-generi Wed Feb  3 21:15 - 21:33  (00:17)

so I decided to split this way:
For "still logged":
Code:
axel,pts/0,:0.0,Wed Feb  3 21:39,still logged in

For "normal":
Code:
mark,pts/2,:0.0,Wed Feb  3 21:37 - 21:38,(00:01)

For "reboot or shutdown":
Code:
reboot,system boot,2.6.31-18-generi,Wed Feb  3 21:15 - 21:33,(00:17)

In this way I need only 5 fields..

Someone can help me to make this bash shell script ?

Many thanks in advance..
Axel
# 2  
Old 02-03-2010
resolve for your first question.

Code:
awk '/still logged in/ {printf "%s,%s,%s,%s %s %s %s,%s %s %s\n", $1,$2,$3,$3,$5,$6,$7,$8,$9,$10; next}' urfile

For rest one, just add more reqeust, such as:

Code:
awk '/still logged in/ {printf "format", items; next}
       /reboot/ {printf "format", items; next}
       {printf "format", items}' urfile

# 3  
Old 02-04-2010
Hi rdcwayx , many thanks for your response..

i'm trying, but the first command produce a bad output..
Code:
axel,pts/0,:0.0,:0.0 Wed Feb 3 21:39,still logged in

(duplicate :0.0 )

where am I wrong?

---------- Post updated at 10:49 AM ---------- Previous update was at 09:52 AM ----------

Oh !!
for the first command i have see..
Code:
awk '/still logged in/ {printf "%s,%s,%s,%s %s %s %s,%s %s %s\n", $1,$2,$3,$3,$5,$6,$7,$8,$9,$10; next}' urfile

..change the $3 in $4 all is ok..

..I still do not understand the syntax 's last command (how to use %s and $n)

---------- Post updated at 11:17 AM ---------- Previous update was at 10:49 AM ----------

After some tests I found this syntax.. :

Code:
awk '/still logged in/ {printf "%s,%s,%s,%s %s %s %s,%s %s %s\n", $1,$2,$3,$4,$5,$6,$7,$8,$9,$10; next}
       /reboot/ {printf "%s,%s %s,%s,%s %s %s %s %s %s\n", $1,$2,$3,$4,$5,$6,$7,$8,$9,$10; next}
       {printf "%s,%s,%s,%s %s %s %s %s %s %s\n", $1,$2,$3,$4,$5,$6,$7,$8,$9,$10}' /my/urfile

..and now how to import in mysql database ?
Last edited by axl936; 02-04-2010 at 05:55 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Parsing a fixed column text file in sed

I have a text file with records of the form: A X1 Y1 X2 Y2 X3 Y3 where A is character length 10, Xi is character length 4 and Yi is numeric length 10. I want to parse the line, and output records like: A X1 Y1 A X2 Y2 A X3 Y3 etc Can anyone please give me an idea of how to do this. ... (4 Replies)
Discussion started by: wvdeijk
4 Replies

2. Shell Programming and Scripting

Print column details from fixed width file using awk command

hi, i have a fixed width file with multiple columns and need to print data using awk command. i use: awk -F "|" '($5 == BH) {print $1,$2,$3}' <non_AIM target>.txt for a delimiter file. but now i have a fixed width file like below: 7518 8269511BH 20141224951050N8262 11148 8269511BH... (5 Replies)
Discussion started by: kcdg859
5 Replies

3. Shell Programming and Scripting

To replace the value of the column in a fixed width file

I have a fixed with file with header & trailer length having the same length of the detail record file. The details record length of this file is 24, for Header and Trailer the records will be padded with spaces to match the record length of the file Currently I am adding 3 spaces in header... (14 Replies)
Discussion started by: ginrkf
14 Replies

4. UNIX for Dummies Questions & Answers

Filling the empty columns in a fixed column file

Hi, I have a file with fixed number of columns (total 58 columns) delimeted by pipe (|). Due to a bug in the application the export file does not come with fixed number of columns. The missing data columns are being replaced by blank in the output file. In one line I can have 25 columns (33... (1 Reply)
Discussion started by: yale_work
1 Replies

5. Shell Programming and Scripting

How to split a fixed width text file into several ones based on a column value?

Hi, I have a fixed width text file without any header row. One of the columns contains a date in YYYYMMDD format. If the original file contains 3 dates, I want my shell script to split the file into 3 small files with data for each date. I am a newbie and need help doing this. (14 Replies)
Discussion started by: bhanja_trinanja
14 Replies

6. UNIX for Dummies Questions & Answers

Remove duplicates based on a column in fixed width file

Hi, How to output the duplicate record to another file. We say the record is duplicate based on a column whose position is from 2 and its length is 11 characters. The file is a fixed width file. ex of Record: DTYU12333567opert tjhi kkklTRG9012 The data in bold is the key on which... (1 Reply)
Discussion started by: Qwerty123
1 Replies

7. Shell Programming and Scripting

Changing one column of delimited file column to fixed width column

Hi, Iam new to unix. I have one input file . Input file : ID1~Name1~Place1 ID2~Name2~Place2 ID3~Name3~Place3 I need output such that only first column should change to fixed width column of 15 characters of length. Output File: ID1<<12 spaces>>Name1~Place1 ID2<<12... (5 Replies)
Discussion started by: manneni prakash
5 Replies

8. Shell Programming and Scripting

print a file with one column having fixed character length

Hi guys, I have tried to find a solution for this problem but couln't. If anyone of you have an Idea do help me. INPUT_FILE with three columns shown to be separated by - sign A5BNK723NVI - 1 - 294 A7QZM0VIT - 251 - 537 A7NU3411V - 245 - 527 I want an output file in which First column... (2 Replies)
Discussion started by: smriti_shridhar
2 Replies

9. Shell Programming and Scripting

Comparing column of variable length anf fixed width file

Hi, I have two input files. File1: ID Name Place 1-234~name1~Newyork 1-34~name2~Boston 1-2345~name3~Hungary File1 is a variable length file where each column is seperated by delimitter "~". File2: ID Country 1-34<<11 SPACES>>USA<<7 spaces>> 1-234<<10 SPACES>>UK<<8... (5 Replies)
Discussion started by: manneni prakash
5 Replies
Login or Register to Ask a Question