Oct 22, 2014
Jim Lam
wrote
Hi
I have created an additional table in Prestashop's MYSQL database for field mapping during import.
And I am trying to write a SQL to do the transformation, I tried the SQL in phpMyAdmin, and it worked fine, but the expression editor insisted there are SQL errors.
How can I use SQL in the Expression field for transformation?
I have entered this SQL in the Expression for "Category"
SELECT nvalue FROM `it_mapping` WHERE mapfield = 'Category' AND ovalue = [CSV_VOL(10)]
in which:
it_mapping is a SQL table that I created
the fields nvalue, mapfield and ovalue all exist.
Regards
Jim
I have created an additional table in Prestashop's MYSQL database for field mapping during import.
And I am trying to write a SQL to do the transformation, I tried the SQL in phpMyAdmin, and it worked fine, but the expression editor insisted there are SQL errors.
How can I use SQL in the Expression field for transformation?
I have entered this SQL in the Expression for "Category"
SELECT nvalue FROM `it_mapping` WHERE mapfield = 'Category' AND ovalue = [CSV_VOL(10)]
in which:
it_mapping is a SQL table that I created
the fields nvalue, mapfield and ovalue all exist.
Regards
Jim
Reply
24 Answers
Oct 22, 2014
Jim Lam
wrote
I tried to put the statement within a pair of parenthesis, and it comes back with error of "No Database Selected", and if I prefixed the table with the database name, it comes back with error "Table ...Doesn't Exist".
Please advise.
Regards
Jim
Please advise.
Regards
Jim
Oct 23, 2014
Dmytro Grycelyak
agent
wrote
Hello Jim,
Please submit a ticket at: http://support.emagicone.com/submit_ticket and provide us with the file you're importing and specify the exact data you would like to transform, so we could check it with our techs and advise you on this regard.
Please submit a ticket at: http://support.emagicone.com/submit_ticket and provide us with the file you're importing and specify the exact data you would like to transform, so we could check it with our techs and advise you on this regard.
Oct 23, 2014
Jim Lam
wrote
Hi Volodymyr,
Thanks, just submitted the ticket.
Cheers
Jim
Thanks, just submitted the ticket.
Cheers
Jim
Oct 24, 2014
Dmytro Grycelyak
agent
wrote
Hello Jim Lam,
You may use this simple expression to replace the " - " with pipe:
REPLACE ([CSV_COL(1)], ' - ', '|')
Where 1 is a number of "Category" column.
Please see the screen capture:
http://screencast.com/t/Xrt9tqtB
Feel free to contact us with additional questions.
You may use this simple expression to replace the " - " with pipe:
REPLACE ([CSV_COL(1)], ' - ', '|')
Where 1 is a number of "Category" column.
Please see the screen capture:
http://screencast.com/t/Xrt9tqtB
Feel free to contact us with additional questions.
Oct 24, 2014
Jim Lam
wrote
Hi Volodymyr
Thanks for your reply.
Unfortunately, it is not a simple split into multiple level.
It is actually more involved, for some of the categories, we are merging them together or for some others, we are renaming the category since we are merging multiple supplier into a single catalog.
For example, a category named as "Services & Warranty - HP", we are transforming it to "Computers|Options|Warranties|HP". That is why we need something like that to do the transformation.
Regards
Jim
Thanks for your reply.
Unfortunately, it is not a simple split into multiple level.
It is actually more involved, for some of the categories, we are merging them together or for some others, we are renaming the category since we are merging multiple supplier into a single catalog.
For example, a category named as "Services & Warranty - HP", we are transforming it to "Computers|Options|Warranties|HP". That is why we need something like that to do the transformation.
Regards
Jim
Oct 27, 2014
Dmytro Grycelyak
agent
wrote
In order to use more conditions you may use the following expression:
CASE
WHEN [CSV_COL(1)] = 'Services & Warranty - HP' THEN 'Computers|Options|Warranties|HP'
WHEN [CSV_COL(1)] = 'Services & Warranty - Other' THEN 'Computers|Options|Warranties|Other'
ELSE [CSV_COL(1)]
END
Where 1 is a Category column number.
CASE
WHEN [CSV_COL(1)] = 'Services & Warranty - HP' THEN 'Computers|Options|Warranties|HP'
WHEN [CSV_COL(1)] = 'Services & Warranty - Other' THEN 'Computers|Options|Warranties|Other'
ELSE [CSV_COL(1)]
END
Where 1 is a Category column number.
Sep 27, 2017
Perrone Vincenzo
wrote
Hi,
can I transform my categories that are over 1000 with this method or is there a line limit in the instructions?
Thank you
can I transform my categories that are over 1000 with this method or is there a line limit in the instructions?
Thank you
Sep 28, 2017
Dmytro Grycelyak
agent
wrote
Hello Perrone,
Yes, you can use this expression to convert over 1 thousand category names.
Yes, you can use this expression to convert over 1 thousand category names.
Sep 29, 2017
Perrone Vincenzo
wrote
Ciao Volodymyr, (call me Vincenzo)
Is it also valid if the starting point of the category is divided into three columns?
I have to include the full path of the category "Root|Home page|Audio & Video & Elettronica|Audio Hi-Fi & MP3|Attrezzature per DJ|Amplificatori e Finali di Potenza..."?
All this in the column "Category" in edit?
Thank you so much 4 all
Best regads
Is it also valid if the starting point of the category is divided into three columns?
I have to include the full path of the category "Root|Home page|Audio & Video & Elettronica|Audio Hi-Fi & MP3|Attrezzature per DJ|Amplificatori e Finali di Potenza..."?
All this in the column "Category" in edit?
Thank you so much 4 all
Best regads
Oct 02, 2017
Dmytro Grycelyak
agent
wrote
Hello, Perrone.
It is recommended to merge the columns in spreadsheet editor first.
You may use the following formula in Excel:
=B2&"|"&C2
where
| - is category delimiter
B2 - is your parent category column in file and
C2 - is sub category column in file.
As soon as the columns are merged, you may build the SQL expression in Import Wizard.
It is recommended to merge the columns in spreadsheet editor first.
You may use the following formula in Excel:
=B2&"|"&C2
where
| - is category delimiter
B2 - is your parent category column in file and
C2 - is sub category column in file.
As soon as the columns are merged, you may build the SQL expression in Import Wizard.
Oct 02, 2017
Perrone Vincenzo
wrote
Hello Volodymyr Demyanov
I have solved this instruction:
CASE
WHEN [CSV_COL(7)] = 'Gadget mobile' THEN 'Root|Home page|Audio & Video & Elettronica|Droni & Robot'
WHEN [CSV_COL(7)] = 'Sistema di navigazione satellitare e GPS' THEN 'Root|Home page|Audio & Video & Elettronica|Navigatori & GPS'
WHEN [CSV_COL(7)] = 'Storage- Chiave USB' THEN 'Root|Home page|Informatica|Hard Disk & Storage|Chiavette USB'
WHEN [CSV_COL(7)] = 'Tablet' THEN 'Root|Home page|Informatica|Tablet'
WHEN [CSV_COL(7)] = 'Cradle e supporto' THEN 'Root|Home page|Telefonia & Fax|Cellulari|Accessori Cellulari'
WHEN [CSV_COL(7)] = 'Custodia/Skin/Cover' THEN 'Root|Home page|Telefonia & Fax|Cellulari|Accessori Cellulari|Cover per Cellulari'
WHEN [CSV_COL(7)] = 'Telefono' THEN 'Root|Home page|Telefonia & Fax|Cellulari|Cellulari con MP3'
WHEN [CSV_COL(7)] = 'Smartphone' THEN 'Root|Home page|Telefonia & Fax|Cellulari|Smartphone'
ELSE CONCAT ("Root|Home page|Novità|EXS|"[CSV_COL(7)])
END
thank you so much
I have solved this instruction:
CASE
WHEN [CSV_COL(7)] = 'Gadget mobile' THEN 'Root|Home page|Audio & Video & Elettronica|Droni & Robot'
WHEN [CSV_COL(7)] = 'Sistema di navigazione satellitare e GPS' THEN 'Root|Home page|Audio & Video & Elettronica|Navigatori & GPS'
WHEN [CSV_COL(7)] = 'Storage- Chiave USB' THEN 'Root|Home page|Informatica|Hard Disk & Storage|Chiavette USB'
WHEN [CSV_COL(7)] = 'Tablet' THEN 'Root|Home page|Informatica|Tablet'
WHEN [CSV_COL(7)] = 'Cradle e supporto' THEN 'Root|Home page|Telefonia & Fax|Cellulari|Accessori Cellulari'
WHEN [CSV_COL(7)] = 'Custodia/Skin/Cover' THEN 'Root|Home page|Telefonia & Fax|Cellulari|Accessori Cellulari|Cover per Cellulari'
WHEN [CSV_COL(7)] = 'Telefono' THEN 'Root|Home page|Telefonia & Fax|Cellulari|Cellulari con MP3'
WHEN [CSV_COL(7)] = 'Smartphone' THEN 'Root|Home page|Telefonia & Fax|Cellulari|Smartphone'
ELSE CONCAT ("Root|Home page|Novità|EXS|"[CSV_COL(7)])
END
thank you so much
Oct 12, 2017
Perrone Vincenzo
wrote
Hello,
I can not perform some operations while importing csv files such as:
- I want to delete strange characters from a type field (!, £, = etc) I tried the replace command but I get an error in the SQL syntax;
- I want to delete the blank spaces in consecutive excess within the name field or the concatenated description (like in Excel);
can you fill a field by taking the data from another file?
example:
- a supplier submits my price list to .csv files (file1) but without the quantities that are instead on a second file (file2) and the images in a third file (file3)
- can I decide not to import a record based on a particular condition of a field in the same record?
- can I add more "FIELD IDENTIFIERS" in addition to those already present?
Thank you
I can not perform some operations while importing csv files such as:
- I want to delete strange characters from a type field (!, £, = etc) I tried the replace command but I get an error in the SQL syntax;
- I want to delete the blank spaces in consecutive excess within the name field or the concatenated description (like in Excel);
can you fill a field by taking the data from another file?
example:
- a supplier submits my price list to .csv files (file1) but without the quantities that are instead on a second file (file2) and the images in a third file (file3)
- can I decide not to import a record based on a particular condition of a field in the same record?
- can I add more "FIELD IDENTIFIERS" in addition to those already present?
Thank you
Oct 17, 2017
Dmytro Grycelyak
agent
wrote
Hello Perrone.
Q: - I want to delete strange characters from a type field (!, £, = etc) I tried the replace command but I get an error in the SQL syntax;
A: You may use the following expression to remove a single character:
REPLACE([CSV_COL(5)], '$', '')
And the following schema to remove multiple characters:
REPLACE (REPLACE([CSV_COL(5)], '$', ''), '%', '')
Q: - I want to delete the blank spaces in consecutive excess within the name field or the concatenated description (like in Excel);
A: Please describe more exactly.
Q: - a supplier submits my price list to .csv files (file1) but without the quantities that are instead on a second file (file2) and the images in a third file (file3)
A: No, all data should be in one file.
In case the data are in different files, the common identifier in each file is required (ID, Reference, EAN etc).
Q: - can I decide not to import a record based on a particular condition of a field in the same record?
A: Yes, in case you decided to skip products from category 'Towels', you may apply the following expression to Product Name field:
CASE
WHEN [CSV_COL(5)] = 'Towels' THEN ''
ELSE [CSV_COL(2)]
END
Where 5 is a # of category column and 2 is a number of product name CSV column.
The main idea is to truncate product names for unwanted items. In case product name is missing during import, the item will not be imported.
Q: - can I add more "FIELD IDENTIFIERS" in addition to those already present?
A: No, Import Wizard allows identifying products by Reference, Name (or both), ID, EAN, and UPC.
Q: - I want to delete strange characters from a type field (!, £, = etc) I tried the replace command but I get an error in the SQL syntax;
A: You may use the following expression to remove a single character:
REPLACE([CSV_COL(5)], '$', '')
And the following schema to remove multiple characters:
REPLACE (REPLACE([CSV_COL(5)], '$', ''), '%', '')
Q: - I want to delete the blank spaces in consecutive excess within the name field or the concatenated description (like in Excel);
A: Please describe more exactly.
Q: - a supplier submits my price list to .csv files (file1) but without the quantities that are instead on a second file (file2) and the images in a third file (file3)
A: No, all data should be in one file.
In case the data are in different files, the common identifier in each file is required (ID, Reference, EAN etc).
Q: - can I decide not to import a record based on a particular condition of a field in the same record?
A: Yes, in case you decided to skip products from category 'Towels', you may apply the following expression to Product Name field:
CASE
WHEN [CSV_COL(5)] = 'Towels' THEN ''
ELSE [CSV_COL(2)]
END
Where 5 is a # of category column and 2 is a number of product name CSV column.
The main idea is to truncate product names for unwanted items. In case product name is missing during import, the item will not be imported.
Q: - can I add more "FIELD IDENTIFIERS" in addition to those already present?
A: No, Import Wizard allows identifying products by Reference, Name (or both), ID, EAN, and UPC.
Oct 17, 2017
Perrone Vincenzo
wrote
Q: - I want to delete the blank spaces in consecutive excess within the name field or the concatenated description (like in Excel);
A: Please describe more exactly.
Example I have in the "Product Name" "Apple Iphone " field.
in the "Product Description" field. " 128Gb red ecc .... "
I would like to turn everything into "Apple Iphone 128gb etc" without more blank
To join the two fields I use the "Concat" command but I do not know how to remove the blank spaces in the two fields.
With Excel there is this command, but at any time updating the catalog that sends me the distributor, I should first import into excel and make the transformation and all this slows me down.
Q: - a supplier submits my price list to .csv files (file1) but without the quantities that are instead on a second file (file2) and the images in a third file (file3)
A: No, all data should be in one file.
In case the data are in different files, the common identifier in each file is required (ID, Reference, EAN etc).
each file has the "Reference" field
thank you
A: Please describe more exactly.
Example I have in the "Product Name" "Apple Iphone " field.
in the "Product Description" field. " 128Gb red ecc .... "
I would like to turn everything into "Apple Iphone 128gb etc" without more blank
To join the two fields I use the "Concat" command but I do not know how to remove the blank spaces in the two fields.
With Excel there is this command, but at any time updating the catalog that sends me the distributor, I should first import into excel and make the transformation and all this slows me down.
Q: - a supplier submits my price list to .csv files (file1) but without the quantities that are instead on a second file (file2) and the images in a third file (file3)
A: No, all data should be in one file.
In case the data are in different files, the common identifier in each file is required (ID, Reference, EAN etc).
each file has the "Reference" field
thank you
Oct 17, 2017
Perrone Vincenzo
wrote
Why when you import a csv file do not recognize the html commands (Why when you import a csv file do not recognize the html commands ( YAMAHA YTR3335S STUDY TROMBA IN SIB SILVER + CUSTODIA For)
thank you
thank you
Oct 19, 2017
Perrone Vincenzo
wrote
Q: - I want to delete the blank spaces in consecutive excess within the name field or the concatenated description (like in Excel);
A: Please describe more exactly.
Example I have in the "Product Name" "Apple Iphone blank blank blank" field.
in the "Product Description" field. " blank blank 128Gb red ecc ....blank blank "
I would like to turn everything into "Apple Iphone 128gb etc" without more blank
To join the two fields I use the "Concat" command but I do not know how to remove the blank spaces in the two fields.
With Excel there is this command, but at any time updating the catalog that sends me the distributor, I should first import into excel and make the transformation and all this slows me down.
Q: - a supplier submits my price list to .csv files (file1) but without the quantities that are instead on a second file (file2) and the images in a third file (file3)
A: No, all data should be in one file.
In case the data are in different files, the common identifier in each file is required (ID, Reference, EAN etc).
each file has the "Reference" field
thank you
A: Please describe more exactly.
Example I have in the "Product Name" "Apple Iphone blank blank blank" field.
in the "Product Description" field. " blank blank 128Gb red ecc ....blank blank "
I would like to turn everything into "Apple Iphone 128gb etc" without more blank
To join the two fields I use the "Concat" command but I do not know how to remove the blank spaces in the two fields.
With Excel there is this command, but at any time updating the catalog that sends me the distributor, I should first import into excel and make the transformation and all this slows me down.
Q: - a supplier submits my price list to .csv files (file1) but without the quantities that are instead on a second file (file2) and the images in a third file (file3)
A: No, all data should be in one file.
In case the data are in different files, the common identifier in each file is required (ID, Reference, EAN etc).
each file has the "Reference" field
thank you
Oct 23, 2017
Dmytro Grycelyak
agent
wrote
Hello Perrone,
Q: Example I have in the "Product Name" "Apple Iphone blank blank blank" field.
in the "Product Description" field. " blank blank 128Gb red ecc ....blank blank "
I would like to turn everything into "Apple Iphone 128gb etc" without more blank
A: You may use the REPLACE operator in order to remove the blank spaces:
REPLACE (REPLACE([CSV_COL(5)], ' ', ''), ' ', '')
Where 5 is the column you would like to apply the expression to.
This particular expression will replace double and triple space with '' - nothing.
Q: each file has the "Reference" field
A: In this case, you can import product data in 3 separate imports, identifying by the Reference.
Q: Why when you import a csv file do not recognize the html commands (Why when you import a csv file do not recognize the html commands ( YAMAHA YTR3335S STUDY TROMBA IN SIB SILVER + CUSTODIA For)
A: Store Manager imports descriptions as they are into the database, without making changes.
Q: Example I have in the "Product Name" "Apple Iphone blank blank blank" field.
in the "Product Description" field. " blank blank 128Gb red ecc ....blank blank "
I would like to turn everything into "Apple Iphone 128gb etc" without more blank
A: You may use the REPLACE operator in order to remove the blank spaces:
REPLACE (REPLACE([CSV_COL(5)], ' ', ''), ' ', '')
Where 5 is the column you would like to apply the expression to.
This particular expression will replace double and triple space with '' - nothing.
Q: each file has the "Reference" field
A: In this case, you can import product data in 3 separate imports, identifying by the Reference.
Q: Why when you import a csv file do not recognize the html commands (Why when you import a csv file do not recognize the html commands ( YAMAHA YTR3335S STUDY TROMBA IN SIB SILVER + CUSTODIA For)
A: Store Manager imports descriptions as they are into the database, without making changes.
Oct 23, 2017
Perrone Vincenzo
wrote
Q: each file has the "Reference" field
A: In this case, you can import product data in 3 separate imports, identifying by the Reference.
so can not I import a single import from 3 different files?
thank you so much for all
A: In this case, you can import product data in 3 separate imports, identifying by the Reference.
so can not I import a single import from 3 different files?
thank you so much for all
Oct 24, 2017
Dmytro Grycelyak
agent
wrote
Hello Perrone,
3 files cannot be imported at once. You may run 3 separate imports in order to update all product details.
3 files cannot be imported at once. You may run 3 separate imports in order to update all product details.
Oct 29, 2017
Perrone Vincenzo
wrote
ok thank you
Oct 06, 2018
Perrone Vincenzo
wrote
Hello, I need to manage multiple price lists, such as retailer, agent, VIP.
can you give me some solution different from that of specific prices that is not very manageable for my demanding?
thank you.
best regards
can you give me some solution different from that of specific prices that is not very manageable for my demanding?
thank you.
best regards
Oct 09, 2018
Dmytro Grycelyak
agent
wrote
Hello Perrone,
You can add multiple specific prices to your products in bulk, manually or during import:
https://www.prestashopmanager.com/useful-articles/how-to/bulk-remove-specific-prices-prestashop/
https://www.prestashopmanager.com/useful-articles/how-to/how-to-import-specific-prices-to-prestashop/
You can add multiple specific prices to your products in bulk, manually or during import:
https://www.prestashopmanager.com/useful-articles/how-to/bulk-remove-specific-prices-prestashop/
https://www.prestashopmanager.com/useful-articles/how-to/how-to-import-specific-prices-to-prestashop/
Oct 09, 2018
Perrone Vincenzo
wrote
Thanks for the suggestion, I already knew this solution, I was looking for a simpler solution, maybe adding new fields in the product table with different prices for each type of user.
best regards
best regards
Oct 10, 2018
Dmytro Grycelyak
agent
wrote
Hello Perrone,
We can advise you regarding the default PrestaShop functionality, supported by Store Manager.
As an option, you can add the reduction for each customer group from "Customers & Groups" section.
We can advise you regarding the default PrestaShop functionality, supported by Store Manager.
As an option, you can add the reduction for each customer group from "Customers & Groups" section.