Hack43.Convert CSV to PHP | PHP Hacks: Tips & Tools For Creating Dynamic Websites

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 browser

5.11.1. The Code

Save 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 Hack

First 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.

The page currently hardwires in five fields, but it would be trivial to change the HTML to allow for more field names.

Figure 5-8. The entry form for the CSV data and the field names

Once 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.