Jump to content
gemini

Automate Csv Into Db

Recommended Posts

Hello everyone,

 

I have a csv file that being uploaded to my site every morning and I need to import it into existing table - update the data. I'm not a PHP wiz, but I'm sure I could find a script that I can adjust to my needs and make it work, but I would like to ask if there is easier way do that. I know I can import the data manually, so I was wandering if I can use the same tools by setting up cron job and without any additional scripting?

 

Any suggestions?

 

Thank you.

Share this post


Link to post
Share on other sites
I know I can import the data manually, so I was wandering if I can use the same tools by setting up cron job and without any additional scripting?

I think that would depend on how you are "manually" importing the data.

Share this post


Link to post
Share on other sites

I don't think you'd need a complicated script, but I think you'd need one. If I understand correctly what you're doing, the script would need to log in and connect to your database, then execute a 'LOAD DATA INFILE' query to import your CSV file. It's not using phpMyAdmin to do the job, but it's doing the same task in the same way that phpMyAdmin does.

Share this post


Link to post
Share on other sites
I don't think you'd need a complicated script, but I think you'd need one.  If I understand correctly what you're doing, the script would need to log in and connect to your database, then execute a 'LOAD DATA INFILE' query to import your CSV file.  It's not using phpMyAdmin to do the job, but it's doing the same task in the same way that phpMyAdmin does.

 

Thank you for pointing me into right direction. I still have problems - here is my code:

 

><?php
$dbh=mysql_connect ("localhost", "xxx", "xxx") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("some_db"); 
$sql1 = "TRUNCATE TABLE sometable";
$sql2 = "LOAD DATA INFILE '/home/my_host/public_html/folder/test.txt' INTO TABLE sometable FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES";
mysql_query($sql1) or die ('I can not empty the 4closures table.');
mysql_query($sql2) or die ('I can not insert data into 4closures table.');
echo "Done";
?>

 

The syntax seas to be correct and the main problem in the location of the text file. Would you please tell me what path I should use - I tried everything.. may be there is something else.. I tried importing manually through phpMyAdmin and it is successful, also it generated SQL and PHP code that I took for base, the only difference that in the phpMyAdmin I uploaded file form my hard drive and in the script I want it to be off the same site.

 

Thank you for your help.

Share this post


Link to post
Share on other sites

Without knowing the exact details about what you've set up on your site, the path you have is generally correct, unless you're getting a specific error about not being able to find the file.

 

Depending on how your CSV file is being uploaded to your site, I'd first suspect the 'LINES TERMINATED BY'. On a Windows machine, '\r\n' would be correct, but on a Linux server, it normally would be just '\n'. If your file is uploaded via FTP in ASCII mode, the '\r\n' line endings are automatically converted to '\n'. Assuming this is the case, no data would ever be imported, as the MySQL server never finds a "\r\n" character combination to mark the end of the first line.

 

If this sounds likely, you'd need to change the 'LINES TERMINATED BY' to look for just '\n' rather than '\r\n':

>LINES TERMINATED BY '\\n'

Share this post


Link to post
Share on other sites

Thank you David. I changed it to \n, but still no difference. I actually have txt tab delimited file as it stated in the LOAD DATA line. I tried using full path in the phpMyAdmin with different options and couldn't import data either - only fom my hdd :angry:

Share this post


Link to post
Share on other sites

I created my own test database and tried importing a tab delimited file with a script and queries pretty much as you have. The 2nd query (the 'LOAD DATA INFILE' one) would trigger an error, but the first one did not. I added '. mysql_error()' to the die messages for each query, and discovered the 2nd query was failing with an "Access denied" message.

 

In order to execute a 'LOAD DATA INFILE' query to read a file on the server, the MySQL user must have the MySQL 'FILE' privilege. It would appear that granting a MySQL user 'ALL' privileges in phpMyAdmin does not include the 'FILE' privilege.

 

There is a way around this, however. When you import a file that is on your local computer, phpMyAdmin is executing a 'LOAD DATA LOCAL INFILE' query. The 'LOCAL' keyword tells the MySQL server that it needs to get the file from the client computer (your PC in this case - local to you) instead of the MySQL server. Executing a 'LOAD DATA LOCAL INFILE' query does not require the MySQL 'FILE' privilege.

 

Your script can take advantage of this by running a 'LOAD DATA LOCAL INFILE' query instead of 'LOAD DATA INFILE'. The MySQL server still thinks it needs to get the file from the 'client' computer, but in this case, your script running on the server *is* the client, and you can specify a server path to the file anyway without having to have the MySQL 'FILE' privilege.

 

I was able to successfully import a tab delimited file with this query:

>$sql2 = "LOAD DATA LOCAL INFILE '/home/my_host/public_html/folder/test.txt' INTO TABLE sometable FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\r\\n' IGNORE 1 LINES";

Whether or not you need '\\r\\n' or '\\n' for 'LINES TERMINATED BY' would depend on how your files are being created and uploaded to the server. Try '\\r\\n' first, and if the data didn't import correctly, then try '\\n'.

 

Hope this helps...

Share this post


Link to post
Share on other sites

Thank you very much David! The magic worked! This is cool - I learned something today - thanks a lot, now I have better understanding of it :group:

I had to use \n instead of \r\n - thanks to you I know why :) Thank you, thank you, thank you :) MySQL doc is quite compicated for a newby like me.

Share this post


Link to post
Share on other sites

I'm having the same problem in a PERL CGI script. I thought for sure that this post would solve my problem but unfortunately, no.

 

Will someone give me a pointer? It seems I'm very close to getting this to work.

 

Right now I'm just trying to get the command to work in phpMyAdmin but keep getting an error. I've tried sooo many combinations with the command.

 

Here's one of the commands I thought would work from within phpMyAdmin:

>LOAD DATA LOCAL INFILE '/home/<username>/public_html/cgi-bin/formdata.txt' INTO TABLE registered FIELDS TERMINATED BY ';' LINES TERMINATED BY '\\r\\n'

 

Here's the error:

>#13 - File '/home/<username>/public_html/cgi-bin/formdata.txt' not found (Errcode: 13)

 

I've also tried it with the .txt file in the root directory, not the cgi-bin. .txt Files have full access CHMOD 777

Share this post


Link to post
Share on other sites

Yep, all lowercase on the file name.

 

 

I'm really stumped. Maybe something in the line needs to be escaped? I've tried so many things.

Share this post


Link to post
Share on other sites

Based on my testing, it appears that phpMyAdmin is only allowing files to be imported with "LOAD DATA LOCAL INFILE" from specific directories (and the directories in your account aren't among them). The "Errcode: 13" is actually a "permission denied" error, not an actual "file not found" error, and it looks to me that it's something in phpMyAdmin's configuration that is causing the permission denial.

 

You said you were working on a CGI script. I think your best bet would be to post relevant code from the script rather than trying to get the query to work in phpMyAdmin. I am able to get "LOAD DATA LOCAL INFILE" queries to work in both PHP and CGI scripts, but those same queries fail in phpMyAdmin with the exact same error message you're seeing.

 

Here's a quick and dirty example of how a CGI script could do a LOAD DATA LOCAL INFILE query:

>#!/usr/bin/perl

use DBI;

print "Content-Type: text/plain\n\n";

my $dsn = "DBI:mysql:" .
"database=cpanelName_dbName;" .
"host=localhost;" .
"port=3306";

my $dbh = DBI->connect($dsn, 'cpanelName_dbUserName', 'dbPassword')
or die $DBI::errstr;

my $qry = "LOAD DATA LOCAL INFILE '/home/cpanelName/public_html/cgi-bin/formdata.txt' INTO TABLE registered FIELDS TERMINATED BY ';' LINES TERMINATED BY '\\r\\n'";
my $rows = $dbh->do($qry) or die $dbh->errstr;

print "\nDone!\n";

With the above code, your formdata.txt file only needs to have full read permissions (0644) - it does not need (and should not have) 0777 permissions.

Edited by TweezerMan

Share this post


Link to post
Share on other sites

Thank you, Thank you, Thank you!

 

I had tried so many combos that I stopped using the CGI script and just used phpMyAdmin to perform the testing. I was trying to narrow down the problem but, anyway... it's working great now with the SQL command string you posted above. Thanks so much. You da man, TweezerMan.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...