What I'd like to do is create a textfile and then import the whole thing into a table. So, naturally, I've a few questions.
Do I need to include the column heads as a line in my textfile, or can I simply go straight to the data?
No ... you can simply go straight to the data ..
The command to use for this is mysqlimport from the command line, or load data infile from a mysql session. Both commands would accept an option to specify the columns (according to the ordering of the columns in the text file) to be loaded. Look up mysql docs on both methods ...
Assuming, no column heads are necessary--because they're already there in the table I've previously created--I'm looking at something like this (the double quotes at the start of each line are deliberately empty because the first column is an auto_increment column):
No need for the double quotes to denote the auto_increment field, IMHO. You should just exclude the column, and use the parameter that tells mysqlimport or load data infile what columns you are loading ... the system will handle the auto_increment seemlessly as usual.
"", 'jack', 'sprat', '1 Main St', 'apt 2L', 'Anytown', 'NY', '10000', '20030912154545'The key thing is the field separation character. There is a parameter to tell the system what you have chosen as a field separation character, like this: --fields-separated-by="<char>" ... After using this, u need not worry about embedded spaces within the individual fields -- ie, no need to quote each field.
"", 'john', 'doe', '45 Hoover St', 'apt 8B', 'Anytown', 'NY', '10000', '20030912154555'
"", 'jane', 'smyth', '9 Fifth Ave', 'apt 2L', 'Anytown', 'NY', '10000', '20030912154615'
Now, do I put items in the single quotes, or is that only for when I'm typing stuff directly into the table via a command line situation? Do I separate entries using "\t"--no quotes, of course--without any spaces, commas, or tabs? And end each line with "\r"? (Or "\n"? Or both?) Like so:
""\t'jack'\t'sprat'\t'1 Main St'\t'apt 2L'\t'Anytown'\t'NY'\t'10000'\t'20030912154545'\r
"", 'john'\t'doe'\t'45 Hoover St'\t'apt 8B'\t'Anytown'\t'NY'\t'10000'\t'20030912154555'\r
"", 'jane'\t'smyth'\t'9 Fifth Ave'\t'apt 2L'\t'Anytown'\t'NY'\t'10000'\t'20030912154615'\r
For lines, as long as you have one row corresponding to one line, you need not bother. But there is also an option to tell the system the character(s) that mark the end of each line .
Look up the docs for load data infile and mysqlimport ...
hth --carl
Steve Tiano
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
