CSV import

[Isgaard]

Thyon, hello.
I have a question... Now the catalog is made export items in csv file. How to import the same csv back into the catalog with the updating of fields in the items and the number of items?

[thyon]

This is already in the version 2 alpha, but I had it developed as a patch for previous installations.

[Isgaard]

I do not understand. It turns out the patch has already been developed?

[thyon]

This feature has been completed quite some time ago, but was never added to the repository release as it was incorporated into the new Alpha version. As the repository release is quite stable, I won't want to add it to that. You'll have to download and install the Alpha, which is good enough to run as a production version, as it includes mostly features and changes to the back-end. The alpha can be safely installed and upgrade an existing installation, otherwise, if you're a developer, you can steal the code from the alpha and re-insert the export feature into the existing installation ("import") keyword in config.php and then also the "import" function in Catalog.php.

[Isgaard]

Oh, thank you very much! I'll try this method.

[Isgaard]

Thyon
1. I copied the line 'import' => array ( 'Catalog', 'importCSV'), in the config.php file
2. Copied the function from the file catalog.php - public function importCSV (DataContainer $ dc) {...}
3. Copy the relevant lines from files in the directory "languages"

But the import option does not appear: (
Where I made a mistake, explain to me please. I do not have very good knowledge of php...

[thyon]

That's pretty much it. Just check that your "import" in the config.php does have a comma after it, as well as the previous/next line (before or after "export"), otherwise it will bomb the catalog completely (it's a comma delimiter PHP error then). Also, the "import" function button only appears INSIDE the editing of the catalog (next to export). You also might need to copy some CSS code from the html folder that is used to generate the import icon.

[Isgaard]

OK, example of my config.php

$GLOBALS['BE_MOD']['content']['catalog'] = array
(
    'tables'       	=> array('tl_catalog_types', 'tl_catalog_fields', 'tl_catalog_items'),
    'icon'         	=> 'system/modules/catalog/html/icon.gif',
    'regenerateDca'	=> array('Catalog', 'regenerateAllDca'),
    'import'            => array('Catalog', 'importCSV'), // a new entry for import (Alpha)
    'export'				=> array('Catalog', 'exportItems'),
);

and function in catalog.php

public function exportItems(DataContainer $dc)
{...} // original Export
public function importCSV(DataContainer $dc)
{...} // a pasted entry from Alpha

and inside the catalog is no link to import

[thyon]

It could just be a browser refresh problem. just refresh your browser's page... if the menu item is defined, it has to appear (with or without icon).

[Isgaard]

Ctrl+F5 doesn't solve a problem.
There is no import link in the source code of web-page and direct link like /typolight/main.php?do=catalog&table=tl_catalog_items&id=6&ke y=import returns nothing

TypoLight version is 2.7.5

[thyon]

Ahh, now I see what is missing... The config was just the link definition, you still need to make the header button in the catalog dca....

Find this in Catalog.php (which auto-creates the DCA) and then add the import section from the new catalog.php file. This creates the button with the correct CSS class and also links it to the key=import, which is what is missing...

	'global_operations' => array
	(
		'export' => array
		(
			'label'               => &$GLOBALS['TL_LANG']['tl_catalog_items']['export'],
			'href'                => 'key=export',
			'class'               => 'header_css_export',
			'attributes'          => 'onclick="Backend.getScrollOffset();"'
		),
..... import goes here

I'm just not sure why the key=import doesn't work, maybe it has to be a button otherwise it's not allowed. Just add the above changes and see what happens. You might have to regenerateDCA, because this code is stored in the DB (old version).

[Isgaard]

fail...
something went wrong

add code

  
                  // regenerate impot button
                    'import' => array 
                    (
                        'label'               => &$GLOBALS['TL_LANG']['tl_catalog_items']['import'],
                        'href'                => 'key=import',
                        'class'               => 'header_css_import',
                        'attributes'          => 'onclick="Backend.getScrollOffset();"'
                    ),

then regenerate DCA
ok, appear icon of import

but when they click on it turns out (see screenshot)

backend can not render the form for csv load

[thyon]

the screenshot wasn't attached. Maybe you need to do it again. You can attach files to your messages (using upload attachment), and you can use them inline in your message as well...

[Isgaard]

Well... i'm not sure, but i have see the csv form!!!
I commented this part of source and add ! to $objCatalog->import

// check if import is enabled
        $objCatalog = $this->Database->prepare("SELECT * FROM tl_catalog_types WHERE id=?")
                ->limit(1)
                ->execute($this->Input->get('id'));

   /*  if (!$objCatalog->numRows || !$objCatalog->import)
        {
            return;
        }
*/
        if (!$objCatalog->import)
        {

And now i have yet another trouble: list of files and directories have not control buttons like expand. And i didn't see my uploaded csv file

[thyon]

see, why I didn't want to do this... its turning into quite a task. Now I remember that there are also settings in the DCA to set-up and database. Once you have completed the DB settings, you have to run install to create those additional fields.

The additional items:

in tl_catalog_items.php => this is what is missing for the set-up of that field.

	// Fields
	'fields' => array
	(
		'source' => array
		(
			'label'                   => &$GLOBALS['TL_LANG']['tl_catalog_items']['source'],
			'eval'                    => array('fieldType'=>'radio', 'files'=>true, 'filesOnly'=>true, 'extensions'=>'csv')
		)
	)

The catalog import feature is also manually enabled and to allow overwrite in the catalog, so you need database and tl_catalog_.php changes


tl_catalog_types.php

	// Palettes
	'palettes' => array
	(
		'__selector__'		=> array('addImage', 'import', 'searchable', 'allowComments', 'makeFeed'),
// add the 'import' word above.
.....
	// Subpalettes
	'subpalettes' => array
	(
....
...
		'import'					=> 'importAdmin,importDelete',
// add the 'import' section above
...
...
// then, add the import, importAdmin and importDelete options to control how importing can work...
		'import' => array
		(
			'label'                   => &$GLOBALS['TL_LANG']['tl_catalog_types']['import'],
			'exclude'                 => true,
			'inputType'               => 'checkbox',
			'eval'										=> array('submitOnChange'=>true),
			'doNotCopy'               => true,
		),
		
		'importAdmin' => array
		(
			'label'                   => &$GLOBALS['TL_LANG']['tl_catalog_types']['importAdmin'],
			'exclude'                 => true,
			'inputType'               => 'checkbox',
			'doNotCopy'               => true,
			'eval'                    => array('tl_class'=>'w50')
		),
		
		'importDelete' => array
		(
			'label'                   => &$GLOBALS['TL_LANG']['tl_catalog_types']['importDelete'],
			'exclude'                 => true,
			'inputType'               => 'checkbox',
			'doNotCopy'               => true,
			'eval'                    => array('tl_class'=>'w50')
		),
...

Also remember to add these also from the language files, e.g. languages/en/tl_catalog_types.php, etc.

Database file: database.sql:

CREATE TABLE `tl_catalog_types` (
...
...

  `import` char(1) NOT NULL default '',
  `importAdmin` char(1) NOT NULL default '',
  `importDelete` char(1) NOT NULL default '',
...

[Isgaard]

Well, file manager is active now and i see my uploaded csv. But when i try to import it, i get
Fatal error: Uncaught exception Exception with message Query error: Column count doesn't match value count at row 1 (REPLACE INTO books_old (pid, tstamp, id,pid,sorting,tstamp,cat,author,title,pages,annot ation_short,annotation_full,image_small,image_larg e,disable,taggg,tagcloud)

Fields pid & tstamp are duplicated, but the original csv contains this fields:

"id","pid","sorting","tstamp","cat","author","title","pages","annotation_short","annotation_full","image_small","image_large","disable","taggg","tagcloud"
"3","6","32","1247047851","20","F","TEST","482","1","1","1","111","1","1","1"

[thyon]

Remove pid and tstamp from your CSV file. I don't think you can import multiple fields more than once in the same REPLACE statement.

[Isgaard]

aha, i found mistake

Fatal error: Uncaught exception Exception with message Query error: Column count doesn't match value count at row 1 (REPLACE INTO books_old (pid, tstamp, id,sorting,cat,author,title,pages,annotation_short,annotation_full,image_small,image_large) VALUES ('6',1264600748,'3','32','20','AA','BB','482','CC','DD'))

Import doesn't see all of the values in csv file. It missing last two values for catalog fields type "file"

[thyon]

well it all depends on the way your CSV is exported. Now it seems you just need to massage your data a bit... It's not a foolproof import, you might have to add/remove quotes to make it import correctly. you can link the CSV file, then I could maybe explain what is wrong, if I can spot anything.

[Isgaard]

ok, file is attached
csv made with standart catalog export function. cols "pid" & "tstamp" was deleted.

And an error when i try to import

Fatal error: Uncaught exception Exception with message Query error: Column count doesn't match value count at row 1 (REPLACE INTO books_old (pid, tstamp, id,sorting,author,title,pages,annotation_short,annotation_full,image_small,image_large,cathegory) VALUES ('6',1264603382,'4','16','Autor NAme','Title of the Book','464','short description','long description','tl_files/books/53-str_razriv.jpg'))

[thyon]

there seems to be a problem with $headercount and count($fieldlist), each determined from the CSV as well as your defined columns in the catalog. If they don't match its an error, but they do match, then I generate a list of fields from $fieldlist and add items from the $row (which is each row's data). Someone the data is not getting into the $row correctly, or it's being parse incorrectly by $fgetcsv...

Its quite a process to test this, as I'll have to recreate your exact catalog (mission!). Best if you can try to print_r($variable) a few $variables.

Try to print_r() the following just BEFORE the SQL REPLACE line in importCSV():
print_r($headercount);
print_r(count($fieldlist));
print_r($fieldlist);
print_r($row);

[Isgaard]

Yes, i'll try to deal with this trouble.
Is Alpha had the same trouble? May be it was easy to upgrade catalog up to Alpha? )

Thank you

[Isgaard]

88

Array ( 
[0] => cathegory 
[1] => author 
[2] => title 
[3] => pages 
[4] => annotation_short 
[5] => annotation_full 
[6] => image_small 
[7] => image_large 
) 

Array ( 
[0] => 4 //id
[1] => 16 //sorting
[2] => Autor NAme //autor
[3] => Title of the Book 12 //title
[4] => 464 //pages
[5] => short description //annotation_short
[6] =>long description //annotation_full
[7] => tl_files/books/53-str_razriv.jpg //small
[8] => tl_files/books/53_elektroenergetika_rossii.jpg //large
[9] => 9 //cathegory
)

[thyon]

Looks like it can't deal with the ID and the sorting value, as they are internal fields as well. just remove them as well... then it should work. I'll have to take a look at the code and ensure that the id and sorting are someone checked.

[Isgaard]

yet another last question )

import feature in the alpha adds changed records as new items, is not it?
I created a new catalog in alpha, create 2 items, export them,

"art","imgsmall","imgbig","description"
"123","files/jewerelly/small/0101256_s.jpg","files/jewerelly/big/0101256_b.jpg","test1"
"123456","files/jewerelly/small/0101256_s.jpg","files/jewerelly/big/0101256_b.jpg","test12"

and then changed the value of one of the fields. as result of import i got 4 item in the list of items in catalog, but not an update value of the changed fields in the same items

[thyon]

yeah, the REPLACE INTO Sql statement works for the ID being present only, so I'll have to check why the ID isn't working right. its needs the ID to identify a record as an original value present in the database, and then replace it.

[Isgaard]

Thyon

i found solution: we need place string

array_unshift($fieldlist, 'id');

AFTER

$fieldlist = $objFields->fetchEach('colName');

but export function MUST write ID for each item in exported csv file
place

array_unshift($arrFields, 'id');

after

$arrFields = array();

and then modify $objRow = $this->Database->prepare with ID

$objRow = $this->Database->prepare("SELECT ". id ."  ". join(', ', $arrFields) ." FROM ".$objCatalog->tableName." WHERE pid=?")