You didn't mention what sort of database you are connecting to, but if it's any sort of reasonably-well-endowed RDBMS (oracle, postgres, mysql, etc), then it comes with its own command-line tool for loading tab-, comma- or pipe-delimited data files directly into to the database (e.g. oracle's "sqlload", mysql's "mysqlimport", etc).

And those vendor-supplied tools are orders of magnitude faster (and usually hard to beat in terms of data validation) when compared to executing a series of "insert" statements from a Perl/DBI script. Also, they are pretty flexible: you can choose what suits you best in terms of record and field delimiters, and how the db server should behave while loading the data.

If the data files you are importing are in the hundreds (or maybe few thousands) of records, the speed difference might not count for much. But the fact that the tool already exists (and does excellent error trapping/handling) still makes it worthwhile.

For data sets of many thousands of records, you could be looking at a run-time difference of 10 to 1 or worse for Perl/DBI inserts vs. the server's native import tool (i.e. if mysqlimport takes 6 minutes, Perl/DBI inserts will take at least an hour).

It looks like you are opening a file for outputting cvs-style records, but you don't seem to ever write to that file handle. Let me suggest that you drop the idea of connecting to the database in this script, and just focus on writing a proper comma- (or better yet, tab-)delimited output file that puts the intended insertion data into a consistent set of plain-text rows.

Then read up on your db-server's import tool and work out how to feed it the data file that was written by your perl script. You'll finish your task a lot quicker that way, because in addition to the actual db load being a lot faster, the perl script will be a lot simpler to code, and will go very fast on its own.

In reply to Re: Parsing Script by graff
in thread Parsing Script by phimtau123

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.