README

Spreadsheet To DB (& other conversions)

Convert .ods files into .csv, .json, .sql, and create an .sqlite database

Install

composer require taeluf/spreadsheet-converter v0.1.x-dev

or in your composer.json

{"require":{ "taeluf/spreadsheet-converter": "v0.1.x-dev"}}

Dependencies

libreoffice (We convert .ods files with the libreoffice cli command)

Usage

See test/example.php and test/data to for a working example. You can execute test/example.php to make sure it works on your system.

Make a php file like this
chmod ug+x the-file.php to make it executable.
./the-file.php to execute it & build your database

#!/usr/bin/env php  
<?php  
  
$root_dir = dirname(__DIR__);  
require($root_dir.'/vendor/autoload.php');  
  
  
// .ods files are NEVER overwritten (except in case of error, maybe? Do use version control)  
\Tlf\build_csv_conversions(  
      
    // you could do this:  
        // json_decode(file_get_contents(__DIR__.'/settings.json'));  
    // or a for loop over a scandir() or an array of settings files & run many separate conversions  
    [   
        // all settings shown are the defaults  
          
        'source_dir' => $root_dir.'/test/data/src/',  
  
        'path_prefix'=>$root_dir.'/test/data/raw-',  
  
        // bugfix: Fileformat encoding leaves some characters incompatible with utf8, so apply a fix with libreoffice cli  
        'use_filetype_conversion'=>true,  
  
        // WARNING: OVERWRITES FILES if true  
        // none of the files should be edited, generally. They're meant to be built from .ods spreadsheets  
        // Good to set them false if testing some kind of error  
        //  
        'csv'=>true,  
        'json'=>true,  
        'sql'=>true,  
  
        'sqlite'=>true,  
  
    ]  
);

File Structure

The root dir can be anything, I'm using data for the example

data/  
    src/ -- all the data that you enter manually  
        covid-cases.ods  
        local-use-of-force-incidents.ods  
        local-traffic-stop-disparities.ods  
        delicious-gummi-bears.ods  
    raw-csv/  
        local_use_of_force_incidents.csv  
        -- and the other files  
        -- these are generated from the .ods files  
    raw-json/  
        -- same  
    raw-sql/  
        -- same  
    data.sqlite -- is overwritten on build & tables are named after their source files   
                -- so delicious_gummi_bears is a table (hyphens replaced with underscore)

taeluf / spreadsheet-converter

Maintainers

Details