Perl Script To Convert Flat-Text To MySQL

[Justin]

I'm pretty sure that's all installed. I installed WWWThreads, which is Perl/MySQL, and it worked first time without a hitch even though the instructios said to install a million things first - all was already here :)[nbsp][nbsp]You'll find that a lot here though :)

I personally prefer PHP with MySQL. PHP was written strictly as a web based scripting language - I don't think that is Perl's primary intended use, although it is extremely popular for it - but PHP takes care of content headers, has built in functions for printing additional headers, setting cookies, etc. It also automatically converts GET, POST, and cookie variables - it does 90% of the work for you :)[nbsp][nbsp]And it's integration with MySQL is totally seamless...

But that's me :)[nbsp][nbsp]I just really like php for some reason...

------------------
Justin Nelson
FutureQuest Support

[DanS]

Thanks for your replies, Justin.[nbsp][nbsp]I appreciate you helping me get up to speed quickly.

I have fixed-length fields in the flat-text database, so wouldn't that necessitate a lot of text processing to get the import function to work properly (it appears that the function requires delimiting)?[nbsp][nbsp]Further, the flat-text tables have dates in YYYYMMDD.[nbsp][nbsp]Wouldn't this require a lot of text processing?

[Terra]

Just write an import massager in Perl using the DBD::DBI (fought with them for a week compiling/testing/installing them into the FQuest core)...

DBD::DBI is fully functional...

You will definitely have to make sure that your Time/Date input from your files is reworked for DATETIME field injection compatability...[nbsp][nbsp]I usually just create a regex to pick it apart and sprintf() to the desired format...

One day I will have to write an FQuest installed Perl Module lister, because I have TONS of modules installed... (FEEL the power of cpan) ;)

I usually check my installed core every 2 weeks and run updates/upgrades as needed...

--
Terra
--Member of the Bonified Perl Module fanatics club--
FutureQuest

[DanS]

Thanks Terra![nbsp][nbsp]Very informative.

On third thought, I might just skip DBD:DBI.[nbsp][nbsp]As you say, the regular expressions of perl are extremely powerful.[nbsp][nbsp]Might take a couple of extra minutes, since I'll be gulping >25MB of text.[nbsp][nbsp]But that's what we have your fast servers for! ;)[nbsp][nbsp]Once this is done, I will be able to use the load function to get it into MySQL.

Also thanks for the lwpcook man, which you mentioned some 5 months ago in a post.[nbsp][nbsp]I just used the large file code snippet to fetch 4 zips. :)

Once I add that to my crontab, I'll be well on my way!

[DanS]

Oops.[nbsp][nbsp]If I don't use DBD:DBI, there will be an extra step.[nbsp][nbsp]So, on fourth thought...

[DanS]

In a far off thread somebody said...

"Perl![nbsp][nbsp]Perl's text handling capabilities are great to begin with, so a flat text database will work well. Then, when you're ready to move on to a database (I'm assuming mySQL), it shouldn't be very hard to whip up a small Perl script that would take the info from the text database, and dump it into a mySQL table (if you know anything about mySQL anyway, I sure don't... (I still don't wanna wait til May for the book))"

Has anybody written a script such as this yet?[nbsp][nbsp]I will probably integrate it into a perl script that drops a current MySQL database, lwps to a remote site, downloads about 20 zipped flat-text files, unzips them, converts them to MySQL tables in a newly created database, and creates appropriate indices.

Any ideas?

[DanS]

Aggghhh![nbsp][nbsp]I'm becomin a nuisance to myself and others by answering my own questions... :)

The answer has to do with the mysqlimport executable.[nbsp][nbsp]I'll have to figure out all of the options.

[Justin]

I don't know if anyone has written a script for that yet, but you might be confused - you only get one database... you can't drop and create databases (actually you could drop your database but cannot create one - so be careful :))

You can create all the tables you want to, but can't create databases. You will only have one, with the name being xdomainname... it seems limited, but as long as you name your tables properly there shouldn't be any problems :D

Just wanted to let you know before you end up fighting with a script trying to figure out why you can't create a database (or before you dropped your database...).

------------------
Justin Nelson
FutureQuest Support

[DanS]

Thanks Justin![nbsp][nbsp]That would have indeed been a problem.[nbsp][nbsp]But I can delete and create tables from the script, no?

In a way, I'm inclined to go with DBD:DBI rather than working through PHP.[nbsp][nbsp]Any suggestions either way?[nbsp][nbsp]Do the servers have the DBD:DBI module loaded?[nbsp][nbsp]How about mysql.pm?