dear monks
I'm trying to parse the CSV file with TEXT:CSV_XS and then insert the data into MySQL database. The code works fine I think, but I'm wondering if I can optimize is somehow, because it takes cca 2 minutes to parse 200000 lines, which I think is too long. Do you have an idea what I'm doing wrong?

the line of CSV looks like:
-------------
1051;3;10;1;900;EQPT_TYPE_B;B;2.00;2.00;0.00;1.00;0.00;1
-------------
and lines are separated "\n" (LF visible in Notepad++)

The code I have so far looks like : 
use strict;
use warnings;
use Time::Piece;
use DBD::mysql;
use TMT_database; #own module
use Text::CSV_XS;

my $config = init_params();
my $dbh = mysql_db_open($config->{'mysql_server'},'uni',$config->{'mys
+ql_user'},$config->{'mysql_pwd'});

print localtime."\n";
my $csv_dbh = Text::CSV_XS->new ({
                sep_char => ';',
                binary => 1,
                blank_is_undef => 1,
                empty_is_undef => 1,
                }
             );
###### removing garbage from the file
local $^I = '.dirty';
local @ARGV = ( "inputFile.txt" );
while ( <>){
        
    s/--VER.*\n|--DAT.*\n|--EN.*\n|--EIN.*\n|\/|\\//g;            
    s/null|NULL//g;
    s/[^\x00-\x7E]//g;    #removing non proper ascii
    s/(?m)(?:^|;)[^;\n]{30}\K[^;\n]+//g; #truncating the field to 30  
+chars
        print;
}
###################################################################
###### parsing and inserting into mysql
open my $io, "<","inoutFile.txt";
my @headerFields = @{$csv_dbh->getline ($io)};
my $row = {};
$csv_dbh->bind_columns (\@{$row}{@headerFields});

print localtime."\n";

my $fieldList = join ",", @headerFields;
my $field_placeholders = join ",", map {'?'} @headerFields;    
my $query = qq{INSERT INTO some_table (id,$fieldList) VALUES (id,$fiel
+d_placeholders)};
my $mysql_sth = $dbh->prepare($query);                
while ($csv_dbh->getline ($io)) {
        my @fieldstoInsert = map { $row->{$_}} @headerFields;
        if ( $fieldstoInsert[0] =~ /^\d{1,}$/ ){
        $mysql_sth->execute(@fieldstoInsert);
                $mysql_sth->finish;
        }
  }
print localtime."\n";
####################################################################

[download]

In reply to How to optimise " csv into mysql " using Text:CSV_XS and DBD::Mysql by taiko

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.