Take a look at "meta syntactics".
For example (using perl)
my $dbh = DBI->connect('DBI:mysql:SD2:localhost', 'root','********') or die
"Couldn't connect to database $!\n";
my $SQL =<<"EOT";
insert into
table(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,c
ol14,col15,col16,col17)
values ( ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?,
?, ?, ?, ?)
EOT
;
my $cursor = $dbh->prepare($SQL) or die "can't make cursor";
while ( <> ) { # your data is fed into the program via STDIN
my @data=split;
$cursor->execute(@data);
}
$cursor->finish;
$dbh->disconnect();
The question marks are replaced by real data on the fly from STDIN.
Thanks to Richard Dice for this interesting wrinkle in my brain.
sql,query foo
~Kelly W. Black
-----Original Message-----
From: Daniel Kasak [mailto:dkasak@;nusconsulting.com.au]
Sent: Tuesday, November 05, 2002 2:49 PM
To: Alain Motasim; [EMAIL PROTECTED]
Subject: Re: loading files containing fixed-length fields
Alain Motasim wrote:
> Hello,
>
> I would like to load text files containing fixed-length fields into a
> MySQL table by specifying position indices like when loading files
> into an Oracle database, with a syntax close to this:
>
> LOAD DATA
> INFILE'c:\mydata.dat' append INTO MYTABLE ( ID
> POSITION( 1 : 10 ),
> NAME POSITION( 11 : 20 ))
>
> I have browsed the URL
>
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#LOA
D_DATA
>
> but I haven't found anything similar. Is there a chance I can load
> this kind of file inline with MySQL?
>
> Thanks for your help.
> Best regards,
>
> Almo
I'm pretty sure the only way to do this is to define the size of each
field in the table definition in MySQL, eg:
create table ImportedStuff
ID mediumint(10),
Name varchar(10)
etc
Then you use something like:
load data infile '/path/to/file.txt' into table ImportedStuff fields
terminated by '' lines terminated by ''
The fields terminated by '' and lines terminated by '' when used
together tells mysql to use a fixed-width import, with the widths
specified by the table definition you're importing into. I know this is
a bit clumsy, but it works and it's all we've got...
If you can't change the spec of your table you're importing into, try
creating a tmp table with the right spec, importing into that, then
appending from that into the destination table.
And maybe someone can write some code which automates this work around
so we can support the Oracle-style import from above (don't look at me,
I'm VB and SQL only...).
Dan
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
