I'll give you an example, step-by-step.
I didn't do that kind of thing myself yet, so I had a look at the
manual. First, I discovered that mysql can take all kinds of
separators (I read that before, but forgot about it), so you
don't have to convert at all, as you can signal different
separators and delimiters to mysql.
To get a text file in the first place, I issued the following
command:
SELECT * INTO OUTFILE "result.txt"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\\n"
FROM autoren;
Here you see already that you can specify exactly how you want
the records to be written. The result looked like this:
1,"Werner Stürenburg","[EMAIL PROTECTED]"
2,"Priv. Doz. Dr. med. Th. Rommel",""
3,"Regina Steinkrauss","[EMAIL PROTECTED]"
4,"Uta Over","[EMAIL PROTECTED]"
5,"Paula da Silva","[EMAIL PROTECTED]"
6,"Friedhelm Splett","[EMAIL PROTECTED]"
7,"Kathrin und Eva Astl","[EMAIL PROTECTED]"
8,"Heidi Grieder Ulrich","[EMAIL PROTECTED]"
9,"Lena Scherling","[EMAIL PROTECTED]"
10,"Hardy Oelke","[EMAIL PROTECTED]"
11,"Elizabeth Wener",""
12,"Marianne Schwöbel",""
13,"Merten-Melching",""
14,"Dr. Otto Marré",""
15,"Taschen Verlag","[EMAIL PROTECTED]"
Now let's reverse the process. I used phpMyAdmin to copy the
table autoren to autoren2, structure only (enter name + click).
Now I issued the command
LOAD DATA INFILE 'result3.txt' INTO TABLE autoren2
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\\n";
again from phpMyAdmin. This populated the new table with those
values. Both tables are identical.
I did all this on my local Win system. If you do it on your
server, there are differences in syntax regarding the location of
your text file, residing either on your local machine or the
server. The latter is faster, as the data doesn't have to travel
across the phone line (it had to earlier to get there in the first
place), but you need the FILE priviledge in the user table to be
able to do that.
So if your text file is very large, I'd suggest you compress it,
transfer it to the server, decompress it and load from there -
make sure you have the file priviledge or else give it to
yourself in this case.
The advantage of this approach is that it is straightforward and
as fast as can be.
Kit Kerbel schrieb am Sonntag, 29. Juli 2001, 23:57:26:
> I'm sorry, I'm kind of a newbie at what you are discussing about converting
> to csv files. Could you explain the advantage of doing this as opposed to
> other ways, if any. Thanks.
> ----Original Message Follows----
> From: Werner Stuerenburg <[EMAIL PROTECTED]>
> Reply-To: Werner Stuerenburg <[EMAIL PROTECTED]>
> To: "Kit Kerbel" <[EMAIL PROTECTED]>
> CC: [EMAIL PROTECTED]
> Subject: Re: Parsing text file into mysql database.
> Date: Sun, 29 Jul 2001 22:49:45 +0200
> filter: mysql
> I don't have the original post any more, but you will have some
> kind of file where the records are lines and the fileds in the
> record are separated by some kind of character, say tab or blank
> or anything else, but you will know what.
> So let's say the filename is $filename and the separator is $sep.
> Then you read the content of the file into an array with
> $content = file($filename);
> Now the elements of the array are the records. You will have to
> convert the separator into , to get csv style. There are several
> styles to do that, for example
> while(list($key, $val) = each($comment)) {
> $content[$key] = str_replace($sep, ',', $val);
> }
> Now $content should be written to a file to feed to mysql. Well,
> this is a common task, and there isn't a handy php function as
> far as I know, so I wrote it myself (see below). With that
> function, I don't have to think about all that mumbo jumbo and
> get it right nevertheless:
> stringToFile(implode("\n", $content), $filename_csv);
> The implode function transforms the array to a string which can
> be written to the file, glueing the rows together with \n (new
> line) in this case.
> function stringToFile($str, $filename, $mode="a"){
> $fp = fopen($filename,$mode);
> $res = fwrite($fp, $str);
> fclose($fp);
> return $res;
> }
> Of course, I have written the reverse function, too:
> function fileToString($filename, $sep=' ') {
> return @implode($sep, @file($filename));
> }
> The @ insures that no error messages are shown. So this function
> will return nothing if the file does not exist.
> Kit Kerbel schrieb am Sonntag, 29. Juli 2001, 21:33:16:
> > I'm the original poster of the "parsing text file into mysql database"
> note.
> > I am in need of some extra assistance on this topic. I kinda know
> what's
> > going on here, but just need to see it to understand it, you know? So if
> > you could, extra assistance would be greatly appreciated.
> > Thanks,
> > Kit
> > ----Original Message Follows----
> > From: Werner Stuerenburg <[EMAIL PROTECTED]>
> > Reply-To: Werner Stuerenburg <[EMAIL PROTECTED]>
> > To: Kit Kerbel <[EMAIL PROTECTED]>, [EMAIL PROTECTED]
> > Subject: Re: Parsing text file into mysql database.
> > Date: Sun, 29 Jul 2001 19:00:21 +0200
> > > Unfortunately I am no PHP expert but rather familiar with Perl but I
> > don't
> > Why don't you convert your textfile to a csv file with
> > str_replace() and then import it the normal way - both operations
> > are a snap. If you need further assistance, give me a note.
> > --
> > Herzlich
> > Werner Stuerenburg
> > _________________________________________________
> > ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
> > Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
> > http://pferdezeitung.de
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
> --
> Herzlich
> Werner Stuerenburg
> _________________________________________________
> ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
> Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
> http://pferdezeitung.de
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Herzlich
Werner Stuerenburg
_________________________________________________
ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen
Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409
http://pferdezeitung.de
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
