LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices

Reply
 
Search this Thread
Old 11-05-2009, 01:33 PM   #1
kenosando
LQ Newbie
 
Registered: Oct 2009
Distribution: Ubuntu 9.11
Posts: 10

Rep: Reputation: 0
Re-creating a column byte-based file


Greetings,
I searched the forums for what I am trying to accomplish, but it was hard describing it and getting any relevant results. Here is what I am trying to accomplish:

I have a file that stores employee login IDs, names, types, and permissions. Our software reads the information based on byte-columns, so it reads a column as any ASCII character (spaces, letters, numbers, punctuation, etc.). I want to create a web-interface for adding and removing users, and storing the data in a MySQL database. However, if I am creating the files from the MySQL output, I need a way to write to specific column locations in the file ...

User ID: Columns 1-4
User Name: Columns 6-30
Type: 32-40
Permissions: 42-45
I want to use a scripting language, preferably C-Shell, to call MySQL for the data and write the data to the correct columns of the file. I wrote a script that takes the data from the file, and dumps it into the MySQL table, so maybe I can pad the remaining space in the table column to fill with spaces ... any suggestions?
 
Old 11-05-2009, 01:57 PM   #2
MensaWater
Guru
 
Registered: May 2005
Location: Atlanta Georgia USA
Distribution: Redhat (RHEL), CentOS, Fedora, Debian, FreeBSD, HP-UX, Solaris, SCO
Posts: 5,937
Blog Entries: 5

Rep: Reputation: 752Reputation: 752Reputation: 752Reputation: 752Reputation: 752Reputation: 752Reputation: 752
You can use the "cut" command -c option to get positions.

The way I'd do extract the data in bash/ksh is:

Code:
while read line
do USER=`echo $line |cut -c1-4`
NAME=`echo $line |cut -c6-30`
TYPE=`echo $line |cut -c32-40`
PERMS=`echo $line |cut -c42-45`
echo User ID is $USER User Name is $NAME Type is $TYPE Permissions are $PERMS
done <FILE
Where FILE is the file that contains the original data.

Of course instead of just doing the final echo line you would want to put your routine that writes the data the way you want.

Last edited by MensaWater; 11-05-2009 at 01:58 PM.
 
Old 11-06-2009, 09:10 AM   #3
kenosando
LQ Newbie
 
Registered: Oct 2009
Distribution: Ubuntu 9.11
Posts: 10

Original Poster
Rep: Reputation: 0
That is what I used to get the data out of the file and into MySQL, using cut, but since I will need to re-write any changes made to the MySQL table back to this file, I need it formatted as before.
 
Old 11-06-2009, 09:17 AM   #4
kenosando
LQ Newbie
 
Registered: Oct 2009
Distribution: Ubuntu 9.11
Posts: 10

Original Poster
Rep: Reputation: 0
Code:
while [ $i -le $lines ]
do

        id=`range $i $i $file | cut -b 1-4`
        name=`range $i $i $file | cut -b 6-30`
        types=`range $i $i $file | cut -b 32-40`
        permits=`range $i $i $file | cut -b 42-45`
        team=`range $i $i $file | cut -b 140-147`
        i=$(($i+1))

        echo 'insert into test.employee () values ("'$id'", "'$name'", "'$types'", "'$permits'", "'$team'");' >> .insert
done
This is what I had to do, because when you stream a line of a text file to a variable (in your case $line) it doesn't have columns anymore, as I tried the exact code you did before and it wouldn't read the many spaces as columns. the range command is simply:

Code:
awk -v start="$1" -v end="$2" '{if(NR >= start && NR <= end) {print}}' $3
where arg1 is the starting point and arg2 is the end, and arg3 is the file. This was the only way I could get the columns to be preserve is actually streaming the file, and not a variable that captures the line of the file.

But I digress, just pointing that out ...
 
Old 11-06-2009, 09:27 AM   #5
MensaWater
Guru
 
Registered: May 2005
Location: Atlanta Georgia USA
Distribution: Redhat (RHEL), CentOS, Fedora, Debian, FreeBSD, HP-UX, Solaris, SCO
Posts: 5,937
Blog Entries: 5

Rep: Reputation: 752Reputation: 752Reputation: 752Reputation: 752Reputation: 752Reputation: 752Reputation: 752
Sorry I misread - I thought you were trying to figure out how to get positions out of text into MySQL. You were going the other way.

Unfortunately I don't work with MySQL much so can't really guide you.

This link talks about writing to a text file from MySQL:
http://www.wellho.net/forum/The-MySQ...text-file.html

You can use awk to pad fields. An example of that would be this test script:

Code:
#!/bin/bash
# Example script I wrote for LQ to show use of padding in awk with printf.
# 07-Jul-2009 jlightne
#
while read label address
do echo $address |awk -F. '{printf "fixed-address %03d.%03d.%03d.%03d\n",$1,$2,$3,$4}'
done <awkprintf.test
awkprintf.test is the following:
Code:
fixed-address 1.2.3.4
fixed-address 22.33.123.234
 
Old 11-06-2009, 09:35 AM   #6
ghostdog74
Senior Member
 
Registered: Aug 2006
Posts: 2,695
Blog Entries: 5

Rep: Reputation: 240Reputation: 240Reputation: 240
using a while read loop and calling external cut 6 times for each line is a terribly slow way of doing file parsing. Use awk for efficiency (or use bash's internal string functions).
 
Old 11-06-2009, 09:36 AM   #7
kenosando
LQ Newbie
 
Registered: Oct 2009
Distribution: Ubuntu 9.11
Posts: 10

Original Poster
Rep: Reputation: 0
Of course, I didn't think of awk being able to printf and pad, thanks a lot!
 
Old 11-06-2009, 09:41 AM   #8
MensaWater
Guru
 
Registered: May 2005
Location: Atlanta Georgia USA
Distribution: Redhat (RHEL), CentOS, Fedora, Debian, FreeBSD, HP-UX, Solaris, SCO
Posts: 5,937
Blog Entries: 5

Rep: Reputation: 752Reputation: 752Reputation: 752Reputation: 752Reputation: 752Reputation: 752Reputation: 752
Quote:
Originally Posted by ghostdog74 View Post
using a while read loop and calling external cut 6 times for each line is a terribly slow way of doing file parsing. Use awk for efficiency (or use bash's internal string functions).
Maybe providing an actual example routine to do this would be helpful rather than simply saying the cut routine provided isn't.
 
Old 11-06-2009, 09:46 AM   #9
kenosando
LQ Newbie
 
Registered: Oct 2009
Distribution: Ubuntu 9.11
Posts: 10

Original Poster
Rep: Reputation: 0
@ghostdog74 - it is slow, however, the cut process only needed to be done once in my case. I just needed to get existing data into the MySQL table, after that, the MySQL call will feed into the text file.

What is the field delimiter to use for a single byte in awk, as in cut -b is for byte?

Last edited by kenosando; 11-06-2009 at 09:49 AM.
 
Old 11-06-2009, 09:57 AM   #10
ghostdog74
Senior Member
 
Registered: Aug 2006
Posts: 2,695
Blog Entries: 5

Rep: Reputation: 240Reputation: 240Reputation: 240
you use substr() in awk.
 
Old 11-17-2009, 04:28 PM   #11
kenosando
LQ Newbie
 
Registered: Oct 2009
Distribution: Ubuntu 9.11
Posts: 10

Original Poster
Rep: Reputation: 0
the syntax for substr() is:

Code:
awk '{ print substr(a,b,c)}' file
where...
a is the field (or $0 for the entire line, in my case)
b is the starting position
c is the length from the starting position
 
Old 11-17-2009, 07:18 PM   #12
chrism01
Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.5, Centos 5.10
Posts: 16,225

Rep: Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021Reputation: 2021
Why don't you just keep all the data in one place eg MySQL. Copying back and forth means you'll always have some time when the 2 are not synced.

Anyway, to extract from MySQL

1. if the data is always same length (in the DB), the use the concat() fn to extract.
2. if the data is of variable lengths (in the DB), then use rpad()
http://dev.mysql.com/doc/refman/5.0/...functions.html
 
Old 11-18-2009, 10:22 AM   #13
kenosando
LQ Newbie
 
Registered: Oct 2009
Distribution: Ubuntu 9.11
Posts: 10

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by chrism01 View Post
Why don't you just keep all the data in one place eg MySQL. Copying back and forth means you'll always have some time when the 2 are not synced.
That is the goal, but the software that runs read a column-delimited file that has to be formatted per its standards.

The easiest way for me was using awk's printf and then speciftying the length of the field and then the data to write to it. The data is not the same length, as there are name strings. Also, since this will translate into a web-based tool, PHP does fwrite() which also has extensive formatting options.
 
  


Reply

Tags
columns, file, mysql, text


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to copy an entire disk byte-for-byte Pawprint Linux - Software 6 06-16-2011 11:01 AM
Read text file column by column RVF16 Programming 11 05-31-2009 07:16 AM
how to append columns form a column file in another file adam_blackice Programming 4 09-17-2007 11:33 PM
I want to change one byte in a large file, pajout Linux - Newbie 4 03-23-2006 10:53 AM
File Byte Level replication. Jamesman Linux - Software 3 01-26-2006 02:40 PM


All times are GMT -5. The time now is 01:22 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration