Adding column to tab-delimited file 1

[aalnaif]

I am writing a bash script to automatically import a tab-delimited file into an MySQL database. The problem is that I want to add a date column to the file before I import it into the database. The date is included in the filename.

For example, assume a file named patientinfo_20070217.txt. Initially, the tab-delimited file looks like this:

Bob Billy 3423
Hunt George 1232
Adams John 3456

Before importing this file into the MySQL database, I want to change the file to look like this:

Bob Billy 2007-02-17 3423
Hunt George 2007-02-17 1232
Adams John 2007-02-17 3456
Hunt George 2007-02-17 1232

Notice that the date is the same date that is stated in the filename. Does anyone know how to do this?

 

[mbrooks]

IS there any reason you are doing this before? Since the date is not unique it's alot easier to add the date column after the import from within MySQL. Also, why are you using .bash? To accomplish this task would be alot easier if you were using perl.

M. Brooks

http://mbrooks.info

 

[aalnaif]

The reason I am using bash is so that I can set up a cronjob to run daily. Would I be able to do this in Perl? Also, why would it be easier to accomplish this task in Perl?

 

[sleipnir214]

I would, in the script invoked by cron, read in the filename, import the data into MySQL, then update all records in the MySQL database that have blank dates, using the filename to do it.

As mbrooks has said, this activity would be best accomplished by a programming language like PHP, ruby or perl than in a bash shell script.

While the question of "How do I run a PHP/ruby/python/perl script as a cron job on my Linux server?" is appropriate to this forum, any questions about how to write that script is appropriate to a forum devoted to that language. In this case, forum434, forum753, forum278 or forum219, respectively.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[stevexff]

perl would be a good solution, as it has the facility to connect directly to MySQL and perform your updates. It will also parse your input lines more easily than bash.

Rather than going off-topic here, if you want to post it on the perl forum forum219, I'll have a look at it...



Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[Annihilannic]

You could do something like this in bash if you wish:

#!/bin/bash

file=patientinfo_20070217.txt
date=${file%.txt}
date=${date#patientinfo_}
date=$(echo $date | sed 's/\(....\)\(..\)\(..\)/\1-\2-\3/')

while read surname firstname number
do
        echo $surname $firstname $date $number
done < patientinfo_20070217.txt > /tmp/importfile.$$

# do mysql import from /tmp/importfile.$$ here

rm /tmp/importfile.$$

Annihilannic.