Categories
View Replies (3)

Resolved Export and Import same file using Excel

  1. p.barg
    p.barg
  2. RO CSVI
  3. Monday, 31 August 2015
  4.  Subscribe via email
Hello,
my customer would like to work in the following way:
- export all products with all available fields
- open that export file in Excel
- apply changes and additions to this Excel sheet
- save that sheet
- re-import that file
This seems to be a reasonable wish, but very complicated (if not impossible) to achieve with CSVI. I've been able to set up the required templates to assure a match between the fields. But I don't seem to be able to use delimiters that are a)good for Excel (; and ";) and b)not included in the data (the customer uses ; and " in his data) and c)good for a re-import. If I save the altered data from Excel in a CSV file I end up with a file that has double "" and confusion with ;

So is there an easy way to export/import Virtuemart products using CSVI and Excel in together and if so is there a tutorial?

Virtuemart version 3.0.8
CSVI Pro: 5.21.2

Thanks and best regards

Petra
0
Responses (3)
rolandd
rolandd
Accepted Answer Pending Moderation
Hello Petra,

- export all products with all available fields
That is already a bad idea. You should only export the fields you really need.

This seems to be a reasonable wish
Very reasonable ;)

But I don't seem to be able to use delimiters that are a)good for Excel
In Europe Excel uses a semi-colon and double-quotes.

and b)not included in the data
Your delimiters can be included in the data as the CSV standard has a solution for that too. Look at this example of a valid CSV file 
"product_sku";"product_desc"

"ABC";"This is a description; A description with ""extra"" use of the delimiters"
Notice the double double-quotes inside the description.That is how CSV readers know this is a double-quote beloning to the text.

If I save the altered data from Excel in a CSV file I end up with a file that has double "" and confusion with ;
How is the data confused?

So is there an easy way to export/import Virtuemart products using CSVI and Excel in together and if so is there a tutorial?
There is no tutorial on how to use Excel. There are tutorials on how to import/export products but you seem to have that covered. The problem with Excel is that you get no control whatsoever on how the file is saved. One thing you need to do is set the option Excel CSV to Yes in your export template and in the import template set Auto-detect delimiters to No and specify your delimiters manually.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 1
p.barg
p.barg
Accepted Answer Pending Moderation
Hello RolandD,
thanks for getting back to me.
In fact, the main problem is that after saving my file back to CSV all text delimiters are gone.
Example:
CSV original:
"product_sku";"product_desc"
"ABC";"This is a description; A description with ""extra"" use of the delimiters"
After saving it back using Excel:
product_sku;product_desc
ABC;This is a description; A description with ""extra"" use of the delimiters

I guess that's what you mean by saying: "The problem with Excel is that you get no control whatsoever on how the file is saved", right?

The removal of the " as text delimiters leads to problems with ; messing it up and others.

We have written an Excel macro in order to overcome that problem, but it's always hard to tell customers to use such a macro... So, the feature I was really looking for was an equivalent to your "Excel CSV" for the import template.
  1. more than a month ago
  2. RO CSVI
  3. # 2
rolandd
rolandd
Accepted Answer Pending Moderation
Hey Petra,

After saving it back using Excel:
product_sku;product_desc
ABC;This is a description; A description with ""extra"" use of the delimiters
That is actually an invalid CSV.
The removal of the " as text delimiters leads to problems with ; messing it up and others.
Correct.
I gave it a try with Excel 2010 here an it produces this CSV file: 
product_sku;product_desc

ABC;"This is a description; A description with ""extra"" use of the delimiters"
and this is a correct CSV file.

Not sure what is going on with your Excel. In Excel, make sure you save the file as CSV (Comma delimited) and not as one of the other ones.

So, the feature I was really looking for was an equivalent to your "Excel CSV" for the import template.
It isn't a feature CSVI needs. Programs should save correct CSV format files.

The macro shouldn't be needed and I agree, it is difficult to explain it to users.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 3
  • Page :
  • 1


There are no replies made for this post yet.
Be one of the first to reply to this post!