List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 4 2001 1:37am
Subject:RE: specifying a delimiter
View as plain text  
>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
# paul@stripped
# 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, paul@stripped
Thread
MySQL can't open mysql.sock on startChad Nantais2 May
  • Re: MySQL can't open mysql.sock on startGerald Clark2 May
    • specifying a delimiteradmin2 May
      • Re: specifying a delimiterPaul DuBois3 May
        • Re: specifying a delimiterPaul DuBois3 May
          • RE: specifying a delimiteradmin4 May
            • RE: specifying a delimiterPaul DuBois4 May
              • space in column name and table name, standard?.Mohamad Ilhami4 May
                • Re: space in column name and table name, standard?.Rolf Hopkins4 May
                • Re: space in column name and table name, standard?.Van4 May
            • RE: specifying a delimiterDon Read4 May