0 users online. Create an account or sign in to join them.Users

Search

Import/export CSV updated to version 0.2.3 on 1st of September 2011

  • Added support to import/export the multilangual field: Export a field to a CSV, do the translations (or supply it to a translation agency), and import it again.

I could kiss you :) I was asked to provide import ability for 2 language section today and just popped on the forum to find this little gem.

Thank you for pushing the boundaries of multilingual with this CMS. very much appreciated.

The subsectionmanager export only the ID. but I like to export the value.

Is there a way to retrieve that value?

@kanduvisla, on a mac (Chrome) sym 2.2.2 install, I can't seem to import a csv anymore. it's the latest version from git for the extension... bug?

I select the file, then the section and click import and it just reverts to no file selected and no errors are present that I can see. So you know.

EDIT

Tried Safari too and got same result. I can Export fine though.

@plenaforma: The value that is exported/imported for SMM is determined by it's driver. In the case of SMM, the ID is used, because the linked entries are what matters for SMM (since SMM allows you to attach multiple entries with in each entry multiple fields). You could edit the driver to export/import the value of your related field(s), but I think this might only work in your case. Also beware of importing issues when doing this.

@moonoo2: Could you file an issue for that on GitHub?

Just wanted to add what an excellent extension this is. I did however run into a usecase which wasn't supported. My knowledge of PHP is a little dangerous but nonetheless the client needed this done. Maybe this issue is related to a wider extension architecture problem?

Context

I built an iPad events management app for a client which uses Symphony to manage all events and guests. Running Symphony CMS 2.2.3.

Usecase

Client wants to import/export CSV files.

Problem

Client is a user of type "Author". Extension only supports "Developer".

Solution

After some digging it wasn't difficult to establish that the extension driver class had to check if the author is of type "Author" too. A better solution is to make this configurable through the Preferences page but I don't have the knowledge to do this. So I just update a line in the class to

    if($author->isDeveloper() || $author->isAuthor())

I then looked in the Author class and realised there was no isAuthor method so added it.

    /**
     * Returns boolean if the current Author is of the developer
     * user type.
     *
     * @return boolean
     */
    public function isAuthor(){
        return ($this->get('user_type') == 'author');
    }   

This is not ideal as it will break any new updates Symphony makes. Which leads me to my question - shouldn't extensions have some easy way of hooking into author types?

I wouldn't add the extra to the core, just remove the if logic for the developer form the extension!

Good idea thanks!

I've been using this extension for a while and it's great!

For some reason on my current project when I export the CSV there is something wrong with the formatting and, when the file is opened in Excel or Page (for example), all fields appear in the first cell of each column rather than there being a cell for each field.

All the data is there it is just all in one cell.

I have a lot of fields (75!) but no fields that are unsupported as far as I can tell. I've made sure I'm using the latest version.

I've attached an example of the CSV I'm getting. Would love to know how to resolve this if possible!

Attachments:
sign-up_2011-10-18.csv

one thing i noticed right off the bat is that libreoffice by default interprets the comma as the column separator and this file uses semicolons.

Yeah I noticed that too but wasn't sure how to tell Excel to look for a semicolon instead of a comma as the delimiter.

After a bit of research it turns out you can use the File/Import tool to bring in a CSV and select all the options you want.

I'm not sure why it's always worked in the past without doing this or whether there is some way to get it to output with commas rather than semicolons.

Either way, for now I've got something I can work with which is good!

Yeah about that: the extension used to work with comma's to seperate the fields, but Excel couldn't work with that with the File/Import-tool. Therefore the comma's were replaced with the semicolon (as seen in this commit)

Semicolon is not csv, comma-separated-values.

Excel on my machine opens .csv files separated with commas perfectly.

@designermonkey: yeah, I know, but for some reason Excel didn't open comma-seperated files correctly, where open office for example did. So I looked in how Excel exports to a CSV format and that was semicolon seperated. Tried that, and it proved to be a working solution for both situations.

If it works, it works ;)

way to go, microsoft. who cares if it's called comma separated.

I'm getting another error on import:

Symphony Fatal Database Error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's'' at line 1
An error occurred while attempting to execute the following query

SELECT SQL_CACHE `entry_id` FROM `sym_entries_data_1` WHERE `value` = 'Mama Tai's';
Backtrace:
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/symphony/lib/toolkit/class.mysql.php:463] MySQL->__error();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/symphony/lib/toolkit/class.mysql.php:609] MySQL->query();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/symphony/lib/toolkit/class.mysql.php:697] MySQL->fetch();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/extensions/importcsv/drivers/ImportDriver_selectbox_link.php:34] MySQL->fetchVar();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/extensions/importcsv/content/content.index.php:320] ImportDriver_selectbox_link->import();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/extensions/importcsv/content/content.index.php:43] contentExtensionImportcsvIndex->__ajaxImportRows();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/symphony/lib/toolkit/class.administrationpage.php:260] contentExtensionImportcsvIndex->view();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/extensions/importcsv/content/content.index.php:26] AdministrationPage->build();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/symphony/lib/core/class.administration.php:230] contentExtensionImportcsvIndex->build();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/symphony/lib/core/class.administration.php:383] Administration->__buildPage();
[/Users/designermonkey/Projects/Symphony/finances/httpdocs/index.php:25] Administration->display();
Database Query Log:
SET character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'; [0.0002]
SET CHARACTER SET 'utf8'; [0.0001]
SELECT SQL_CACHE `session_data` FROM `sym_sessions` WHERE `session` = '3ed167ef15d8d13391f100d6e9009782' LIMIT 1; [0.0003]
DELETE FROM `sym_sessions` WHERE `session_expires` <= '1318180970'; [0.0002]
SELECT SQL_CACHE `name` FROM `sym_extensions` WHERE `status` = 'enabled'; [0.0002]
SELECT SQL_CACHE `id` FROM `sym_authors` WHERE `username` = 'john-porter' AND `password` = 'b064039c12eec87b0034061bc876418a82bca30c' LIMIT 1; [0.0003]
UPDATE sym_authors SET `last_seen` = '2011-10-23 18:22:50' WHERE `id` = '1'; [0.0003]
SELECT SQL_CACHE * FROM `sym_authors` WHERE `id` IN (1) ORDER BY id ASC; [0.0002]
SELECT SQL_CACHE * FROM `sym_extensions`; [0.0003]
SELECT SQL_CACHE * FROM `sym_authors` WHERE `username` = 'john-porter' LIMIT 1; [0.0003]
SELECT SQL_CACHE * FROM `sym_sections` ORDER BY `sortorder` ASC; [0.0002]
SELECT SQL_CACHE * FROM `sym_authors` WHERE `username` = 'john-porter' LIMIT 1; [0.0002]
SELECT SQL_CACHE t1.name, t2.page, t2.delegate, t2.callback FROM `sym_extensions` as t1 INNER JOIN `sym_extensions_delegates` as t2 ON t1.id = t2.extension_id WHERE t1.status = 'enabled'; [0.0003]
SELECT SQL_NO_CACHE * FROM `sym_cache` WHERE `hash` = 'importcsv' AND (`expiry` IS NULL OR UNIX_TIMESTAMP() <= `expiry`) LIMIT 1; [0.0003]
SELECT SQL_CACHE t1.* FROM sym_fields as t1 WHERE 1 AND t1.`id` = '1' LIMIT 1; [0.0002]
SELECT SQL_CACHE * FROM `sym_fields_input` WHERE `field_id` = '1' LIMIT 1; [0.0002]
SELECT SQL_CACHE t1.* FROM sym_fields as t1 WHERE 1 AND t1.`id` = '2' LIMIT 1; [0.0002]
SELECT SQL_CACHE * FROM `sym_fields_selectbox_link` WHERE `field_id` = '2' LIMIT 1; [0.0002]
SELECT SQL_CACHE `related_field_id` FROM `sym_fields_selectbox_link` WHERE `field_id` = 2; [0.0002]
SELECT SQL_CACHE `entry_id` FROM `sym_entries_data_1` WHERE `value` = 'Designermonkey';; [0.0002]
SELECT SQL_CACHE `s`.* FROM `sym_sections` AS `s` WHERE `s`.`id` = '1'; [0.0001]
SELECT SQL_CACHE t1.* FROM sym_fields as t1 WHERE 1 AND t1.`parent_section` = '1' ORDER BY t1.`sortorder` ASC; [0.0002]
SELECT SQL_CACHE * FROM `sym_fields_reflection` WHERE `field_id` = '3' LIMIT 1; [0.0002]
INSERT INTO `sym_entries` (`section_id`, `creation_date`, `creation_date_gmt`) VALUES ('1', '2011-10-23T18:22:50+01:00', '2011-10-23T17:22:50+00:00'); [0.0002]
DELETE FROM sym_entries_data_1 WHERE `entry_id` = '87'; [0.0002]
INSERT INTO `sym_entries_data_1` (`entry_id`, `value`, `handle`) VALUES ('87', 'Tutorials', 'tutorials'); [0.0001]
DELETE FROM sym_entries_data_2 WHERE `entry_id` = '87'; [0.0001]
INSERT INTO `sym_entries_data_2` (`entry_id`, `relation_id`) VALUES ('87', '81'); [0.0001]
DELETE FROM sym_entries_data_3 WHERE `entry_id` = '87'; [0.0001]
INSERT INTO `sym_entries_data_3` (`entry_id`, `handle`, `value`, `value_formatted`) VALUES ('87', NULL, NULL, NULL); [0.0001]
SELECT SQL_CACHE `related_field_id` FROM `sym_fields_selectbox_link` WHERE `field_id` = 2; [0.0001]
SELECT SQL_CACHE `entry_id` FROM `sym_entries_data_1` WHERE `value` = 'Designermonkey';; [0.0001]
SELECT SQL_CACHE t1.* FROM sym_fields as t1 WHERE 1 AND t1.`parent_section` = '1' ORDER BY t1.`sortorder` ASC; [0.0002]
INSERT INTO `sym_entries` (`section_id`, `creation_date`, `creation_date_gmt`) VALUES ('1', '2011-10-23T18:22:50+01:00', '2011-10-23T17:22:50+00:00'); [0.0001]
DELETE FROM sym_entries_data_1 WHERE `entry_id` = '88'; [0.0001]
INSERT INTO `sym_entries_data_1` (`entry_id`, `value`, `handle`) VALUES ('88', 'Software', 'software'); [0.0001]
DELETE FROM sym_entries_data_2 WHERE `entry_id` = '88'; [0.0001]
INSERT INTO `sym_entries_data_2` (`entry_id`, `relation_id`) VALUES ('88', '81'); [0.0001]
DELETE FROM sym_entries_data_3 WHERE `entry_id` = '88'; [0.0001]
INSERT INTO `sym_entries_data_3` (`entry_id`, `handle`, `value`, `value_formatted`) VALUES ('88', NULL, NULL, NULL); [0.0001]
SELECT SQL_CACHE `related_field_id` FROM `sym_fields_selectbox_link` WHERE `field_id` = 2; [0.0001]
SELECT SQL_CACHE `entry_id` FROM `sym_entries_data_1` WHERE `value` = 'Business';; [0.0001]
SELECT SQL_CACHE t1.* FROM sym_fields as t1 WHERE 1 AND t1.`parent_section` = '1' ORDER BY t1.`sortorder` ASC; [0.0002]
INSERT INTO `sym_entries` (`section_id`, `creation_date`, `creation_date_gmt`) VALUES ('1', '2011-10-23T18:22:50+01:00', '2011-10-23T17:22:50+00:00'); [0.0001]
DELETE FROM sym_entries_data_1 WHERE `entry_id` = '89'; [0.0001]
INSERT INTO `sym_entries_data_1` (`entry_id`, `value`, `handle`) VALUES ('89', 'Mama Tai's', 'mama-tais'); [0.0001]
DELETE FROM sym_entries_data_2 WHERE `entry_id` = '89'; [0.0001]
INSERT INTO `sym_entries_data_2` (`entry_id`, `relation_id`) VALUES ('89', NULL); [0.0001]
DELETE FROM sym_entries_data_3 WHERE `entry_id` = '89'; [0.0001]
INSERT INTO `sym_entries_data_3` (`entry_id`, `handle`, `value`, `value_formatted`) VALUES ('89', NULL, NULL, NULL); [0.0001]
SELECT SQL_CACHE `related_field_id` FROM `sym_fields_selectbox_link` WHERE `field_id` = 2; [0.0001]
SELECT SQL_CACHE `entry_id` FROM `sym_entries_data_1` WHERE `value` = 'Mama Tai's';; [0.0000]

I think this is due to there being apostrophes in my CSV that are not being escaped before being read into the SQL.

Thanks a lot for this great extension, this has made importing a lot of entries a breeze.

I do have one feature request: would it be possible to move the input panel from the 'System' tab to the index page of each section? This way, I could give my clients access to the importing magic, which is really useful if they receive data in another system (which does not support web-interaction).

If I had the time I would fork and edit myself, but I don't ;)

I found a bug when importing image fields when there are no images uploaded yet
The following function in ImportDriver_upload.php is supposed to set meta data, filesize and mimetype to 'no value' on line 53-55:

$fileData['size'] = filesize(DOCROOT .$destination . '/' . $value);
$fileData['mimetype'] = ''; // mimecontenttype(DOCROOT .$destination . '/' . $value);
$fileData['meta'] = serialize($this->field->getMetaInfo(DOCROOT . $destination . '/' . $value, $fileData['mimetype']));

I think you forgot to change the values for filesize and metadata?
So i changed it to:

$fileData['size'] = ''; // filesize(DOCROOT . $destination . '/' . $value);
$fileData['mimetype'] = ''; // mimecontenttype(DOCROOT . $destination . '/' . $value);
$fileData['meta'] = ''; // serialize($this->field->getMetaInfo(DOCROOT . $destination . '/' . $value, $fileData['mimetype']));

And got it working

I am running into memory problems when trying to export 9576 entries,
It downloads a blank csv file but with an error message in it;

Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 80 bytes) in /home/symphony/lib/toolkit/class.mysql.php on line 467

What is the limit of entries to export?
For uploading the csv i need to divide it into 4 csv files of 2500 entries to avoid memory problems

I think this extension is an essential tool for importing large amounts of data and it's a pity it's dealing with memory problems, so it would be nice to be able to download the csv in sections too when it becomes too big.
Could this please be implemented in a future version?

Thanks for this great extension!

Create an account or sign in to comment.

Symphony • Open Source XSLT CMS

Server Requirements

  • PHP 5.3-5.6 or 7.0-7.1
  • PHP's LibXML module, with the XSLT extension enabled (--with-xsl)
  • MySQL 5.5 or above
  • An Apache or Litespeed webserver
  • Apache's mod_rewrite module or equivalent

Compatible Hosts

Sign in

Login details