Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

perl DBI LOAD DATA INFILE

by nickd (Initiate)
on Oct 14, 2013 at 00:18 UTC ( #1058110=perlquestion: print w/ replies, xml ) Need Help??
nickd has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I am relatively new to perl and this forum. I am trying to use perl DBI for mySql LOAD DATA INFILE statement to upload a csv file to a mySQL database. However, the execute statement returns an undef value. However if i use a select or desc statement, it works fine.

#!/usr/bin/perl -w use strict; use warnings; use File::Basename; use DBI; use DBD::mySQL; my $data_path="D:\\NickD\\Project\\StockData\\"; my $db = "TestMMDB"; my $user = "user"; my $pass ="pass"; my $host = "localhost"; my $query =""; my @row; ## First get all files to upload, scan names, identify table to be upl +oaded in my @files = glob("$data_path*.csv"); DBI->trace(1); ## Connect to the database my $dbh = DBI->connect("dbi:mysql:$db:$host",$user,$pass); ## Gather the files to upload to DB foreach my $file(@files){ my $filename = basename($file); my ($db_table,$date) = split("_",$filename); ## Create Query # $query ="SELECT * FROM $db.$db_table"; $query = q{LOAD DATA INFILE ? INTO TABLE ? FIELDS TERMINATED B +Y ',' (Date,Symbol,Open,High,Low,Close,Volume)}; my $sqlQuery = $dbh->prepare($query); my $rv = $sqlQuery->execute($file,$db.".".$db_table) or die "Oops! +: Can't execute the query :".$sqlQuery->errstr; while (@row = $sqlQuery->fetchrow_array()) { print "@row\n"; } } my $rc = $dbh->disconnect(); exit(0);

All help will be greatly appreciated.

Comment on perl DBI LOAD DATA INFILE
Download Code
Re: perl DBI LOAD DATA INFILE
by Anonymous Monk on Oct 14, 2013 at 00:49 UTC
    So what is the question? What error message do you get?
      I do not get any error value. It says undef. I have posted the trace output below
      !! warn: 0 CLEARED by call to connect method <- connect('TestMMDB:localhost', 'user', ...)= ( DBI::db=HASH(0x18 +88a5c) ) [1 items] at DBI.pm line 658 <- STORE('PrintError', 1)= ( 1 ) [1 items] at DBI.pm line 710 <- STORE('AutoCommit', 1)= ( 1 ) [1 items] at DBI.pm line 710 <- STORE('Username', 'user')= ( 1 ) [1 items] at DBI.pm line 713 <- connected('dbi:mysql:TestMMDB:localhost', 'user', ...)= ( undef + ) [1 items] at DBI.pm line 720 <- connect= DBI::db=HASH(0x1888a5c) <- STORE('dbi_connect_closure', CODE(0x1896404))= ( 1 ) [1 items] +at DBI.pm line 729 <- prepare('LOAD DATA INFILE ? INTO TABLE ? FIELDS TERMINATED BY ' +,' (Date,Symbol,Open,High,Low,Close,Volume)')= ( DBI::st=HASH(0x1889b +b4) ) [1 items] at te st.pl line 33 <- execute('D:\NickD\Project\StockData\PriceAMS_2013-10-13.csv', ' +TestMMDB.PriceAMS')= ( undef ) [1 items] at test.pl line 34 <- errstr= ( undef ) [1 items] at test.pl line 34 Use of uninitialized value in concatenation (.) or string at test.pl l +ine 34. Oops!: Can't execute the query : at test.pl line 34. <- DESTROY(DBI::st=HASH(0x1889df4))= ( undef ) [1 items] at test.p +l line 34 <- DESTROY(DBI::db=HASH(0x1888c0c))= ( undef ) [1 items] at test.p +l line 34 <- disconnect_all= ( ) [0 items] (not implemented) at DBI.pm line +737 ! <- DESTROY(DBI::dr=HASH(0x18964b4))= ( undef ) [1 items] during gl +obal destruction
Re: perl DBI LOAD DATA INFILE
by Kenosis (Priest) on Oct 14, 2013 at 03:10 UTC
Re: perl DBI LOAD DATA INFILE
by Anonymous Monk on Oct 14, 2013 at 04:28 UTC

    To import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the mysql prompt:

    LOAD DATA LOCAL INFILE '/importfile.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, filed2, field3);

      Hi, I will need to upload multiple files daily hence am trying to script it using perl. I cannot use manual upload at the command prompt.

Re: perl DBI LOAD DATA INFILE
by Generoso (Vicar) on Oct 14, 2013 at 04:51 UTC

    Sorry I was not logged in. Here is the entire article

    MySQL Database

    How do I import delimited data into MySQL?

    Applies to: Grid System

    If you have data that you need to bring into your MySQL database, there are a few ways to do it. Exporting data out of mysql is another topic, described here.

    1. Using the LOAD DATA INFILE SQL statement

    For security reasons, no one has the mysql FILE priv, which means you cannot "LOAD DATA INFILE". You can, however, use a "LOAD DATA LOCAL INFILE" statement as long as you have a mysql prompt on our system and have uploaded the data file to your account here first.

    The "LOAD DATA LOCAL INFILE" statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.

    To import a file this way, first upload your data file to your home directory on our system with FTP or SCP. Then get a shell prompt on our system, and then a MySQL Monitor prompt so that you can issue the SQL that will import your file.

    For example, suppose you have a data file named importfile.csv that contains 3 comma separated columns of data on each line. You want to import this textfile into your MySQL table named test_table, which has 3 columns that are named field1, field2 and field3.

    To import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the mysql prompt:

    LOAD DATA LOCAL INFILE '/importfile.csv'
    INTO TABLE test_table
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (field1, filed2, field3);

    The above SQL statement tells the MySQL server to find your INFILE on the LOCAL filesystem, to read each line of the file as a separate row, to treat any comma character as a column delimiter, and to put it into your MySQL test_table as columns field1, field2, and field3 respectively. Many of the above SQL clauses are optional and you should read the MySQL documentation on the proper use of this statement.

    2. Using a script to parse and import the file

    You can also write a script in any programming language that can connect to MySQL (such as PHP) to open your data file, break it up into an array of lines that each represent a row of data, split each line up by the delimiter character (such as a comma ',', tab '\t', semicolon ';', space ' ', etc.), and then perform invididual MySQL INSERT queries (one INSERT for each line) to insert all your data from the file into the appropriate table fields.

    Such scripts are not difficult to write in less than 15 lines and can import data from text files just as effectively as a LOAD DATA LOCAL INFILE command. A working example script written in PHP appears below in the Annotations.

    3. Importing a mysqldump

    If your data file actually comes from another MySQL database, and not from Excel or any other source, then the most direct way to export and import your data would be to dump out your table or entire MySQL database on the original database server using the mysqldump command, FTP the resulting dump file to your account here, and then import the dump file at a shell prompt.

    For instructions on creating the dumpfile using the mysqldump command, see this FAQ. For instructions on how to import a dump made with mysqldump, see this FAQ.


    Here is an example of creating a data file and then importing it with LOAD DATA syntax.

    mulder@modwest:/$ echo 'a b c d' > testfile
    mulder@modwest:/$ mysql

    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 3979754 to server version: 4.0.15-Max-log

    mysql> CREATE TABLE ´test´ (
    -> ´fe´ VARCHAR( 2 ),
    -> ´fi´ VARCHAR( 2 ),
    -> ´fo´ VARCHAR( 2 ),
    -> ´fum´ VARCHAR( 2 )
    -> );
    Query OK, 0 rows affected (0.00 sec)

    mysql> load data LOCAL infile '/testfile' into table test
    fields terminated by ' ' lines terminated by '\n';
    Query OK, 1 row affected (0.02 sec)
    Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

    mysql> select * from test;
    +------+------+------+------+
    | fe | fi | fo | fum |
    +------+------+------+------+
    | a | b | c | d |
    +------+------+------+------+
    1 row in set (0.00 sec)

    mysql> load data LOCAL infile '/testfile' into table test
    fields terminated by ' ' lines terminated by '\n' (fum, fo, fi, fe);

    Query OK, 1 row affected (0.02 sec)
    Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

    mysql> select * from test;
    +------+------+------+------+
    | fe | fi | fo | fum |
    +------+------+------+------+
    | a | b | c | d |
    | d | c | b | a |
    +------+------+------+------+
    2 rows in set (0.00 sec)

    mysql> exit
Re: perl DBI LOAD DATA INFILE
by poj (Priest) on Oct 14, 2013 at 13:50 UTC

    Most DBDs won't allow placeholders for a table name. Try using variable interpolation. For example ;

    #!/usr/bin/perl use strict; use DBI; my $db_table = 'test1'; my $dbh = dbh();# connect code as required #my $count = $dbh->do("DELETE FROM $db_table"); #print "$count lines deleted from $db_table\n"; my $sql = <<SQL; LOAD DATA LOCAL INFILE ? INTO TABLE $db_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3); SQL my $count = $dbh->do($sql,undef,'importfile.csv'); print "$count lines inserted into $db_table\n"; my $sql = qq{ SELECT * FROM $db_table }; my $sth = $dbh->prepare($sql); $sth->execute(); while (my @f = $sth->fetchrow_array){ print "@f\n"; }
    poj

      Dear POJ, Your suggestion worked. Thanks a lot for your help and suggestion.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1058110]
Front-paged by kevbot
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (8)
As of 2014-08-29 21:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (289 votes), past polls