Forum Moderators: coopster
now I would like to insert data from a textfile, preferably with myphpAdmin, which seems to work really well.
does anyone have a good tutorial or any tips?
tried google, but the results are meager ...
e.g. I get the following mssage:
"Inserted rows: 24 (Query took 0.0010 sec)
LOAD DATA LOCAL INFILE '/tmp/phpxLGxMB' REPLACE INTO TABLE `test`
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n' "
but only 4 rows are actually in the table and in the text file there were 12 (not 24 and not 4)
seems to me like some sort of syntax error, but I cannot figure out what to look for ...
[edited by: mifi601 at 9:54 pm (utc) on May 14, 2004]
take a look at what got inserted and what didn't
check to see if data was chopped in the middle
that may give you some clues as to what is blowing it out
it seems the rows it takes are arbitrary. if it were the same ones all the time I could look even closer for a pattern, BUT ..
ok ok I'll look again
FIELDS TERMINATED BY ';'
all the individual fields have a semi colon after them
OPTIONALLY ENCLOSED BY '"'
so char and varchar fields have quotes around them and others don't
ESCAPED BY '\\'
all double quotes inside the fields are escaped by \\
LINES TERMINATED BY '\r\n' "
each line ends in \r\n
try just using
LINES TERMINATED BY '\n' "
and see if that makes a difference
took out the optional "" and it took 12 rows.
what settings should the PRIMARY KEY and the UNIQUE have in the place where varchar etc is?
the text file was an export from an access db ... so the they didn't come with the "".
maybe I should try exporting the whole thing without them?
what are your preferences? I am used to writing into files and using them with \t for field separators ...
what's the 'standard', meaning the least likely to have problems?
I DID try \n instead of \r\n since it was an exported file to begin with, but it did not make a difference.
I would say comma or tab between fields is the most common
but having the quotes when you export from access is an option you can do, is it not?
you can always just go with the defaults
[dev.mysql.com...]
f you specify a FIELDS clause, each of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) is also optional, except that you must specify at least one of them.If you don't specify a FIELDS clause, the defaults are the same as if you had written this:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
If you don't specify a LINES clause, the default is the same as if you had written this:
LINES TERMINATED BY '\n' STARTING BY ''
In other words, the defaults cause LOAD DATA INFILE to act as follows when reading input:
* Look for line boundaries at newlines.
* Do not skip over any line prefix.
* Break lines into fields at tabs.
* Do not expect fields to be enclosed within any quoting characters.
* Interpret occurrences of tab, newline, or `\' preceded by `\' as literal characters that are part of field values.
Note: If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r.
the culprit was the unique identifier.
since the txt file was exported from an access table, the unique id was not that unique and it wrote only 4 out of the 12.
what I still do not understand is why it said, 'inserted 24 rows'?
I have no idea why it said that
anyway thank you again - i'll be back :)
