Import txt-file to mysql DB

[Sigi05]

Hello,

first of all, i want to say that i am new to sql-programming, so this might be an easy one for you.
On the one hand i have a mysql-table called "news". It has 4 fields, "uid" (with auto_increment attribute), "headline", "text", "author". On the other hand i have a txt-file in the following format.
headline|text|author
This file contains of several lines, all in the same format.
Is it possible to import this file to my database? I need the "headline" from the txt-file in my "headline" field and so on.. The uid-field has the attribute auto-increment, so i think i must not care about it, right? I tried some things but they didn't work..
Would be nice if you could help me out. Thx very much.

Sigi

 

[Bastien]

use phpmyadmin to load the file. very simple to do.

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[TonyGroves]

You could use LOAD DATA INFILE:
[tt]
LOAD DATA INFILE 'filepath'
INTO TABLE news
FIELDS TERMINATED BY '|'
(headline,text,author)
[/tt]

 

[dunamin]

A Newbie? Welcome to the wonderful world of MySQL. There are several ways of importing text into MySQL. The LOAD DATE INFILE mentioned above is a great way to do it. However, if you're new, you are may be wondering how to give SQL commands to the database. There's a couple of ways. First, type mysql at the command line. It will log you in to the mysql monitor where you can type the above command. (Don't forget to end all lines with a semi-colon)

I prefer to use the Query Browser (available at mysql.com). It is a GUI application that supports the execution of SQL scripts. Just create a new script window and type in the command.

HTH

Randy Solomonson

Randy Solomonson
Contract Programmer

 

[Sigi05]

First of all, thx for all your answers. I'm using phpMyAdmin atm and was trying the LOAD DATA step.
I typed

LOAD DATA INFILE '/home/sigi/tmp/sql.txt'
INTO TABLE tt_news
FIELDS TERMINATED BY '|'
(headline,text,author)

into the SQL-section of PhpMyAdmin and uploadad the file with the txt-file the the right location.
But i got the following error:

#1045 - Access denied for user: 'sigi@localhost' (Using password: YES)

Why does this happen? I can create the content trough the Insert-Tab of phpMyAdmin, but not if I'm using the LOAD DATA INFILE...
Do you have an idea why this happens?

 

[dunamin]

It sounds like a permissions thing. For the import, try adding a few more permissions to that user. You can do this through phpmyadmin under privileges. Otherwise try doing it as root.

It may also have something to do with file permissions on sql.txt. Try placing the file in / and change the permissions to 777 (chmod 777 sql.txt).

Randy Solomonson
Contract Programmer

 

[TonyGroves]

If your data file is located on your client machine instead of on the server, then you need to use "LOAD DATA LOCAL INFILE ...".

 

[Sigi05]

Great guys, it worked. Thx very much to all!
The following command was the right one

LOAD DATA LOCAL INFILE '/home/sigi/tmp/sql.txt'
INTO TABLE tt_news
FIELDS TERMINATED BY '|'
(headline,text,author)

Thx again for helping me!