[PHP-DB] plain-text file (.txt) to mysql database
Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] plain-text file (.txt) to mysql database
Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
I know this might sound a little bad, but i would use Perl or an editor that supports regulars expresions to convert the data into insert statements and after that i would use a database administration tool (ex. myCC) to insert the data, you could put some statements before doing the inserts to disable foreign key checks and others things to speed up the process, but in your case i think this would have no effect in the speed. - Original Message - From: JeRRy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 01, 2004 9:08 AM Subject: [PHP-DB] plain-text file (.txt) to mysql database Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
G'day, mate, With MySQL LOAD statement, you can specify which columns of your text file goes to what DB column. HTH Ignatius _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 01, 2004 10:08 Subject: [PHP-DB] plain-text file (.txt) to mysql database Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
Hi, So would I need to edit anything in my .txt file? Could you show me an example on what you mean by: you can specify which columns of your text file goes to what DB column. Because my text file has first , last , title seperated by a space and a new entry is seperated by a enter. (new line) I took a look at www.mysql.com and had a read on 'LOAD DATA' but am not 100% sure on how it works with my sort of .txt file setup. If you could show me an example that worked with my .txt file that would be great, or would I need to edit my .txt file somewhat? Sorry about this, never used a .txt file for use of mysql before but since I need to do alot of entries at once it would be the easiest way to do it. Thanks! --- Ignatius Reilly [EMAIL PROTECTED] wrote: G'day, mate, With MySQL LOAD statement, you can specify which columns of your text file goes to what DB column. HTH Ignatius _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 01, 2004 10:08 Subject: [PHP-DB] plain-text file (.txt) to mysql database Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
Hi Ricardo, Yep that is why I asked for website/Program already established. Don't have the time to do what is mentioned as this is very urgent and needs to be done quicker rather than later. Short notice posting I know but anything is worth a try. Thanks for your suggestion. J --- Ricardo Lopes [EMAIL PROTECTED] wrote: I know this might sound a little bad, but i would use Perl or an editor that supports regulars expresions to convert the data into insert statements and after that i would use a database administration tool (ex. myCC) to insert the data, you could put some statements before doing the inserts to disable foreign key checks and others things to speed up the process, but in your case i think this would have no effect in the speed. - Original Message - From: JeRRy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 01, 2004 9:08 AM Subject: [PHP-DB] plain-text file (.txt) to mysql database Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
In your case something like this will do: LOAD DATA INFILE data.txt INTO TABLE my_table FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\r\n'# or '\n' - depending on your system IGNORE 1 LINES# if applicable ( First, Last, Title ) # actual order of your columns in your txt file, mapping columns names HTH Ignatius _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: Ignatius Reilly [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 01, 2004 10:44 Subject: Re: [PHP-DB] plain-text file (.txt) to mysql database Hi, So would I need to edit anything in my .txt file? Could you show me an example on what you mean by: you can specify which columns of your text file goes to what DB column. Because my text file has first , last , title seperated by a space and a new entry is seperated by a enter. (new line) I took a look at www.mysql.com and had a read on 'LOAD DATA' but am not 100% sure on how it works with my sort of .txt file setup. If you could show me an example that worked with my .txt file that would be great, or would I need to edit my .txt file somewhat? Sorry about this, never used a .txt file for use of mysql before but since I need to do alot of entries at once it would be the easiest way to do it. Thanks! --- Ignatius Reilly [EMAIL PROTECTED] wrote: G'day, mate, With MySQL LOAD statement, you can specify which columns of your text file goes to what DB column. HTH Ignatius _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 01, 2004 10:08 Subject: [PHP-DB] plain-text file (.txt) to mysql database Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
Two ways: - full path (like c:/temp/data.txt) - simple file name: MySQL will assume the file is located in the current database data directory (like d:/mysql/data/my_database) _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: Ignatius Reilly [EMAIL PROTECTED] Sent: Monday, March 01, 2004 11:15 Subject: Re: [PHP-DB] plain-text file (.txt) to mysql database Hi, Right now I understnad. Thanks for the example. :) As for data.txt ... Where should I pop this on my server? In the root of my account? (e.g. ~/data.txt ?) J --- Ignatius Reilly [EMAIL PROTECTED] wrote: In your case something like this will do: LOAD DATA INFILE data.txt INTO TABLE my_table FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\r\n'# or '\n' - depending on your system IGNORE 1 LINES# if applicable ( First, Last, Title ) # actual order of your columns in your txt file, mapping columns names HTH Ignatius _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: Ignatius Reilly [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 01, 2004 10:44 Subject: Re: [PHP-DB] plain-text file (.txt) to mysql database Hi, So would I need to edit anything in my .txt file? Could you show me an example on what you mean by: you can specify which columns of your text file goes to what DB column. Because my text file has first , last , title seperated by a space and a new entry is seperated by a enter. (new line) I took a look at www.mysql.com and had a read on 'LOAD DATA' but am not 100% sure on how it works with my sort of .txt file setup. If you could show me an example that worked with my .txt file that would be great, or would I need to edit my .txt file somewhat? Sorry about this, never used a .txt file for use of mysql before but since I need to do alot of entries at once it would be the easiest way to do it. Thanks! --- Ignatius Reilly [EMAIL PROTECTED] wrote: G'day, mate, With MySQL LOAD statement, you can specify which columns of your text file goes to what DB column. HTH Ignatius _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 01, 2004 10:08 Subject: [PHP-DB] plain-text file (.txt) to mysql database Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] plain-text file (.txt) to mysql database
Thankyou very much :) --- Ignatius Reilly [EMAIL PROTECTED] wrote: Two ways: - full path (like c:/temp/data.txt) - simple file name: MySQL will assume the file is located in the current database data directory (like d:/mysql/data/my_database) _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: Ignatius Reilly [EMAIL PROTECTED] Sent: Monday, March 01, 2004 11:15 Subject: Re: [PHP-DB] plain-text file (.txt) to mysql database Hi, Right now I understnad. Thanks for the example. :) As for data.txt ... Where should I pop this on my server? In the root of my account? (e.g. ~/data.txt ?) J --- Ignatius Reilly [EMAIL PROTECTED] wrote: In your case something like this will do: LOAD DATA INFILE data.txt INTO TABLE my_table FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\r\n'# or '\n' - depending on your system IGNORE 1 LINES# if applicable ( First, Last, Title ) # actual order of your columns in your txt file, mapping columns names HTH Ignatius _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: Ignatius Reilly [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, March 01, 2004 10:44 Subject: Re: [PHP-DB] plain-text file (.txt) to mysql database Hi, So would I need to edit anything in my .txt file? Could you show me an example on what you mean by: you can specify which columns of your text file goes to what DB column. Because my text file has first , last , title seperated by a space and a new entry is seperated by a enter. (new line) I took a look at www.mysql.com and had a read on 'LOAD DATA' but am not 100% sure on how it works with my sort of .txt file setup. If you could show me an example that worked with my .txt file that would be great, or would I need to edit my .txt file somewhat? Sorry about this, never used a .txt file for use of mysql before but since I need to do alot of entries at once it would be the easiest way to do it. Thanks! --- Ignatius Reilly [EMAIL PROTECTED] wrote: G'day, mate, With MySQL LOAD statement, you can specify which columns of your text file goes to what DB column. HTH Ignatius _ - Original Message - From: JeRRy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 01, 2004 10:08 Subject: [PHP-DB] plain-text file (.txt) to mysql database Hi, Quick question, does anyone know of a program/site that could do the following: I have a .txt file written in notepad. Bob Someone Mr Now I have a db with the fields: First Last Title Now the .txt file looks something like this: Bob Someone Mr Susan Taylor Miss Andrea Nothing Ms ... Now I want to use a Program/site to put the names in the right field. So each field is seperated by a space as shown above. I have about 1000 entries to put in and don't think doing each query per line is going to be a 5 min job, any suggestions? Thanks! Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com Find local movie times and trailers on Yahoo! Movies. http://au.movies.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
