Hack 43. Convert CSV to PHP
Use PHP to create PHP data arrays from comma-separated value (CSV) datafiles. Every once in a while, I have a static list of values that I don't want to put into a database, but that I do want to use in my PHP application. That static data can come from a variety of sources, but often it's in a spreadsheet. This handy hack converts any CSV data (one of the easiest formats to pull from a spreadsheet) to PHP code that I can then copy and paste into my PHP page. Figure 5-7. The publisher table as shown in the browser5.11.1. The CodeSave the code in Example 5-31 as index.php. Example 5-31. Page that sets up some comma-separated values to convert<html> <body> <form method="post" action="commaconv.php" /> <table> <tr><td>CSV Data:</td> <td><textarea name="data" cols="40" rows="10"> "Alabama",4530182 "Alaska",655435 "Arizona",5743834 "Arkansas",2752629 "California",35893799 "Colorado",4601403 "Connecticut",3503604 "Delaware",830364 "District of Columbia",553523 "Florida",17397161 "Georgia",8829383 "Hawaii",1262840 "Idaho",1393262 "Illinois",12713634 "Indiana",6237569 "Iowa",2954451 "Kansas",2735502 "Kentucky",4145922 "Louisiana",4515770 "Maine",1317253 "Maryland",5558058 "Massachusetts",6416505 "Michigan",10112620 "Minnesota",5100958 "Mississippi",2902966 "Missouri",5754618 "Montana",926865 "Nebraska",1747214 "Nevada",2334771 "New Hampshire",1299500 "New Jersey",8698879 "New Mexico",1903289 "New York",19227088 "North Carolina",8541221 "North Dakota",634366 "Ohio",11459011 "Oklahoma",3523553 "Oregon",3594586 "Pennsylvania",12406292 "Rhode Island",1080632 "South Carolina",4198068 "South Dakota",770883 "Tennessee",5900962 "Texas",22490022 "Utah",2389039 "Vermont",621394 "Virginia",7459827 "Washington",6203788 "West Virginia",1815354 "Wisconsin",5509026 "Wyoming",506529</textarea></td></tr> <tr><td> Field Name 1:</td><td><input name="field0" value="state" /></td></tr> <tr><td> Field Name 2:</td><td><input name="field1" value="population" /></td> </tr> <tr><td> Field Name 3:</td><td><input name="field2" value="" /></td></tr> <tr><td> Field Name 4:</td><td><input name="field3" value="" /></td></tr> <tr><td> Field Name 5:</td><td><input name="field4" value="" /></td></tr> </table> <input type="submit" /> </form> </body> </html> The code in Example 5-32commaconv.phphandles the data conversion. Example 5-32. PHP that converts data from CSV just as easily as from XML or SQL<html><body> <div style="font-family:courier; font-size:small;"> $data = array(<br/> <? $fieldnames = array( $_POST['field0' ], $_POST['field1' ], $_POST['field2' ], $_POST['field3' ], $_POST['field4' ] ); $rows = split( "\n", $_POST['data'] ); $index = 0; foreach( $rows as $row ) { if ( $index != 0 ) print( ",<br/>" ); $index++; print( " array(" ); $fields = split( ",", $row ); for( $f = 0; $f < count( $fields ); $f++ ) { $data = $fields[ $f ]; $data = preg_replace( "/\\\\\"/", "\"", $data ); if ( $f > 0 ) print( ", " ); print( $fieldnames[ $f ] ); print( " => " ); print( $data ); } print( " )" ); } ?><br/> ); </div> </body></html> 5.11.2. Running the HackFirst navigate to the index.php page on the server. There you will see the form shown in Figure 5-8. Paste the CSV data you have into the CSV Data field (or you can use the state data provided by default). Then type the names of the fields from the CSV data into the Field Name fields that follow.
Figure 5-8. The entry form for the CSV data and the field namesOnce you are done entering data and column names, click the Submit Query button and the form will submit the data to the commaconv.php script. The output of the script looks like Figure 5-9. Select all of the text on this page and use the Copy command in the browser to put the text into the clipboard. You can then use the Paste command in your favorite text editor to put this code into your page. |