RE: Textfile to a 2 column mysql database
To pinpoint the solution here it is an one liner ;-) perl -n -e 'chomp; print "INSERT INTO YourTable (question,answer) VALUES (\"$tmp\",\"$_\");\n" if ($|--); $tmp=$_' test.txt > test.sql It's a "hybrid" perl solution which requires feeding in MySQL afterwards and it disregards special characters. Lian > -Original Message- > From: gerald_clark [mailto:[EMAIL PROTECTED] > Sent: Monday, June 23, 2003 4:42 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: Textfile to a 2 column mysql database > > > Unless, of course, you have quotes in your data. > With perl you can use the quote() function to ensure the whole > line gets in. > > Christopher Knight wrote: > > >OR, if you are good at vi, > > > >you can insert a ' at the begining and end of every line (if you > dont have > >any 's in the file) > >then put a , at the end of every odd line > >then join every other line > > > >the put a "insert into blah (question, answer) values > ( " at the > >begin of every line > >and then a ");" at the end of every line > > > >Then you hopefully have a file full of insert statements and you can just > >feed it into a mysql client. > >If I missed a step or added one by accident or even got one > wrong, go ahead > >and fix it and pretend > >I told you correctly. ;-) > > > >The problem with my solution, is that if you have any wierd > characters, they > >arent escaped. > > > >or you could just write a perl program > > > >chris > > > >-Original Message- > >From: gerald_clark [mailto:[EMAIL PROTECTED] > >Sent: Monday, June 23, 2003 8:21 AM > >To: O.S. Bos > >Cc: [EMAIL PROTECTED] > >Subject: Re: Textfile to a 2 column mysql database > > > > > >Write a quick perl program. > > > >O.S. Bos wrote: > > > > > > > >>Hi there, > >> > >>I have a textfile that I want to get inserted into a database. The > >>textfile consists of Questions and Answers. 1st line is a question. 2nd > >>line of the textfile is the answer. And so on... > >> > >>What is the best way to import these lines into the database with 2 > >>columns. 1 column for the questions and one for the answers? > >> > >>Thx! > >>Unox > >> > >> > >> > >> > >> > >> > > > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Textfile to a 2 column mysql database
gerald_clark wrote: Unless, of course, you have quotes in your data. With perl you can use the quote() function to ensure the whole line gets in. Or placeholders. - Cs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Textfile to a 2 column mysql database
Unless, of course, you have quotes in your data. With perl you can use the quote() function to ensure the whole line gets in. Christopher Knight wrote: OR, if you are good at vi, you can insert a ' at the begining and end of every line (if you dont have any 's in the file) then put a , at the end of every odd line then join every other line the put a "insert into blah (question, answer) values ( " at the begin of every line and then a ");" at the end of every line Then you hopefully have a file full of insert statements and you can just feed it into a mysql client. If I missed a step or added one by accident or even got one wrong, go ahead and fix it and pretend I told you correctly. ;-) The problem with my solution, is that if you have any wierd characters, they arent escaped. or you could just write a perl program chris -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 8:21 AM To: O.S. Bos Cc: [EMAIL PROTECTED] Subject: Re: Textfile to a 2 column mysql database Write a quick perl program. O.S. Bos wrote: Hi there, I have a textfile that I want to get inserted into a database. The textfile consists of Questions and Answers. 1st line is a question. 2nd line of the textfile is the answer. And so on... What is the best way to import these lines into the database with 2 columns. 1 column for the questions and one for the answers? Thx! Unox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Textfile to a 2 column mysql database
OR, if you are good at vi, you can insert a ' at the begining and end of every line (if you dont have any 's in the file) then put a , at the end of every odd line then join every other line the put a "insert into blah (question, answer) values ( " at the begin of every line and then a ");" at the end of every line Then you hopefully have a file full of insert statements and you can just feed it into a mysql client. If I missed a step or added one by accident or even got one wrong, go ahead and fix it and pretend I told you correctly. ;-) The problem with my solution, is that if you have any wierd characters, they arent escaped. or you could just write a perl program chris -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 8:21 AM To: O.S. Bos Cc: [EMAIL PROTECTED] Subject: Re: Textfile to a 2 column mysql database Write a quick perl program. O.S. Bos wrote: >Hi there, > >I have a textfile that I want to get inserted into a database. The >textfile consists of Questions and Answers. 1st line is a question. 2nd >line of the textfile is the answer. And so on... > >What is the best way to import these lines into the database with 2 >columns. 1 column for the questions and one for the answers? > >Thx! >Unox > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Textfile to a 2 column mysql database
Write a quick perl program. O.S. Bos wrote: Hi there, I have a textfile that I want to get inserted into a database. The textfile consists of Questions and Answers. 1st line is a question. 2nd line of the textfile is the answer. And so on... What is the best way to import these lines into the database with 2 columns. 1 column for the questions and one for the answers? Thx! Unox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Textfile to a 2 column mysql database
Hi there, I have a textfile that I want to get inserted into a database. The textfile consists of Questions and Answers. 1st line is a question. 2nd line of the textfile is the answer. And so on... What is the best way to import these lines into the database with 2 columns. 1 column for the questions and one for the answers? Thx! Unox -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
