Converting table data (txt files) into SQL?

[Halide]

I have several text files with individual table data. I'm trying to get the data into my database, but I am denied the "LOAD DATA" priv. So, what I'm asking is, is there an alternative program that I can use to convert it into SQL (insert statements)?

[iDxMan]

Here's a very quick hack that might help. It assumes your data is in pipe-delimited format - change $delim as necessary.


#!/usr/bin/perl

$delim = '\|';
$table = shift;

if (!@ARGV) {
die "Usage:\n$0 -table name- -input file-\n";
}

while(<>) {
chomp;
s/\r//g;

@line = split(/$delim/o);
$str = "'" . join("','", @line) . "'";
print "INSERT INTO $table ($str);\n";
}



Sample data:


r1f1|r1f2|r1f3
r2f1|r2f2|r2f3


Usage:


./script_name table_name data_file


output:


INSERT INTO table_name ('r1f1','r1f2','r1f3');
INSERT INTO table_name ('r2f1','r2f2','r2f3');

[Halide]

Hey, thanks for the reply... I really have no idea how to execute perl scripts, though, I'm under WINXP... Although, that does give me an idea. I could parse it with a PHP script even...

[Halide]

Argh. Now I see a problem...

Mine is delimited by commas, and since they aren't being 'escaped' then i couldn't use the explode function?

I just need to get my priveleges updated on the server... :eh:

[Halide]

or, i could tab delimit, that might work

[iDxMan]

:) That was the second part of my reply I didn't include. You could parse it with php and export to a file or just insert the data then.

Either explode or preg_split should work.


$foo = explode(',', $line);
$foo = preg_split(/\,/, $line);



perl should work on XP via the activestate install: www.activestate.com/activeperl

[Halide]

hehe cool... I might get into perl soon...

I'm starting college monday.

Anyway, I figured out a completly different work around, by using phpMyAdmin on my local computer and then saving an SQL dump.

woohoo. :)

thanks for your help anyway!
cya,
hal

[Strike]

Just for fun, here's the Python version of that Perl script:


import sys

if len(sys.argv) != 2:
print "Usage: ./script.py table_name file_name"
sys.exit(1)

delim = '|'
table = sys.argv[0]

f = open(sys.argv[1])
for line in f.readlines():
fields = line.split(delim)
str = "'"
for field in fields:
str += field + ","
str = str[:-1] + "'"
print "INSERT INTO %s (%s);" % (table, str)


note - untested

[Halide]

I guess that was fun, converting into python (never messed with that either) :)

[darelf]

If it's Strike, converting to Python may have been orgasmic....

(no offense, Python is also my favorite....)

[Strike]

Originally posted by Halide
I guess that was fun, converting into python (never messed with that either) :)
Well, it's half fun. Reading the Perl and deciphering it isn't fun. Letting the Python just flow once Ihave a description of the code in English is fun :)

[Halide]

haha, you are definately a python "freak"... :P

[DeadlySin3]

Originally posted by iDxMan


perl should work on XP via the activestate install: www.activestate.com/activeperl

Just to let ya know, I've had perl installed on my XP Pro system for over a month, its works great w/no problems :)