List export to CSV "Excel CSV" uses semicolon delimiter

[pastvne]

Fabrik 3.2 on Joomla 3.3
I have a export to csv on a list.
The params for the export specific 'Excel CSV" as the format
The file comes in with semi-colon rather than comma delimiters between columns
I don't see a place in the properties to modify the delimiter that is used
Apparently this is a pretty common issue aside from Fabrik and many regions, especially in Europe, use semi-colon as the column delimiter. However, when running in USA region, Excel balks at the semi-colon and when opening the csv it tosses the entire line into column 1 with all quotes and delimiters.

My temp work around is to:
1) Rename the csv as .txt
2) Open excel
3) Open the .txt file
4) Click 'semi-colon delimiter' in the conversion wizard

But users, of course, are complaining that this is too complicated.

Is there an option I'm not finding to set the delimiter that is used?

I can probably find and hack it into the code but would prefer to see a param on the excel export that allows for specification of the formatting sauce going into the spreadsheet.

Thanks,

BD

 

[troester]

If you want a standard CSV file (comma separated, UTF8) don't use Excel CSV.

Because Excel IS using semi-colons and non-UTF8 (try to save an Excel file as csv, grr) the "Excel CSV format" was added (using semi-colon and some UTF-16; you can select "File encoding" in list Publishing/CSV settings to match Excel and your DB collation)
Excel CSV is creating a file which can be directly opened (not imported) in Excel, just right-click and select "Open with" = Excel (or set csv to open with Excel on your PC).

 

[pastvne]

Thanks Troester. I thought I tried that "Directly open" but then I got everything crammed into Column 1 including quoting and delimiters. But, since I've jumped the gun recently with reports, let me go back and re-test it from scratch. I read a lot of posts that seemed to indicate that if you run a US-region excel, it would not handle semicolon delimiters properly whereas other regions of the world, it's the normal practice and excel will work. But again, let me re-test before I run anyone through another fire drill that turns out to be on me.

 

[troester]

I'm running a non-US Excel on Windows. No idea if there's a difference to US-Excel or maybe with Excel on Mac...
But Hugh should be in your region

 

[cheesegrits]

But Hugh doesn't have Excel. He uses Office Libre. Hugh can't afford Excel.

-- hugh

 

[pastvne]

Hi Troester:
I went back and ran a few tests to validate a few things:

Saving in Excel as CSV using Office 2013 version 14.0.4711.1003
With my US-based Excel when I create a sheet that looks like this (note I am using pipe to show where the column breaks are)

a1|b1|c1
a2|b2|c2

Then save it using File > Save As
Setting Save as type to "CSV (Comma Delimited (*.csv)

Then I open it in word-pad, I get this:
a1,b1,c1
a2,b2,c2

It does not quote the values but uses comma as the column separator and not semi-colon. Are you saying that when you do the same thing that your Excel is creating a file that looks like this:

"a1";"b1";"c1"
"a2";"b2";"c2"

If so, then I think it may be possible that in other regions such as Germany, based on system locale, that Excel may save CSV differently. There is quite a bit of chatter on this topic in google such as this:

http://answers.microsoft.com/en-us/...stead-of/ac62dc75-2af0-45f1-91d0-337a87042e5a

So, if this is true, then it appears that depending on your region, Excel 'save-as-csv' may inject semicolons rather than commas for Excel CSV. It appears that the object in Fabrik defaults to semicolon delimiters which is fine when you are in a region where Excel uses semicolon but if you are in a region where this is not the case, Excel cannot directly import these documents. If the export to CSV properties in the list allowed you to specific the delimiter character then then presumably, the export would be directly readable by excel at least if your delimiter in excel matched the delimiter in fabrik.

Or, if we were able to call a PHP snip to filter the blocks being written to the csv file, then we could convert the semi-colons to comma or do any other processing of the block.

As a last resort, hacking the csv builder to hard-code the comma would be a solution but not one I'd favor.

For now, I'll move back to normal CSV export and assume that Excel-CSV is for users that are in a Excel region where semi-colon is the default excel delimiter.

Interestingly, Open Office Calc handles everything just fine.

-BD

 

[troester]

Interestingly, Open Office Calc handles everything just fine.

It's not MicroSoft...
Yes:
My Excel (Switzerland, Office2010) is creating a CSV with ; and ANSI encoding, no quotes (it doesn't say comma delimited but character delimited), so I think Rob's Excel is doing it, too.

But if your Excel can handle commas (and I assume ANSI or UTF8 doesn't matter in the US) can't you use the standard CSV export?

 

[troester]

I just found:
csvexport is doing
$this->delimiter = $this->outPutFormat == 'excel' ? COM_FABRIK_EXCEL_CSV_DELIMITER : COM_FABRIK_CSV_DELIMITER;
so it should be possible to do a language override on
COM_FABRIK_EXCEL_CSV_DELIMITER

Edit: no, it seems it's no language string, it's defined in plugins\system\fabrik\defines.php

 

[pastvne]

The issue came up because the customer requested a format that would load into excel more intelligently. They claim, for example, that if properly formatted for Excel, that column widths would be better handled. So, I looked at the List CSV-Export option and found Excel-CSV option and assumed it put some additional 'kindness' into the format that would please Excel.

Now, I have run some more tests and have these results:

If I choose "CSV" rather than "Excel-CSV" in Fabrik, then:
1) I can directly 'load' the .csv file in excel
2) Columns are parsed based on the comma delimiter
3) All columns are a uniform standard width which requires the customer to then adjust column widths

If I go into Excel first then then choose "Data" then "From Text" and then browse to the .csv file and choose "Import" then accept "Delimited" for data type and then choose 'comma' as the delimiter, then Excel's import function present nicely formatted columns of appropriate widths suited to the content of the column.

It should be noted that when I call up this .csv file generated with Fabrik's 'CSV' option , the columns values are quoted and the delimiter is comma. From a superficial view, the only think different between CSV and Excel-CSV that I have noticed is that the delimiter is semi-colon rather than comma.

The same procedure (above) works with Excel-CSV generated files with the exception that you must select 'semi-colon' rather than 'comma'.

However, when attempting to directly load the Excel-CSV generated file with Excel, it does not parse the semi-colons into column breaks resulting in the entire line of the CSV being loaded into Column A of Excel (at least here in the US)

So the conclusion for me is that this is not a Fabrik issue (of course) but nonsense introduced by Microsoft.

I will advise the customer to run the above tests to see for themselves and then to either use OpenOffice calc or complain to Microsoft, or use the procedure I outlined above using "Data>From Text>Import" to achieve their desired result.

Thanks for being a sounding board.
-BD

 

[troester]

I think if Excel is doing such local stuff there should be an option in Fabrik options "Excel delimiter".

As a workaround you could hack plugins\system\fabrik\defines.php line34

https://github.com/Fabrik/fabrik/issues/1495

 

[pastvne]

Thanks Troester. I've forced the customer to use the clunky Excel Data>Import thing and explained that we have Microsoft to thank.
-BD

 

[rob]

I've added a global option to override the default ';' and ',' delimiters with this commit https://github.com/Fabrik/fabrik/commit/212862bd8f78bb004a01cc49149501f7b6cd62d7