Convert Spreadsheet to Database

← Return

Do you ever wish you could do more with a spreadsheet? Well, in just a few steps, you can convert your spreadsheet into a table in a flat file database.

Each spreadsheet row will become a record, and the headings will now define record fields. To use this database in another application, you will need the folder holding the records, an array showing the list of fields, and a few functions for reading from and and writing to the records.

Click here for a demo

Convert at CSV File to a DATABASE

Click here to download files

CSV-DATABASE/index.php ▾

CSV-DATABASE/inc

convert-csv-file-to-records.php ▾

<?php
//Remove previous records
$array1 = scandir ('data/records');
foreach ($array1 as $item1) {
    if (substr ($item1, 0, 1) !== '.') {
        unlink ('data/records/' . $item1);
    }
}
$spreadsheetkeys = readArray ('data/spreadsheetkeys.txt', ',');
$recordkeys = readArray ('data/recordkeys.txt', ',');

$file = fopen('temp.csv',"r");
$count = 0;
$recordnumber = 0;
while(! feof($file) ){
    $array1 = fgetcsv($file);   
    if ($array1) {   
        if ($count === 0) { 
            $count++;   
        }        
        else  {   
            //create a temporary record with values in the order of the original csv file
            $temprecord = array();              
            foreach ($spreadsheetkeys as $id => $key) {
                if (array_key_exists ($id, $array1)) {   
                   $temprecord[$key] = ""; 
                   $item1 = $array1[$id]  ;
                   $item1 = preg_replace('/[^A-Za-z0-9-?!@\s\/]/', '', $item1);                       
                   $temprecord[$key] =  $item1;
                }
            }        
            //move values from temporary record to database record
            $record = initializeRecord ($recordkeys);           
            foreach ($spreadsheetkeys as $key) {
                if (in_array ($key, $recordkeys)) {                                   
                    $record[$key] = $temprecord[$key];
                }
            }     
            
            //Create record id
            $recordid = '';
            $number = 0;
            if ($key1) {              
                $recordid = $temprecord[$key1]; 
                if ($key2) {                        
                    $recordid .=  "-" . $temprecord[$key2]; 
                    if ($key3) {                               
                        $recordid .= "-" . $temprecord[$key3]; 
                        
                    }
                }
                if ($key4 === 'number-first') {
                    $recordnumber++;
                    $recordnumber = str_pad ($recordnumber, 5, 0, STR_PAD_LEFT);
                    $recordid  = $recordnumber  . "-".  $recordid;   
                } 
                else if ($key4 === 'number-last') {
                    $recordnumber++;
                    $recordnumber = str_pad ($recordnumber, 5, 0, STR_PAD_LEFT);
                    $recordid .=  "-" . $recordnumber ;   
                }
            }

            if ($recordid ) {
                $recordid = str_replace (' ', '', $recordid);
                $recordid = strtolower ($recordid);                    
                $recordid = preg_replace('/[^A-Za-z0-9-]/', '', $recordid);  
                if (file_exists ('data/records/' . $recordid . '.txt')) {
                    echo "File already exists: " . $recordid . "<br>";
                }
                else {
                    writeDatabaseRecord ($record, 'data/records/' . $recordid . '.txt'); 
                }
            }
            
        }
      
    }        
}  
fclose($file);

?>

display-all-records-in-table.php ▾

functions.php ▾

<?php

//delimeters for records
$dl1 = "%%%";
$dl2 = "%#%";

function initializeRecord ($keys){     
    $record = array ();
    foreach ($keys as $Id => $key) {
        $record[$key] = ""; 
    }  
    return ($record);
}
function readDatabaseRecord ($keys,  $filename) {
    //Creates 'record' variable by assigning keys and values to associative array, 
    global $dl1;
    //Assign null values to each key
    $record = array();  
    foreach ($keys as $Id => $key) {
        $record[$key] = ""; 
    }
    //If text string is found, assign values to each key
    if ($filename && file_exists($filename)) {        
        $string = file_get_contents ($filename);  
        $Array1= explode ($dl1, $string);
        foreach ($keys as $Id => $key) { 
            if (array_key_exists ($Id, $Array1)) {
                $record [$key] = $Array1[$Id]; 
            }        
        }          
    } 
    return $record;  
}


function writeDatabaseRecord ($record,  $filename) { 
    //Stores record array as text file
    global $dl1;    
    $String = implode ($dl1, $record);     
    file_put_contents ($filename, $String);      
}

//ARRAYS 

function readArray ($filename, $delimiter){
  
    $fArray1 = array();
    if (file_exists($filename)) { 
      
        $String = file_get_contents ($filename);
        if ($String !== "") {
            $fArray1 = explode ($delimiter, $String); 
        }
    }  
    return $fArray1;    
}

 function writeArray ($filename, $array, $delimiter){    
    $String = implode ($delimiter, $array);    
    file_put_contents ($filename, $String);          
}  


function getFromQueryString ($label) {  
	$value = "";
	if (isset ($_GET[$label])) { 
        $value =  $_GET[$label];	
        if (specialChars($value) || strlen ($value) > 300) {            
            //Invalid input
            $value = '';
        }            
	}   
	return $value;    
}


function specialChars($str) {
     global $dl1, $dl2; 
  
    if (strpos ($str, "<") !== false || strpos ($str, ">") !== false | strpos ($str, $dl1) !== false | strpos ($str, $dl2) !== false ) {
        return true;
    }
}
?>

header.php ▾

identify-csv-column-headings.php ▾

key-and-field-selection-form.php ▾

<form method = 'post' action = 'index.php'>
    <h3>Select up to 3 column headings to be primary record keys</h3>
    <div class = 'third-column'>
        <h4>Key 1</h4><br>
        <?php
        foreach ($spreadsheetkeys as $key) {
            echo "<input id = '" . $key . "-1' type = 'radio' name = 'key1' value = '" . $key . "'/>";
            echo "<label for = '" . $key . "-1' >" . $key . "</label><br>";
        }
        ?>
    </div><div class = 'third-column'>
        <h4>Key 2</h4><br>
        <?php
        foreach ($spreadsheetkeys as $key) {
            echo "<input id = '" . $key . "-2' type = 'radio' name = 'key2' value = '" . $key . "'/>";
            echo "<label for = '" . $key . "-2' >" . $key . "</label><br>";
        }
        ?>
    </div><div class = 'third-column'>
        <h4>Key 3</h4><br>
        <?php
        foreach ($spreadsheetkeys as $key) {
            echo "<input id = '" . $key . "-3' type = 'radio' name = 'key3' value = '" . $key . "'/>";
            echo "<label for = '" . $key . "-3' >" . $key . "</label><br>";
        }
        ?>
        </div><br><br><br>
    <div class = 'third-column'>
        <h4>Add a number to the beginning or end of the record key</h4>
        <input id = 'number-first'  type = 'radio' name = 'key4' value = 'number-first'/>
        <label for = 'number-first' >number First</label><br>
        <input id = 'number-last'  type = 'radio' name = 'key4' value = 'number-last'/>
        <label for = 'numberlast' >number Last</label><br>

    </div><div class = 'third-column'>
        <h4>Select fields to be available in new record</h4>
        <?php
        foreach ($spreadsheetkeys as $key) {
            echo "<input id = '" . $key . "' type = 'checkbox' name = 'recordkeys[]' value = '" . $key . "'/>";           
            echo "<label for = '" . $key . "' >" . $key . "</label><br>";            
        }        
        ?>   
   </div>
    <br><br><input class = 'submitbutton' type = 'submit' name = 'submit-keys-and-fields' value = 'Submit'>
</form>

style.css ▾

CSV-DATABASE/pages

display-record.php ▾

display-table.php ▾

home.php ▾

<?php

if ($_SERVER ["REQUEST_METHOD"] == "POST") {     
  
    if (isset ($_POST['submit-csv'])) {  
        //If not input, use default CSV file   
        copy ('data/FCS.csv', 'temp.csv');  
        if ($_FILES['uploadfile1']['name'] !== ''){          
            $imagebase1 = $_FILES['uploadfile1']['name'];      
            $imagename = pathinfo ($imagebase1, PATHINFO_FILENAME);
            $extension = strtolower (pathinfo ($imagebase1, PATHINFO_EXTENSION));
            if($extension ===  'csv' ) {                 
                $fileTmpName = $_FILES['uploadfile1']['tmp_name'];                     
                move_uploaded_file($fileTmpName, 'temp.csv');   
            }    
        }     
        include ('inc/identify-csv-column-headings.php');  
        include ("inc/key-and-field-selection-form.php");       
    }
    
    else if (isset ($_POST['submit-keys-and-fields'])){  
        $recordkeys = array();
        $key1 = $key2  = $key3 = $key4 = '';

        if (isset ($_POST['key1'])) {
            $key1 = $_POST['key1'];
            $key1 = preg_replace('/[^A-Za-z0-9-?!,\s\/]/', '', $key1); 
            $key1 = str_replace (' ', '', $key1);
            $key1 = strtolower($key1);
        }
        if (isset ($_POST['key2'])) {
            $key2 = $_POST['key2'];           
            $key2 = preg_replace('/[^A-Za-z0-9-?!,\s\/]/', '', $key2); 
            $key2 = str_replace (' ', '', $key2);
            $key3 = strtolower($key3);
        }
        if (isset ($_POST['key3'])) {
            $key3 = $_POST['key3'];           
            $key3 = preg_replace('/[^A-Za-z0-9-?!,\s\/]/', '', $key3); 
            $key3 = str_replace (' ', '', $key3);
            $key3 = strtolower($key3);
        }
        if (isset ($_POST['key4'])) {
            $key4 = $_POST['key4'];            
        }
        if (isset ($_POST['recordkeys'])) {
            $recordkeys = $_POST['recordkeys'];          
        }
        

        //Create record key array, including any new keys                     
        if ($key1 ){                
            writeArray ('data/recordkeys.txt', $recordkeys,',');
            include ('inc/convert-csv-file-to-records.php');
            echo "<h4>Records have been created</h4>";
            if (file_exists ('temp.csv'))  {           
                 unlink ('temp.csv');   
            }   
        }
        else {
            echo "<div class = 'error'>Missing keys</div>"; 
            if (file_exists ('temp.csv'))  {           
                unlink ('temp.csv');   
            }   
        } 
    }
}

else { 
?>
<h4>Choose a CSV file ot click 'Enter' to use the default file</h4>
<form action="index.php?page=home" method="post" enctype="multipart/form-data">    
    <label for = 'uploadfile1'>csv file </label>
    <input id = 'uploadfile1'  type="file" name="uploadfile1"  /><br><br>  

    <input class = 'submitbutton' type = 'submit' name = 'submit-csv' value = 'Enter' />
</form>  
<?php
}
?>

update-field-names.php ▾

CSV-DATABASE/data

FCS.csv ▾

recordkeys.txt ▾

records ▾

spreadsheetkeys.txt ▾

Example record:

Each field is separated by the character string: %%%

Armstrong%%%Antonio%%%Mousetail Blvd%%%1%%%770 111-2222%%%%%%

>^..^<