optionally create new table when importing csv

Issue #299 resolved
Christopher Kramer created an issue

Currently, importing csv is only possible into existing tables. It would be great if it would be possible to import a csv and phpLiteAdmin would automatically create the structure of the table. Of course CSV does not have any type info, so I would say it should just create all columns as TEXT. Using phpLiteAdmin, it is easy to edit the types afterwards manually if necessary. Does not make much difference with SQLite anyway...

Comments (12)

  1. phpLiteAdmin repo owner

    No its not too hard to build. Basically, take the first row and create a simple CREATE TABLE statement with one column definition per field that was found in the first row. After the table is created, the rest of the import works the same way.

    Nobody ever requested it other than me, and I don't need it anymore. That's the only reason why it's not yet implemented. But I'll assign it to me and plan it for the next release.

  2. Peter Wolanin

    Great, thanks. Seems like one minor trick is the need to quote (with " or `) or normalize (remove or replace spaces and other odd chars with underscores or the like) the column names from the header row

  3. Peter Wolanin

    In fact, I'm wondering if the CSV import has a bug for headers that have parens or some other characters. I created a table by hand from my CSV file, but the import fails with error: {code} ERROR: table test_import has 27 columns but 26 values were supplied

    I am able to import the same file with the sqlite3 cli

  4. phpLiteAdmin repo owner

    Well, CSV is not really standardized. I have seen Excel not able to open CSVs that LibreOffice opens just fine and the other way round. That's because Excel has another understanding of CSV than LibreOffice. And similarly, PHP (and thus phpLiteAdmin) has another understanding than the Sqlite cli. We just use PHPs CSV functions. Can you provide an example that does not import correctly? What format is your CSV in? What settings did you choose when importing?

  5. phpLiteAdmin repo owner

    @pwolanin Please try the latest development version, it should now allow to create a new table on import. And it should import your CSV fine.

  6. Peter Wolanin

    Testing with my CSV is successful using master @31057a4795417ba72cb2194dd

    The table was created and the 5 rows imported. Thanks!

  7. Log in to comment