----- Original Message ----- From: "Franklin Williams Jr." <[EMAIL PROTECTED]> Date: Tue, 20 Aug 2002 18:24:52 -0400 To: Joseph Grundy <[EMAIL PROTECTED]> Subject: Re: insert command
> > ----- Original Message ----- > > From: "Keith C. Ivey" <[EMAIL PROTECTED]> > > Date: Tue, 20 Aug 2002 16:50:39 -0400 > > To: [EMAIL PROTECTED] > > Subject: Re: insert command > > > > > > > On 21 Aug 2002, at 4:27, Joseph Grundy wrote: > > > > > > > I need to know if there is a way to do a simple insert from a .txt > > > > file, using insert and only populate one column. Its a streamline > > db > > > > and I want to enter only a few of the columns. > > > > > > > > I have exported all data into txt files at this point. I can not > > find > > > > any mention on mysql's site other than doing all rows or leaving > > /n > > > > spots with 3000 records and 35 columns can take a bit of time > > > > > > If I'm understanding what you want correctly, you need to either > > > (1) preprocess your text file (with a line or two of Perl or > > whatever > > > you use for manipulating text files) to remove the columns you > > don't > > > want or (2) import all the columns and just drop the ones you don't > > > want. > > > > > > In the special case where the columns you do want happen to be the > > > ones at the start of each line, then you should be able to import > > > them with LOAD DATA without modifying the text file. > > > > > > -- > > > Keith C. Ivey <[EMAIL PROTECTED]> > > > Tobacco Documents Online > > > http://tobaccodocuments.org > > > > > > > > > > > > I have the text files created. > > > > Example I have a table created lets say prices, in table prices > > there are 15 columns named 1 through 15. I want to be able to > > populate one column say 8 with data I exported from a column > > in a different database, using the txt file. Instead of populating > > by row. > > > > I was seeing if there was a way other than > > nulling all columns but that one I wanted the data to go into. > > To put 2000 records directly into column 8 as above. > > > > Thanks > > > > Joseph. > > > > Hi, > I think McNeil basically answered this one....use Load data and provide the > field list for ONLY the columns you wish to populate. Similar to: > INSERT INTO tablename(fieldlist, ) VALUES(value list, ) > > The number of items in the VALUE list MUST match the number of fields in the > field list. Here is the syntax: > > LOAD DATA [LOCAL] INFILE 'file_name.txt' > [REPLACE | IGNORE] > INTO TABLE 'table_name' > [FIELDS > [TERMINATED BY '\t' or ','] > [ENCLOSED BY ''] > [ESCAPED BY '\\'] > ] > > [LINES TERMINATED BY '\n'] > [IGNORE number LINES] > [(colname, ...)] > > LOCAL specifies client machine...without it mysql assumes file is on server > REPLACE IGNORE controls duplicates...replace replaces existing rows with the > same key, IGNORE skips those. leave out and any dups found returns error and > stops processing. > So in your case its pretty simple: > LOAD DATA LOCAL INFILE 'file_name.txt' > INTO TABLE table_name (colname1, colname2, ...); > > The qualifiers in the FIELDS section can be used to designate how the data is > presented in the infile if that is a concern. You will not need them for a > single column entry. > > let me know if this DOES NOT work!! hehe > Franklin Williams > > For the (colname1) if column was address, table was friends, and txt file was data.txt, would it look like this? LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE friends (address); when doing it this way i get error 1148 the used command is not allowed with this MySQL version. also I am doing this on windows machine if that really makes a difference. Thank you Joseph -- __________________________________________________________ Sign-up for your own FREE Personalized E-mail at Mail.com http://www.mail.com/?sr=signup --------------------------------------------------------------------- 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
