garyj has asked for the wisdom of the Perl Monks concerning the following question:

I have a Perl script that writes to a flat file DB and want to convert it to write to MySQL. Is this easy to do? If so, can you provide some code for basic setup and config, and point out differences in how values would be called and used in the script.

I'm a newbie, so...KISS

Or tell me I'm in way over my head and job it out.

Replies are listed 'Best First'.
Re: convert flat file to MySQL
by grep (Monsignor) on Jan 16, 2002 at 00:36 UTC
    Well how's this for KISS... there is a utility with MySQL called 'mysqlimport'

    shell> mysqlimport [options] database textfile1 [textfile2....]

    You can read the man page here

    Don't reinvent a good wheel

    grep 
    grep> cd pub
grep> more beer

    [download]
[reply]
[d/l]
[select]
(Ovid) Re: convert flat file to MySQL
by Ovid (Cardinal) on Jan 16, 2002 at 00:51 UTC

    Before you do the conversion, it's very important to validate your flat file data. Since MySQL often lacks many real database features (specifically, I'm thinking about foreign key support) and since flat files don't do much in the way of data type validation, you could easily wind up with some significant problems in trying to import the data into your database. Having bad datatypes, orphan records, and many other issues will significantly complicate this task.

    Rather than just tell you about the problems, I'll offer a solution :) If you are using CSV files, or can easily convert to them, check out my CSV Database Validation program. This will allow you to create a schema for your database, validate foreign keys contrainst, unique fields, and even optionally specify regular expressions that every field must match. I've included full POD for the program and even a mini-tutorial.

    Cheers,
    Ovid

    Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

[reply]
Re: convert flat file to MySQL
by runrig (Abbot) on Jan 16, 2002 at 00:36 UTC
    If your script uses DBI and either DBD::CSV or DBD::AnyData (depending on what kind of flat-file it is), then you're most of the way there. If not, then convert your script to use those modules, and then the switch to MySQL (and DBD::mysql) should be easy.

    (But you probably should be using the native import utility as suggested below, unless you need to massage the data in a way that the import utility won't handle...)

[reply]
Re: convert flat file to MySQL
by talexb (Chancellor) on Jan 16, 2002 at 00:43 UTC
    Strongly recommend you check out the MySQL documentation page. Also have a look at this article for background on DBI which is probably how you'd implement your interface to MySQL.

    It is reasonably easy to set up an SQL statement of the form 

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...

    [download]
    (grabbed from their docs, section 6.4.3, INSERT Syntax).

    Instead of relying on positioning (as you are now with your flat file, using either offsets or field separators), you can insert the field values by name. 

    insert into sales (agent, stock_num, value)
values ("AB234","RB-55602","6500")

    [download]

    Finally, try doing a search on this site for MySQL. You'll probably find a wealth of information.

    --t. alex

    "Of course, you realize that this means war." -- Bugs Bunny.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom