>Hi Paul,
>Thanks for the help. I was hoping to accomplish this with MySQl, but in the
>end a script works just as good and this is just the first of several I am
>using. For what it's worth I ended up using sed. Here is is if somone else
>could use it:
>sed "s/ /|/g"
> ^This is a tab, not spaces..
>
>I am now confronted with another problem. I need to also provide a CSV
>version.
>I need to use , seperated and " protected text. I have a copy of phpMyAdmin
>that will
>export to this format, but I need to use this at the shell.
>I haven't yet dug into the script to se if this done with PHP or MySQL. I am
>thinking it is probably done with PHP.
>I noticed a mention of MySQL being able to set a delim in the docs but alas
>they never actually say how.
>Finally, I considered using awk for this but I am running into a problem
>there as well
>since "'s are used to protect text. For instance
>awk '{print """$1""","""$2"""} would print the fields correctly if not for
>the " issue.
>
>A shove in the right direction would sure be appreciated.
>Thanks,
>-Eric
I guess in this case, I'd probably use a Perl DBI script and combine
it with a CSV module to do CSV export. Something like what's shown
below. You can use it, uh ... let's see, like this:
% mysql_csv_export.pl db_name.tbl_name
#! /usr/bin/perl -w
# mysql_csv_export.pl - generate CSV- or merge-format output from a query
# Usage: mysql_csv_export.pl [ options ] > csv_data_file
# Paul DuBois
# [EMAIL PROTECTED]
# 2000-12-28
#@ SETUP
use strict;
use DBI;
use Text::CSV_XS;
use Getopt::Long;
$Getopt::Long::ignorecase = 0; # options are case sensitive
$Getopt::Long::bundling = 1; # allow short options to be bundled
# default connection parameters - all missing
my ($host_name, $password, $user_name, $db_name)
= (undef, undef, undef, undef);
my ($query, $tbl_name) = (undef, undef);
my ($merge) = undef;
GetOptions (
# =s means a string argument is required after the option
# :s means a string argument is optional after the option
"host|h=s" => \$host_name, # MySQL server host
"password|p:s" => \$password, # MySQL password
"user|u=s" => \$user_name, # MySQL user name
"execute|e=s" => \$query, # query to execute
"table|t=s" => \$tbl_name, # table to export
"merge|m" => \$merge #
generate merge format
) or exit (1); # no error message needed; GetOptions() prints its own
$db_name = shift (@ARGV) if @ARGV;
die "You must specify a query or a table name\n"
if !defined ($query) && !defined ($tbl_name);
die "You cannot specify both a query and a table name\n"
if defined ($query) && defined ($tbl_name);
# If table name was given, expand it to a query that selects entire table
$query = "SELECT * FROM $tbl_name" if defined ($tbl_name);
$merge = defined ($merge);
#@ SETUP
# solicit password if option specified without option value
if (defined ($password) && !$password)
{
# turn off echoing but don't interfere with STDIN
open (TTY, "/dev/tty") or die "Cannot open terminal\n";
system ("stty -echo < /dev/tty");
print STDERR "Enter password: ";
chomp ($password = <TTY>);
system ("stty echo < /dev/tty");
close (TTY);
print STDERR "\n";
}
#@ CONNECT
# Construct data source name and connect to the server. The database
# name comes from the command line. Client connection parameters are
# assumed to be found in one of the standard option files.
my $dsn = "DBI:mysql:";
$dsn .= ";database=$db_name" if $db_name;
$dsn .= ";host=$host_name" if $host_name;
# read client parameters from standard option files
$dsn .= ";mysql_read_default_group=client";
my $dbh = DBI->connect ($dsn, $user_name, $password, {RaiseError => 1});
#@ CONNECT
# By specifying the eol character, $csv will add newlines for us automatically
#@ CREATE_CSV
my ($csv) = Text::CSV_XS->new ({ eol => "\n" });
#@ CREATE_CSV
# Run query to dump all data contained in the table. The first line of
# output must list the names of the columns. These are available in the
# array pointed to by $sth->{NAME} after calling execute().
#@ EXECUTE_AND_PRINT_COLUMN_NAMES
my $sth = $dbh->prepare ($query);
$sth->execute ();
if ($merge) # write line of
column names for merge format
{
$csv->combine (@{$sth->{NAME}}) or die "cannot process column names\n";
print $csv->string ();
}
#@ EXECUTE_AND_PRINT_COLUMN_NAMES
#@ EXTRACT
my $count = 0;
while (my @val = $sth->fetchrow_array ())
{
++$count;
$csv->combine (@val)
or die "cannot process column values, row $count\n";
print $csv->string ();
}
#@ EXTRACT
$sth->finish ();
$dbh->disconnect ();
exit (0);
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
