If you want to store your data somewhere, rather than sending it directly to the other DBMS, here's a general purpose dump script.

It exports data in SQL format, so that it could be later given to any DBMS that supports ANSI SQL standards.

#!/usr/bin/perl -w
# dump.pl
# simple SQL table dump
use strict;
use DBI;

my ($db, $table) = @ARGV;
$table or die "syntax: dump database table\n";
my $driver = 'Your DBD driver name here';

my $dbh = DBI->connect("dbi:$driver:$db",
    'myusername',
    'mypassword',
    {RaiseError=>1})
   or die "Can't open: $DBI::errstr\n";

my $sth= $dbh->prepare(qq{select * from $table});

$sth->execute();
my $fields = join "," , @{$sth->{NAME}};

my $insert_statement = qq{INSERT INTO $table ($fields)\n VALUES (};

while (my $row = $sth->fetchrow_arrayref()) {
    print $insert_statement,
          join( ",", map( {$dbh->quote($_)} @$row)),
          ");\n";
}
$dbh->disconnect;
__END__
sample output using this "department" table
+-----------+-------------+------------+
| dept_code | dept        | sort_order |
+-----------+-------------+------------+
|         1 | Personnel   |          5 |
|         2 | Training    |          2 |
|         3 | Research    |          4 |
|         4 | Sales       |          1 |
|         5 | Development |          3 |
+-----------+-------------+------------+

INSERT INTO department (dept_code,dept,sort_order) 
 VALUES ('1','Personnel','5');
INSERT INTO department (dept_code,dept,sort_order) 
 VALUES ('2','Training','2');
INSERT INTO department (dept_code,dept,sort_order) 
 VALUES ('3','Research','4');
INSERT INTO department (dept_code,dept,sort_order) 
 VALUES ('4','Sales','1');
INSERT INTO department (dept_code,dept,sort_order) 
 VALUES ('5','Development','3');
[download]

while (my $row = $sth->fetchrow_arrayref()) {
    print  join( ",", map( {"\"$_\""} @$row)), "\n";
}
[download]

To create a tab separated file, use

print join("\t", @$row),"\n";
[download]

You get the idea.

The script is rather simple, but you could easily customize it to suit your exporting needs.
For example, to quote the values for a specific DBMS, you may connect to it and use its DB handler "quote" method.

 _  _ _  _  
(_|| | |(_|><
 _|
[download]

Hi Gmax,

Thanks for the dump script. I'll play with it and see what I get.

Hi Gmax,

I tried your general dump of my data and I'm receiving an Internal Server Error.

I would start by looking at DBI. Then make sure the database the information is in has a DBD::(your database here). Then a script can be written to extract the fields and with perl it can then be mangled as desired and output.

One option you didn't mention and may not have considered is that with DBI you can open 2 databases at once and read records out of one while writing the data into the other one. The databases of course can be completely different types

For example for Oracle to PostgreSQL you might go along these lines

use DBI;

my $pgh=DBI->connect("DSN for Postgres here");
my $orh=DBI->connect("DSN for Oracle here");

#set up statments into variables for both
eval{
  my $psh=$pgh->prepare($write_stmt);
  my $osh=$orh->prepare($read_stmt);
  $osh->execute( variable list here );
  $osh->bind_columns( \( $f1, $f2, $f3, ... ) );
  while($osh->fetch)
  {
    $psh->execute( $f1, $f2, $f3, ... );
  }
  $pgh->commit;
}
if($@)  # error handler goes here
{
  $pgh->rollback;
  print STDERR "Problem saving data\n";
}
[download]

Update: Fixed a type $pgh->execute should be $psh->execute. ie. use the statement handle not the DB handle for execute.

Hi Dga,

I was not aware that two databases can be open.

Would your suggestion work if one table that contains the data is on my hosting server provider while the database to which I want to export is located on my hard drive in Access?

If you can provide a DSN suitable to open the connection from your local machine, you could use that database. If you have shell access at your provider then you may be able to use the DBD::Proxy modules to open a connection locally there and proxy it out so you can see if from your local machine. I would be sure to do this over a secure channel ie. ssh or the encrypting variations of the proxy and make sure that no one else can use the proxy otherwise you open up a connection for anyone to query your data. I believe the proxy modules can be set up to allow read only access which would mitigate the risk to a large degree on the remote end

<remote-db> - <DBD::(rem db)> - <DBD::Proxy> #remote -> #local <DBD::Proxy> - <DBD::Access> - <Access>

The connection between the Proxy modules being read only from external. The Proxy's can have an encryption set up between them etc.

Hi Clairudjinn,

The online article you gave me was a big help. I was able to successfully export my data into a text file.

Thank you very much.

Yes. But what type of table, RDBMS? If so, what database? Oracle? Sybase? SqlServer? You can start with DBI and it's associated DBD modules and then move on the DBIx for higher level wrappers.

-derby

Hi Derby,

The type of table I'm using is RDBMS. I plan to use the exported data in Microsoft Access.

I'm not sure how to create the file so I can make the export using Perl?

SQL> spool table_save.txt
SQL> select * from table_to_save;
SQL> spool off
[download]

Not a perl solution. Just create an ODBC connection to your target database and export from Access to your target via 'External Data'->'Export'->'More'->'ODBC Connection'.

Here is some code I used once to create CSV output from a list of tables. I had to add character counting so that output files were no greater that 1GB. If you remove that IF stmt, the loop will be very simple. $sth2 is simply "select * from some_table"

But you will need the DBD and DBI for this code to be useful.

   my @row;
   my $sth2 = $dbh->prepare( $statement );

   ### Execute the statement in the database
   $sth2->execute
       or die "Can't execute SQL statement: $DBI::errstr\n";

   my $file_len = 0;
   ### Retrieve the returned rows of data
   while ( @row = $sth2->fetchrow_array() ) {
        if ($file_len + 1 + length( $row[0] ) > 1073741824) {# break file at 1GB
          $file_len = 0;
          close (FINAL);
          $fn++;
          $filename = sprintf("%s_%03d.csv", ${table_name}, ${fn});
          $pathname = "$list_path/$filename";
          open (FINAL, ">$pathname") || die "Can't open $pathname for output";
          }
        $file_len += length( $row[0] );
        $file_len++;
        $rowctr++;
        print FINAL "@row\n";
        }

   warn "Data fetching terminated early by error: $DBI::errstr\n"
      if $DBI::err;

 close (FINAL);