Bug 117868 - LibreofficeCalc - sep support from MSOffice on CSV files
Summary: LibreofficeCalc - sep support from MSOffice on CSV files
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL:
Whiteboard: target:7.3.0 target:7.4.0
Keywords:
Depends on:
Blocks: CSV-Import
  Show dependency treegraph
 
Reported: 2018-05-29 06:50 UTC by Marin Sagovac
Modified: 2021-12-01 17:44 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Marin Sagovac 2018-05-29 06:50:34 UTC
Description:
MSOffice Excell supports delimiter support and automatically recognized which separator should use if defined in first line for CSV files.

Example of csv file:

sep=|
"LETTER"|"ANIMAL"
"a"|"aardvark"
"b"|"bear"
"c"|"cow"

That part "sep=|" at first line of code recognize automatically by MS and separated as delimiter.

Steps to Reproduce:
1. Create a CSV document with first line delimeter "sep=|" anda fillup data
2. Open with libreoffice and autodetect sep delimiter type
3. Parse delimeter and open a Libreoffice Calc document using CSV

Actual Results:  
sep=|
"LETTER"|"ANIMAL"
"a"|"aardvark"
"b"|"bear"
"c"|"cow"

Expected Results:
LETTER  ANIMAL
a       aardvark
b       bear
c       cow


Reproducible: Always


User Profile Reset: Yes



Additional Info:
https://kb.paessler.com/en/topic/2293-i-have-trouble-opening-csv-files-with-microsoft-excel-is-there-a-quick-way-to-fix-this

Affects only on MS platform.

Tried on LibreOffice 5 from ubuntu 17.10 without affects.


User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0
Comment 1 Joao Farias 2018-05-31 21:59:45 UTC
Marin, thank you for your report.

I could reproduce the issue on version 6.4.0.2 [1] and 6.1.0.0.beta1 on macOS and 6.1.0.0.alpha1 on Windows.

Setting the status to NEW.


[1] - 6.0.4.2
Build ID: 9b0d9b32d5dcda91d2f1a96dc04c645c450872bf
CPU threads: 8; OS: Mac OS X 10.13.3; UI render: default; 
Locale: en-US (en_US.UTF-8); Calc: group

[2] - Version: 6.1.0.0.beta1
Build ID: 8c76dfe1284e211954c30f219b3a38dcdd82f8a0
CPU threads: 8; OS: Mac OS X 10.13.3; UI render: default; 
Locale: en-US (en_US.UTF-8); Calc: group

[3] - Version: 6.1.0.0.alpha1
Build ID: cb47f0d320994e001bc38dc2ee9b7d957b15e6ab
CPU threads: 4; OS: Windows 10.0; UI render: default;
Locale: en-US (en_US); Calc: group
Comment 2 QA Administrators 2019-11-13 03:32:45 UTC Comment hidden (obsolete)
Comment 3 Roman Kuznetsov 2019-11-24 16:05:26 UTC
still repro in

Версия: 6.5.0.0.alpha0+ (x64)
ID сборки: 32dcf3f0fdafcf44457842a8aa4f54d30d856ca9
Потоков ЦП: 4; ОС:Windows 10.0 Build 17763; Отрисовка ИП: GL; VCL: win; 
Локаль: ru-RU (ru_RU); Язык интерфейса: ru-RU
Calc: threaded

but I think it's an enhancement
Comment 4 Commit Notification 2021-08-29 20:09:56 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/451e4abb5377f6d923860e9adfce82d46f31e049

Resolves: tdf#117868 CSV: support sep=; and "sep=;" separator setting

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 5 Xisco Faulí 2021-08-30 12:03:32 UTC
Hi Eike,
Now, when I open a csv file like

sep=|
"LETTER"|"ANIMAL"
"a"|"aardvark"
"b"|"bear"
"c"|"cow"

the result is

sep=
LETTER  ANIMAL
a       aardvark
b       bear
c       cow

Should the first row sep= be ignored and not imported into the spreadsheet ?
Comment 6 Eike Rathke 2021-08-31 08:39:37 UTC
(In reply to Xisco Faulí from comment #5)
> Should the first row sep= be ignored and not imported into the spreadsheet ?
Just set the "From row" number to 2 instead of 1.

Note however that if in a sheet the top left cell content is "sep=" or "sep=|" (without quotes and any separator) and is the only cell on the first row then when writing the sheet back to CSV the then current separator is added even if a different one was chosen. That functionality of course is lost if the first row is not imported.

I'll add details to the release notes.
Comment 7 Xisco Faulí 2021-08-31 13:24:49 UTC
(In reply to Eike Rathke from comment #6)
> (In reply to Xisco Faulí from comment #5)
> > Should the first row sep= be ignored and not imported into the spreadsheet ?
> Just set the "From row" number to 2 instead of 1.
> 
> Note however that if in a sheet the top left cell content is "sep=" or
> "sep=|" (without quotes and any separator) and is the only cell on the first
> row then when writing the sheet back to CSV the then current separator is
> added even if a different one was chosen. That functionality of course is
> lost if the first row is not imported.
> 
> I'll add details to the release notes.

I see, thanks for the clarification!! Will try to write a test for this. Thanks for fixing this issue!!

Verified in

Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: 4f4452f6a74201e862971a79ba5bdcd06f3ba9ce
CPU threads: 4; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 8 Commit Notification 2021-08-31 20:31:53 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/4cfc303886bc9f863f755f8278561f9e57f9a5c5

tdf#117868: sc: Add UItest

It will be available in 7.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2021-12-01 17:44:16 UTC
Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/4973d1a792d20d77e7401c1d2b1fe9821695297d

tdf#117868 Help contents for sep= in CSV files