moving tab delimited file into MySQL

keep getting an error

[Trisha]

I'm trying to move a tab delimited text file in a MySQL table using phpMyAdmin. This is my first time to try something like this.

For "Fields terminated by" I put: \t - which I'm guessing is a tab.

"Fields enclosed by" I left blank

"Fields escaped by" I left blank

"Lines terminated by" I put : \r\n

and got the following error:

Error
SQL-query :

LOAD DATA LOCAL INFILE '/tmp/phpoQ2G7Q' INTO TABLE `my_table` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'

MySQL said:
The used command is not allowed with this MySQL version

Am I doing something wrong with the escaped by or terminated fields? Or is it something else entirely?

[Trisha]

I was looking through the mysql.com site and it appears that sometimes the command "LOAD DATA LOCAL" is disabled by hosting companies for security reasons.

So what are my options? We don't have shell access either because the server was recently broke into that way.

The file is a data feed from a merchant. This is my first time to use one and bringing it into MySQL seems like the best way to make use of it. What does everyone else do?

[Trisha]

Digging a little deeper I found information about "mysqlimport" at:
[mysql.com...]

Am I understanding this correct:

mysqlimport provides a command-line interface to the LOAD DATA INFILE SQL statement.

It sounds to me that "mysqlimport" can only be used through a command line?

[bcolflesh]

Does your server have a version of phpMyAdmin installed for your account? You can use it's GUI to import the file - if it won't do it remotely, you may have to copy the file to the server first.

Regards,
Brent

[Trisha]

I'm using phpMyAdmin, that's where I'm getting the error. I thought about putting the file on the server, but when I click on the 'browse' button to locate the file, the only options displayed in the drop down box are on my computer, not the server.

Another possible problem could be that my host has version 2.2.4 of phpMyAdmin installed. Looking through the phpMyAdmin site I see that 2.4 was released and they list one of the fixes for it as "handling of "LOCAL" in recent MySQL versions for LOAD DATA INFILE". I don't really understand what that means however.

[bcolflesh]

Install phpMyAdmin on your machine, import the file, then export the results as an .sql file - cut and paste the contents into the remote "SQL" screen & click OK.

Regards,
Brent

[Trisha]

Thanks, I hope I can find another solution though. I don't even have php installed on my local computer, so I would have to install both. My computer is old and not functioning real well so I try to avoid installing anything new if I can. Knowing me I'd probably run into all sorts of problems trying to install them too.

I sent an email to my host, maybe he can help.

[Trisha]

I heard back from my host and he confirmed it:
"This command is disabled for security reasons. In order to load a delimited file, you'll need to use a PHP or other script to read and import the data."

If the command, 'load data' is disabled, I wouldn't think you could use it in a php script either. I asked him this, but haven't heard back yet.

In general, how do you read and import data through a script? With load data? mysqlimport? Anyone know?

[jatar_k]

1. upload the csv file as is to a directory on the server.

2. use fopen [php.net] to open a file file pointer to the csv file.

3. use fgetcsv [php.net] to read it line by line in the script.

4. use mysql functions [php.net] to insert your values into your table.

[Trisha]

Thanks, I'll work on doing it that way. Sorry I didn't reply back sooner, I wasn't feeling too well for a few days and wasn't on the computer too much.