Perl script for csv

[Neyland]

I have a number of csv files generated from the admincp->Maint section that I need to push back into a db. These files were generated with the column names, single quote, and comma as the delimiter.

Instead of using myphpadmin to do this one table at a time, I'd just like to write a perl script to loop through the directory contents and call the mysqlimport command passing it the filename.

I'm a relative newb to perl syntax so a little review on that would be helpful, but I need to know what values to pass for the mysqlimport switches --fields-optionally-enclosed-by and --fields-escaped-by.

#!/usr/bin/perl -w
use strict;
 
my $dir = '/home/DIRNAME/import';
my $dbname = 'MYDBNAME';
my $dbuname = 'DBLOGINNAME';
my $dbpswd = 'DBPASSWORD';
my $importfilename;
 
opendir(DIR, $dir) or die $!;
 
while (my $file = readdir(DIR)) {
    # No Directories
    next unless (-f "$dir/$file");
 
    # Look for files ending in .csv
    next unless ($file =~ m/\.csv$/);
    
    # Provide full path to file we want to import
    $importfilename = "$dir/$file"
  
  # Call mysqlimport
   system('mysqlimport --delete --fields-terminated-by=\',\' --ignore-lines=1 -u $dbuname -p $dbpswd $dbname $importfilename');
}

closedir(DIR);
exit 0;

Thanks!

[Marco van Herwaarden]

Originally Posted by Neyland

I have a number of csv files generated from the admincp->Maint section that I need to push back into a db. These files were generated with the column names, single quote, and comma as the delimiter.

Can you explain this a bit more, i am not aware of any generated CSV file from the Maintenance section of the AdminCP.

[Neyland]

AdminCP->Maintenance->Database Backup. Bottom of page "CSV backup of single database table".

I got it to work, for those wanting to know, dump the CSV files in a folder along with the attached script. You'll need to edit with your information and make it executable (chmod u+x importer.pl)

#!/usr/bin/perl -w
use strict;

my $dir = '/home/DOMAINNAME/DIRNAME';
my $dbname = 'DATABASENAME';
my $dbuname = 'DATABASE_LOGIN_NAME';
my $dbpswd = 'DATABASE_PASSWORD';
my $importfilename;
my $cmd;

opendir(DIR, $dir) or die $!;

while (my $file = readdir(DIR)) {
    # No Directories
    next unless (-f "$dir/$file");

    #File names should == table names!! ie access.csv for the access table
    # Look for files ending in .csv
    next unless ($file =~ m/\.csv$/);

    # Provide full path to file we want to import
    $importfilename = "$dir/$file";

   # Call mysqlimport
   # Imports CSVs exported with column names in first row, comma delimted, enclosed by quote
   $cmd ="mysqlimport --local --delete --fields-terminated-by=, --ignore-lines=1 --fields-enclosed-by=\\' --fields-escaped-by=\\\\ --user=$dbuname --password=$dbpswd $dbname $importfilename";

   system($cmd);
}

closedir(DIR);
exit 0;

[Marco van Herwaarden]

Okay i see now what you are doing.

Not sure why you have choosen to go the CSV route, that is not a format intended to be used as backup with the intention to restore into the database. Best to use the regular tables dump options (inside AdminCP or even better from SSH commandline).

[Neyland]

Oh I agree totally! I was in a situation where a forum owner got into a spat with the domain owner and didn't have keys to do a full backup and didn't have a writtable web folder before moving on. We even tried to do a VB backup to a writtable folder on a different web server and couldn't do it. The only thing he could do was download each table to csv. Because I ran a VB board myself he asked if I could help migrate him to a new host. So, we uploaded his software to his new host, ran the install, then I FTPd the csv files up, SSH'd in, ran the script and he was back in business.

But, what I found was there wasn't a whole lot of documentation on these csv files. I asked on the VB forums if they could tell me if the files were optionally enclosed or what the escape character was, but never got an answer. With trial and error I found the character you specify to enclose is a full enclose and not optional. This means integer fields are enclosed just like strings. The escape character is '\'.

I was just trying to throw this out there in hopes to save anyone some legwork if they happened to find themselves in a need to import the VB generated CSV files and had more than a few to do.

[Marco van Herwaarden]

It does make sense that not many people can give real answers on this. Most people will never use this or even know about it (lol, i forgot about it myself). In most cases a CSV file is only usefull when moving smaller amounts of data between 2 uncompatible systems and not to move from MySQL to MySQL. A lot (well all) of structural information is lost, same probably for all binary data.

Still think a better work around might have been possible, but if you managed to move the data, then i would not spend much time on it anymore unless you still have serious problems.

[cheesegrits]

I don't think there is any other workable solution in the position he was in, with an uncooperative host. The PHP backup isn't going to work for anything like large tables. Can't do an SQL dump because of having no writeable directory to put it in. Leaving only the CSV option, which lets you download as you go.

-- hugh