vb.org Archive - Perl script for csv

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - Perl script for csv (https://vborg.vbsupport.ru/showthread.php?t=170421)

Neyland

02-13-2008 07:47 PM

Perl script for csv

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.

Code:

#!/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

02-14-2008 07:19 AM

Quote:

Originally Posted by Neyland (Post 1442468)

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

02-15-2008 12:17 AM

1 Attachment(s)

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)

Code:

#!/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

02-15-2008 09:00 AM

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

02-15-2008 11:38 AM

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

02-15-2008 02:24 PM

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

02-15-2008 05:55 PM

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

All times are GMT. The time now is 01:35 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01440 seconds Memory Usage 1,732KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (2)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: