List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 25 1999 8:33pm
Subject:Re: Load Data Problem
View as plain text  
At 12:28 PM -0700 6/25/99, Creative Digital Publishing Inc. wrote:
>Looking thru the LOAD DATA documentation, it looks like I have to figure
>that out and the priviledge system, and then see if our hosted server is
>set up properly in order to bulk load records. Before I do all that, can
>anyone suggest the easiest way to load in a bunch of data into a table? Any
>caveats and special requirements would  be helpful. Thanks.

Since you don't have LOCAL for your LOAD DATA, you would need the file
privilege on the server.  That's not something I'd be anxious to give out,
where I the admin -- no offense. :-)

If your data isn't something really weird, you could run it through a
Perl script that generates a bunch of INSERT statements, then run the
output into mysql.  Something like this:

#! /usr/bin/perl -w

# Script type: Perl5

# mkinsert

# Generate INSERT statements from tab-delimited data, suitable for feeding
# to mysql.  Useful if you have a file of data but an old version of MySQL
# that doesn't support LOAD DATA LOCAL or mysqlimport --local.

# Use mkinsert like this:

# % mkinsert -t tbl_name data_file | mysql db_name

# or:

# % mkinsert -t tbl_name data_file > junk
#	<look at junk to see that it's what you want>
# % mysql db_name < junk

# This assumes the fields are in the proper order and that you don't want
# load just a subset of the fields.

# Also converts \N to NULL.

# Paul DuBois
# dubois@stripped
# 1999-06-25

# 1999-06-25
# - Created.

use strict;

use vars qw($opt_t);
use Getopt::Std;

my ($prog, $usage);

($prog = $0) =~ s|.*/||;		# get script name for messages

$usage = "Usage: $prog -t tbl_name [ data-file ] ...";

getopts ("t:") or die "$usage\n";
defined ($opt_t) or die "$usage\n";

my ($tbl_name) = $opt_t;
my (@f);
my ($delim);

print "# feed the following output to mysql db_name\n\n";

while (<>)
	@f = split (/\t/, $_);
	print "INSERT $tbl_name VALUES(";
	$delim = "";
	foreach my $val (@f)
		if ($val eq "\\N")
			$val = "NULL";
			$val =~ s/\\/\\\\/g;
			$val =~ s/'/\\'/g;
			$val = "'$val'";
		print "$delim$val";
		$delim = ",";
	print ");\n";

exit (0);

Paul DuBois, paul@stripped
Northern League Chronicles:
Load Data ProblemCreative Digital Publishing Inc.25 Jun
  • Re: Load Data ProblemPaul DuBois25 Jun
    • Re: Load Data ProblemCreative Digital Publishing Inc.25 Jun
    • Re: Load Data ProblemCreative Digital Publishing Inc.25 Jun
      • Re: Load Data ProblemPaul DuBois26 Jun
Re: Load Data ProblemKevin Smith25 Jun