Page 1 of 1

Entering a load of data troubles.

Posted: Tue Nov 16, 2004 2:38 pm
by davidjwest
I'm wanting to add a load of data into my database and would appreciate advice as to the best way of doing it.

I've RTFM and it says this is one option:
Adding a bunch of data
If you have a large amount of data to enter and it’s already in a computer file,
you can transfer the data from the existing computer file to your MySQL database.
The SQL query that reads data from a text file is LOAD. The LOAD query
requires you to specify a database.
Because data in a database is organized in rows and columns, the text file
being read must indicate where the data for each column begins and ends
and where the end of a row is. To indicate columns, a specific character separates
the data for each column. By default, MySQL looks for a tab character
to separate the fields. However, if a tab doesn’t work for your data file, you
can choose a different character to separate the fields and tell MySQL in the
query that a different character than the tab separates the fields. Also by
default, the end of a line is expected to be the end of a row — although you
can choose a character to indicate the end of a line if you need to. A data file
for the Pet table might look like this:
80 Part II: MySQL Database
Unicorn<TAB>horse<TAB>Spiral horn<Tab>5000.00<Tab>/pix/unicorn.jpg
Pegasus<TAB>horse<TAB>Winged<Tab>8000.00<Tab>/pix/pegasus.jpg
Lion<TAB>cat<TAB>Large; Mane on neck<Tab>2000.00<Tab>/pix/lion.jpg
A data file with tabs between the fields is a tab-delimited file. Another common
format is a comma-delimited file, where commas separate the fields. If your
data is in another file format, you need to convert it into a delimited file.
To convert data in another file format into a delimited file, check the manual
for that software or talk to your local expert who understands the data’s current
format. Many programs, such as Excel, Access, or Oracle, allow you to
output the data into a delimited file. For a text file, you might be able to convert
it to delimited format by using the search-and-replace function of an
editor or word processor. For a truly troublesome file, you might need to seek
the help of an expert or a programmer.
The basic form of the LOAD query is
LOAD DATA INFILE “datafilename” INTO TABLE tablename
Except the LOAD DATA INFILE line doesn't work, any ideas? This is the error message:
Parse error: parse error, unexpected T_STRING in /home/qhwslos/public_html/testdatabase.php on line 13
And the line of code is points to: (the first line shown)

Code: Select all

LOAD DATA INFILE "test" INTO TABLE test;
mysql_query($query) or die (mysql_error());
mysql_close();
If there's a better way then let me know!

Thanks!

Re: Entering a load of data troubles.

Posted: Tue Nov 16, 2004 3:00 pm
by timvw
Parse error: parse error, unexpected T_STRING in /home/qhwslos/public_html/testdatabase.php on line 13

Code: Select all

$query = 'LOAD DATA INFILE "test" INTO TABLE test';
mysql_query($query) or die (mysql_error());
mysql_close();
but don't know where the cwd for the mysql lib is.

i usually do it through sheel

Posted: Tue Nov 16, 2004 4:13 pm
by davidjwest
Thanks timvw, that seems to have helped a bit but now I get
Access denied for user: 'qhwslos_pitstop@localhost' (Using password: YES)
I have full permissions for the database, not sure why this is appearing, any ideas?

It's strange as when I comment out this line

Code: Select all

//$query = 'LOAD DATA INFILE "test" INTO TABLE test';
//mysql_query($query) or die (mysql_error());
The error goes away, so do you need special permissions for this type of query or is it a problem at my host perhaps?

The file I'm using is a text file, but I've saved it as test with no extension and it's just a load of data with tabs to seperate it all, saved in the same dir as the php executable.

You mentioned some other stuff "sheel", what's this and is it a better way than what I'm doing?

Posted: Tue Nov 16, 2004 5:46 pm
by timvw
was a type, i wanted to say: "(unix) shell"

you might want to add a

Code: Select all

mysql_connect("localhost", "user", "pass");
mysql_select_db("databasename");
before you perform the query... this way you won't login as the default user.

Posted: Tue Nov 16, 2004 5:49 pm
by davidjwest
I already did that, I'm not that much of a newbie!

:D

I'm going to have a word with my host , will let you know!

Posted: Wed Nov 17, 2004 7:38 am
by hairyjim
try using navicat, you get a 30 day free trial of the software which was enouhg time for me to transfer over 1000 records into a MySQL table.

It has a nice import functionality

http://www.navicat.com

Posted: Wed Nov 17, 2004 8:51 am
by CoderGoblin
I'm not a MySQL person but what are the permissions on the table you are loading the information into.

Normally in Postgres I create the tables using the admin. The Webaccess has to be granted access to each table I use.

Posted: Wed Nov 17, 2004 1:36 pm
by davidjwest
I have full permissions on the database, I can create tables using code without problems.

However the list of "full" permissions doesn't mention the LOAD INFILE command so maybe that's it.

I'll check and let you know!

I've found a workaround for now, which is a bit tedious but nevermind.

Posted: Wed Nov 17, 2004 5:14 pm
by timvw
if you have the mysql client available, you could try:

Code: Select all

system("mysql -u $user -p$password -h$host < $file");