1. phpLiteAdmin Avatar phpLiteAdmin
  2. phpLiteAdmin
  3. public
  4. Issues

Import CSV

Issue #71 resolved
Christopher Kramer created an issue

Originally reported on Google Code with ID 71

You can export to CSV. Why can't you import from CSV?
I have some data in a spreadsheet file and want to import it into an SQLite database.

Reported by crazy4chrissi on 2011-11-21 16:12:37

Comments (16)

  2. Dane Iracleous

    ``` The import for CSV just hasn't been implemented yet. Export was introduced very recently, so the next version will include import too. ```

    Reported by `diracleo` on 2011-11-21 21:56:53 - Status changed: `Accepted`

  3. Former user Account Deleted

    ``` I've see that you have this import feature on the 1.9.1 version. The 1.9.0 don't import CSV. Where can I download this new version (1.9.1 or more) Thanks ```

    Reported by `wetsfrance` on 2011-11-25 11:00:48

  4. Dane Iracleous

    ``` The import feature in v1.9.1 isn't functional. It just looks like it's there but it doesn't do anything. You can get it from SVN though. ```

    Reported by `diracleo` on 2011-11-25 19:44:59

  5. Christopher Kramer reporter

    ``` Okay, here is my patched version of phpliteadmin 1.9.1 that features CSV import :-)

    It is even fairly optimized for huge imports. It imported a csv-file with 70000 rows in a few seconds on my laptop. SQLite INSERT is very slow if you run it in batch because it opens a transaction for every INSERT. So I put a couple of INSERTs in one transaction. But I do not put more than 5000 INSERTs in one transaction, because in case the PHP script times out (max_execution_time), you have at least the beginning of the file. (One could make an option like phpmyadmin has for this, but I think this is not strictly necessary and most users would not understand it correctly).

    Of course your CSV file has to match your schema in terms of number of columns, otherwise you will get an error.

    The script assumes you do not surround any data in quotes. We might add an option for this maybe?

    Please let me know if you have any problems, suggestions, or just love it. ```

    Reported by `crazy4chrissi` on 2012-03-03 00:08:47

  7. Christopher Kramer reporter

    ``` I am currently working on more features. Especially making export and import csv compatible with more or less any setting. I noticed that the export feature is _very_ buggy as well. I will fix that as well. (Most options are just ignored, it adds too many line breaks and terminators and stuff.)

    I am working on an improved version and will upload it soon. ```

    Reported by `crazy4chrissi` on 2012-03-03 12:17:18

  8. Christopher Kramer reporter

    ``` Okay, here it is. A version with real improvements of CSV import AND export!

    Import: - You can now define Enclosure, Escaper, Null and first-row-field-description just like when exporting - using PHP's fgetcsv() now because manual coding of these things is tricky and can cause bugs - can now import files that were exported with phpliteadmin using the same settings! - can even import files exported with phpliteadmin without my corrections and improvements (see below), although they are not "standard CSV".

    Export: - removed line terminator option because: - this was not implemented - in my opinion anything else than a linebreak does not make sense, so useless feature - Fix: Tables with indexes where exported multiple times - Fix: Last column was terminated which is not usual in CSV - NULLs are not enclosed anymore to allow distinction between "NULL"-text - FIX: Removing CRLF was not implemented - FIX: Tables that were not exported caused additional linebreaks in output

    Might I join your programming team? I think team-programming would be easier using SVN instead of bug tracker posts of files... ```

    Reported by `crazy4chrissi` on 2012-03-03 14:11:54

    <hr>

  10. Former user Account Deleted

    ``` Could you pleace post an example of CSV file your version of phpliteadmin understands and fugure out how it works with names of columns, extra-fields in CSV, leading semicolons (colons) at the beginning of each string and other garbage in csv files. I still cant import neither a string from this:

    CSV file (just one-string file):

    "ВИЗ-0059";"Бейдж BEIFA горизонтальный 100х65мм SX109";"31,62";"33,84";"шт";

    And i import with no 'Field names in first row' option checked. Should it just insert fileds one after another in the table that already created and described below?:

    Table "tovar":

    Column # Field Type Not Null Default Value Primary Key 0 artikul TEXT no no 1 name TEXT no no 2 price_opt INTEGER no no 3 price_rozn INTEGER no no 4 unit TEXT no no 5 id INTEGER no no

    i tried to play with CSV file with no results...

    'id' is not a primary key and i am unable to set it so. this probably is another bug, but not for this tread, just don't pay attention to it. ```

    Reported by `master@remort.net` on 2012-03-28 14:12:34

  11. Christopher Kramer reporter

    ``` The number of columns in the csv has to match your schema. You have a schema with 6 columns and a csv-string with 5 columns. Please note that you might be affected by issue #75 again if you use my version. You need to replace the quote-function with the one from issue #75 to get this working with SQLite3. The file here does only work with PDO because of #75. I might post a new version tomorrow which combines both fixes. Slowly I think this is going to become a fork.

    So how my implementation works: It assumes the order of columns in the csv is the same as in the db-schema. It simply inserts the first csv-column into the first column and so on. Even if you say the first line contains column names, it will not even look at the first line but rather simply skips it. If you have a leading semicolon it assumes the first column is empty. You should import a clean csv. It is not Phpliteadmin's job to clean up dirty csv-files.

    How you should test this: Creat a test table with test data. Export it using phpliteadmin. Delete the data from the test table (vacuum). Then import the csv-file into the table. Use the same settings as when exporting. The table should look the same as before. ```

    Reported by `crazy4chrissi` on 2012-03-28 18:14:11

  12. Dane Iracleous

    ``` I'm going to release a new version soon, and it will include these improvements. Thanks for your help everyone. ```

    Reported by `diracleo` on 2012-05-13 22:44:13

  13. Christopher Kramer reporter

    ``` Cool, thanks for the news. Just let me know if you have any questions concerning my changes. Please don't forget to include the fix of issue #75 as well.

    Greetings, Christopher ```

    Reported by `crazy4chrissi` on 2012-05-13 22:53:07

  14. Dane Iracleous

    ``` crazy4chrissi, I just released v1.9.2, which includes your CSV import and export, and your quote function fix from issue #75. Really nice work. I also added you to the project as a developer. You have full access to SVN, so feel free to fix bugs and make improvements. I'm going to close this issue now. ```

    Reported by `diracleo` on 2012-05-30 12:31:50 - Status changed: `Fixed`

  16. Chris Jeffries

    I was importing CSV (actually semicolon delimited) and hit the snag that the columns were not in the same order and the header row was ignored, so I made the following mod (v.1.9.8.2) and it fixed it. It looks messy, but I didn’t want to alter the line numbering.

    Line 5700 changed from continue;
    to {$fieldnames= ' (`' . implode('`, `', $csv_data).'`) '; continue; } else {$fieldnames = '';}

    Line 5702 changed from $csv_insert .= "INSERT INTO ".$this->quote_id($table) . " VALUES (";
    to $csv_insert .= "INSERT INTO ".$this->quote_id($table) . $fieldnames . " VALUES (";

    ‌

  17. Log in to comment
Assignee
–
Type
bug
Priority
minor
Status
resolved
Component
–
Milestone
–
Version
–
Votes
0
Watchers
0
Jira: the preferred issue tracker for Bitbucket. Join the team!