Transfer data from one file to another

inquisitive101 · #1 (**permalink**) 01-05-2009

Hi,
I'm relatively new to shell scripting, Ive worked on a few basic scripts and used most of the unix commands in the simplest of situations. But I am now faced with a task that's seems to be beyond me.

I have a file with some data in the form of rows and columns :

123 4536 abcd4 677 bbb ggg nnn 32425
343 5656 abcd6 566 eee fff ooo 56454
343 5645 abcd7 556 ddd lll jjj 43536....
.
.
.
and so on

I need a shell script to pick this data and put it into an insert command which lies in another text file.The insert command would be in the form insert into table_xyz values ($a,'$b','$c',etc) where the variables would be the values from the above mentioned data file. the output of the script should be as many insert statements as there are lines in the data file.As in,
insert into table_xyz values(123, 4536, 'abcd4', 677, 'bbb', 'ggg', 'nnn' ,32425)
insert into table_xyz values(343, 5656, 'abcd6', 566, 'eee', 'fff', 'ooo' 56454) etc

Some sort of script to store the data in an array or something and iterate through it?Any help,advice would be greatly appreciated...

Thanx in advance

summer_cherry · #2 (**permalink**) 01-05-2009

hi below perl script may help you some
Not sure whether your column is in fixed length, if yes, can remove those trim( and ), and the number of < indicate the length of your value, so make sure use the longest < for all of them depending on your longest column value.

Code:

format TOP=
insert into table_xyz values(@<<<<<,@<<<<<,trim('@<<<<<<'),@<<<<<,trim('@<<<<<'),trim('@<<<<<'),trim('@<<<<<<'),@<<<<<<);
$a $b $c $d $e $f $g $h
.
$~=TOP;
open FH,"<a.txt";
while(<FH>){
	($a, $b, $c, $d, $e, $f, $g, $h)=split(" ",$_);
	write;
}
close FH;

pludi · #3 (**permalink**) 01-05-2009

This Perl script might help you. It takes 2 arguments:

Seperator used (in your example ' ')
Table for inserting (in your example 'table_xyz')

The data itself is read from standard input, the INSERT statements are print to standard out.

Code:

#!/usr/bin/perl -W

use strict;
use warnings;

my $sep   = $ARGV[0];
my $table = $ARGV[1];
while ( my $line = <STDIN> ) {
    print "INSERT INTO $table VALUES(";
    my @line = split /$sep/, $line;
    for ( my $i = 0 ; $i < $#line ; $i++ ) {
        $_ = $line[$i];
        if (/\D/) {
            print '"', $_, '"';
        }
        else {
            print $_;
        }
        print ',' if $i < $#line - 1;
    }
    print ");\n";
}

Your example would give

Code:

$ perl csv2sql.pl ' ' table_xyz < example.txt
INSERT INTO table_xyz VALUES(123,4536,"abcd4",677,"bbb","ggg","nnn");
INSERT INTO table_xyz VALUES(343,5656,"abcd6",566,"eee","fff","ooo");
INSERT INTO table_xyz VALUES(343,5645,"abcd7",556,"ddd","lll","jjj");
$

Christoph Spohr · #4 (**permalink**) 01-05-2009

Or as simple shell script:

Code:

while read a b c d e f g h 
do 
    echo "insert into table_xyz values($a, $b, '${c}', $d, '${e}', '${f}', '${g}', $h)"
done < datafile

HTH Chris

inquisitive101 · #5 (**permalink**) 01-05-2009

Thanx guys.....

will try 'em out...at least now I know in which direction I need to move

inquisitive101 · #6 (**permalink**) 01-07-2009

Ok now how about if I have a new scenario where my .csv file looks like this :

a, b ,c ,d
a ,b, c, d
a, b, c, d
e, b, c ,d
e ,b, c ,d
e ,b ,c, d

My output should b 3 insert queries,that are only slightly different. i.e :

insert into table_xyz values(123, $a, 'abcd4', 601, $b, $c, $d ,32425)
insert into table_xyz values(124, $a, 'abcd4', 602, $b, $c, $d ,32425)
insert into table_xyz values(125, $a, 'abcd4', 603, $b, $c, $d ,32425)
insert into table_xyz values(126, $e, 'abcd4', 601, $b, $c, $d ,32425)
insert into table_xyz values(127, $e, 'abcd4', 602, $b, $c, $d ,32425)
insert into table_xyz values(128, $e, 'abcd4', 603, $b, $c, $d ,32425)

the problem is the way the 2nd and 4th fields in the query should b printed. while the 2nd field should b repeated 3 times(as in read each line of the data file), the 4th field increments to 3 and goes back after every 3rd line of the file...

any suggestions...? need a shell script ...

i tried modifying shell script given by Christoph but the output is such that the same 3 queries are repeated as many times as there lines in my data file before moving on...

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Transfer data from one file to another	inquisitive101	UNIX for Dummies Questions & Answers	1	01-05-2009 03:42 AM
FTP - Data Transfer Limitations.	system-admin	AIX	2	12-20-2006 03:11 AM
Data Transfer programs in IPC Mechanisms??	boris35	UNIX for Dummies Questions & Answers	2	05-26-2005 12:12 PM
data corruption with ftp transfer	malcom	UNIX for Advanced & Expert Users	12	08-04-2003 07:38 AM
How much data will transfer at 100 full	98_1LE	UNIX for Dummies Questions & Answers	3	10-05-2001 09:48 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by