Re: Importing Text File Into mySQL
I did a little shell script to do it. the key was the shell variable IFS: Normally IFS= to make it work right I set it as follows: IFS= Yes, thats a newline between the quotes John Kopanas wrote: I have a text file with over 500K rows of data in it. The problem is that the data is no seperated by commas but instead occupy a certain amount of characters. So for instance: ID 1 -11 NAME 12-50 COMPANY_NAME 51-100 ... How would you parse import this data into mysql? Thanks for your help :-). -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing Text File Into mySQL
In the last episode (Nov 17), John Kopanas said: I have a text file with over 500K rows of data in it. The problem is that the data is no seperated by commas but instead occupy a certain amount of characters. So for instance: ID 1 -11 NAME 12-50 COMPANY_NAME 51-100 ... How would you parse import this data into mysql? Create a table that matches your layout exactly, then LOAD DATA ... FIELDS TERMINATED BY '' FIELDS ENCLOSED BY ''; It's mentioned in one of the comments at http://dev.mysql.com/doc/refman/5.0/en/load-data.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Importing Text File Into mySQL
I am trying to figure out how this would work? How does LOAD DATA figure out when one column begins and another ends when some of the data are addresses with spaces in them? On 11/17/06, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Nov 17), John Kopanas said: I have a text file with over 500K rows of data in it. The problem is that the data is no seperated by commas but instead occupy a certain amount of characters. So for instance: ID 1 -11 NAME 12-50 COMPANY_NAME 51-100 ... How would you parse import this data into mysql? Create a table that matches your layout exactly, then LOAD DATA ... FIELDS TERMINATED BY '' FIELDS ENCLOSED BY ''; It's mentioned in one of the comments at http://dev.mysql.com/doc/refman/5.0/en/load-data.html -- Dan Nelson [EMAIL PROTECTED] -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing Text File Into mySQL
In the last episode (Nov 17), John Kopanas said: On 11/17/06, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Nov 17), John Kopanas said: I have a text file with over 500K rows of data in it. The problem is that the data is no seperated by commas but instead occupy a certain amount of characters. So for instance: ID 1 -11 NAME 12-50 COMPANY_NAME 51-100 ... How would you parse import this data into mysql? Create a table that matches your layout exactly, then LOAD DATA ... FIELDS TERMINATED BY '' FIELDS ENCLOSED BY ''; It's mentioned in one of the comments at http://dev.mysql.com/doc/refman/5.0/en/load-data.html I am trying to figure out how this would work? How does LOAD DATA figure out when one column begins and another ends when some of the data are addresses with spaces in them? It goes by the field widths in the table you're loading into. So set it up like: id VARCHAR(10), name VARCHAR(38), company_name VARCHAR(49), etc. If you've got any numeric fields, you'll probably have to make them varchars now, then convert them to numbers with an ALTER TABLE later (unless your fields happen to hit the magic widths listed at http://dev.mysql.com/doc/refman/5.0/en/load-data.html ). Actually, using DECIMAL fields should work, since you can specify their widths. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing Text File Into mySQL
Hi, LOAD DATA restricts only on BLOB,TEXT,ENUM and FIXED DATA. Others are equally supported [INSERT stmt]. Likewise for mysqlimport also. If the data file has no delimiter other than space[even the name, addresss,etc contain spaces], then the target table will not the one what is expected, though it is possible to import the data. It also depends on the width of the fields. The data and the fieldname does not corresponds. Thanks ViSolve DB Team. - Original Message - From: John Kopanas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 18, 2006 4:35 AM Subject: Importing Text File Into mySQL I have a text file with over 500K rows of data in it. The problem is that the data is no seperated by commas but instead occupy a certain amount of characters. So for instance: ID 1 -11 NAME 12-50 COMPANY_NAME 51-100 ... How would you parse import this data into mysql? Thanks for your help :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- 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]
