Re: [PHP] From TXT to a mySQL db
On Oct 18, 2007, at 3:15 PM, Marcelo Wolfgang wrote: This looks good, but since the db server is in a hosting company it will be possible to read from a .txt ? also I have never seen the LOAD DATA command, so I'm testing here and having problems ... can anyone spot what's wrong on this query ? $SQL = LOAD DATA INFILE 'test.txt' INTO TABLE test FIELDS TERMINATED BY '^' LINES STARTING BY '20'; $Query = mysql_query($SQL); TIA Marcelo Wolfgang I'm not sure about the issue with the query... I've only used it a few times, although I will be using it today with a csv file so I'll let you know if I find anything :) As far as if the hosting company will let you put the file and read it from their account I'm not sure, that would be a decision that they made, actually I'm not positive that there is anyway they can stop it... But if they don't want to let you do it that way.. Would they allow you to place a mysql database on their server that they didn't create? You said you were testing locally, so why not simply do the load file, and then transfer a complete table up to the hosting company? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] From TXT to a mySQL db
Hi all, I need some helps/tips to know if a transition from a txt file to a sql database is viable to do. I have a TXT file that has lots of records like this: 10^13150^COMERCIO DE CALCADOS DILEU LTDA^COMERCIO DE CALCADOS DILEU LTDA^RUA JOSE BONIFACIO, 329^CENTRO^IJUI^RS^9870^055 3325409^ 10^13169^ZILMAR DE PARIS^ZILMAR DE PARIS^MARECHAL FLORIANO1151^CENTRO^SOLEDADE^RS^9930^ 54 3812166^ 20^9833^90700^ELLA VERAO 2007^90702^COURO SOFT^BCO 20^9833^90700^ELLA VERAO 2007^90702^COURO SOFT^PRETO The rows that start with 10 are one kind of data and the rows that start with 20 are another kind of data, each will go to its own table (there's a lots of 10's and lots of 20's). The first set of data, the ones that start with 10 its table structure will look like this (the 10 will be discarded): id - number name - text altName - text address - text neighbour - text city - text state - text cep - number tel - text mail - text the second one with the rows that start with 20 its structure will be like this ( again the 20 will be discarded ) idClient - number brand - text idLine - number descLine - text ref -number material - text color - text Is there a quick and simple way to convert the row string into a INSERT query ? Should I try some manipulation first with the TXT file ? TIA Marcelo Wolfgang -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] From TXT to a mySQL db
On Oct 18, 2007, at 2:29 PM, Marcelo Wolfgang wrote: Hi all, I need some helps/tips to know if a transition from a txt file to a sql database is viable to do. I have a TXT file that has lots of records like this: 10^13150^COMERCIO DE CALCADOS DILEU LTDA^COMERCIO DE CALCADOS DILEU LTDA^RUA JOSE BONIFACIO, 329^CENTRO^IJUI^RS^9870^055 3325409^ 10^13169^ZILMAR DE PARIS^ZILMAR DE PARIS^MARECHAL FLORIANO1151^CENTRO^SOLEDADE^RS^9930^ 54 3812166^ 20^9833^90700^ELLA VERAO 2007^90702^COURO SOFT^BCO 20^9833^90700^ELLA VERAO 2007^90702^COURO SOFT^PRETO The rows that start with 10 are one kind of data and the rows that start with 20 are another kind of data, each will go to its own table (there's a lots of 10's and lots of 20's). The first set of data, the ones that start with 10 its table structure will look like this (the 10 will be discarded): id - number name - text altName - text address - text neighbour - text city - text state - text cep - number tel - text mail - text the second one with the rows that start with 20 its structure will be like this ( again the 20 will be discarded ) idClient - number brand - text idLine - number descLine - text ref -number material - text color - text Is there a quick and simple way to convert the row string into a INSERT query ? Should I try some manipulation first with the TXT file ? I think what you are looking for is the LOAD DATA command. With that one of the options is SEPARATED BY I'd do a search for LOAD DATA in the mysql site. -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] From TXT to a mySQL db
This looks good, but since the db server is in a hosting company it will be possible to read from a .txt ? also I have never seen the LOAD DATA command, so I'm testing here and having problems ... can anyone spot what's wrong on this query ? $SQL = LOAD DATA INFILE 'test.txt' INTO TABLE test FIELDS TERMINATED BY '^' LINES STARTING BY '20'; $Query = mysql_query($SQL); TIA Marcelo Wolfgang Jason Pruim wrote: On Oct 18, 2007, at 2:29 PM, Marcelo Wolfgang wrote: Hi all, I need some helps/tips to know if a transition from a txt file to a sql database is viable to do. I have a TXT file that has lots of records like this: 10^13150^COMERCIO DE CALCADOS DILEU LTDA^COMERCIO DE CALCADOS DILEU LTDA^RUA JOSE BONIFACIO, 329^CENTRO^IJUI^RS^9870^055 3325409^ 10^13169^ZILMAR DE PARIS^ZILMAR DE PARIS^MARECHAL FLORIANO1151^CENTRO^SOLEDADE^RS^9930^ 54 3812166^ 20^9833^90700^ELLA VERAO 2007^90702^COURO SOFT^BCO 20^9833^90700^ELLA VERAO 2007^90702^COURO SOFT^PRETO The rows that start with 10 are one kind of data and the rows that start with 20 are another kind of data, each will go to its own table (there's a lots of 10's and lots of 20's). The first set of data, the ones that start with 10 its table structure will look like this (the 10 will be discarded): id - number name - text altName - text address - text neighbour - text city - text state - text cep - number tel - text mail - text the second one with the rows that start with 20 its structure will be like this ( again the 20 will be discarded ) idClient - number brand - text idLine - number descLine - text ref -number material - text color - text Is there a quick and simple way to convert the row string into a INSERT query ? Should I try some manipulation first with the TXT file ? I think what you are looking for is the LOAD DATA command. With that one of the options is SEPARATED BY I'd do a search for LOAD DATA in the mysql site. -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] From TXT to a mySQL db
Hi. On Thursday 18 October 2007 19:29, Marcelo Wolfgang wrote: Hi all, I need some helps/tips to know if a transition from a txt file to a sql database is viable to do. [snipped data and table descriptions] Is there a quick and simple way to convert the row string into a INSERT query ? Once you have opened the file for reading, read one line at a time and use explode() to get the individual fields out of each line into an array. By the looks of the data you need to tell explode to use ^ as field delimiter. Then contruct an SQL INSERT query and execute it to put the array values into the correct table, depending on whether the first value is 10 or 20. Make sure you escape each field value to make it safe for whatever database you are using. Repeat for each line until there aren't any more. I've done similar things to this more than once and as long as the data isn't too weird you should have no problems. Should I try some manipulation first with the TXT file ? I wouldn't imagine there would be any need for that, no. Hope this helps, Mark -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
