Bug 98168 - Cannot use XLSX (with validation errors) as address list in Mail Merge Wizard - error in Comment 6 (workaround: add as external data source)
Summary: Cannot use XLSX (with validation errors) as address list in Mail Merge Wizard...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: low normal
Assignee: Miklos Vajna
URL:
Whiteboard: target:6.1.0
Keywords:
Depends on:
Blocks: Mail-Merge
  Show dependency treegraph
 
Reported: 2016-02-24 23:57 UTC by Jon 'Smith' Camilleri
Modified: 2017-12-20 08:05 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
spreadsheet (xlsx) (5.00 KB, application/zip)
2016-02-24 23:57 UTC, Jon 'Smith' Camilleri
Details
screenshot (33.30 KB, image/jpeg)
2016-02-24 23:58 UTC, Jon 'Smith' Camilleri
Details
The example file imported correctly in current master (85.73 KB, image/png)
2017-12-16 07:47 UTC, Gabor Kelemen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jon 'Smith' Camilleri 2016-02-24 23:57:54 UTC
Created attachment 122970 [details]
spreadsheet (xlsx)

I am trying to create a mail merge manually by typing in a few fields, and, some of the fields appear not to be retrievable.

Source of reference
1. MFSA Company Registry - http://mfsa.com.mt/.
Comment 1 Jon 'Smith' Camilleri 2016-02-24 23:58:32 UTC
Created attachment 122971 [details]
screenshot

Fields in database are not read so mail merge cannot yet occur
Comment 2 Buovjaga 2016-03-14 14:21:25 UTC
Which LibreOffice version are you using?

Set to NEEDINFO.
Change back to UNCONFIRMED after you have provided the information.
Comment 3 Alex Thurgood 2016-06-03 10:41:00 UTC
@Jon : 

- the third column in the xlsx file has no header. The import wizard needs a first line entry in order to be able to handle that column

- I can confirm that if I attempt to add the xlsx file as a datasource, the OK button is not activated and thus this choice can not be registered and one can not move onto the next step of the wizard

- If I save the xlsx file as an ODS file, I can add it and move on to the matching of fields section of the mailmerge wizard, but then can go no further once I have made name and address fields correspond.

There are still lots of bugs in the mailmerge tool, and you might be experiencing one or more of them.
Comment 4 Alex Thurgood 2016-06-03 10:42:07 UTC Comment hidden (obsolete)
Comment 5 QA Administrators 2016-12-07 12:57:42 UTC Comment hidden (obsolete)
Comment 6 Gabor Kelemen 2016-12-22 19:29:13 UTC
I tried to set the attached xlsx document up as data source for mail merge in the wizard with a current master build.

I can add it to the Select Address List window, but no table is listed and on the console I see this message:

warn:legacy.osl:19537:1:sw/source/ui/dbui/addresslistdialog.cxx:621: exception caught in SwAddressListDialog::DetectTablesAndQueries

I'd say the problem is still present.

I tried with my own xlsx documents made with Excel 2010, 2013 and even 2016 but all give the same error above.
Comment 7 Buovjaga 2016-12-27 22:38:47 UTC
Thanks, Gábor. Let's set to NEW, then.
Comment 8 Timur 2017-07-21 14:19:38 UTC
Attachment 122970 [details] shows a number of validation errors when checked with OpenXML Productivity Tool. On the other hand, LO opens it and there's a way to use it. 

Conclusion from here and from what I tested with master 6.0+: we cannot use this XLSX as address list in Mail Merge Wizard. Connecting to data source fails. Looks inherited from OO.
If we try to use that unconnected address list then as Data Source, we get: 
    "SQL Status: HY000
    The driver class '' could not be loaded."
There's workaround: add it as external data source.

Title "Cannot update all fields in mail merge" is not clear so I change to "Cannot use XLSX (with validation errors) as address list in Mail Merge Wizard (workaround:  add as external data source)".
Comment 9 Gabor Kelemen 2017-12-16 07:47:11 UTC
Created attachment 138471 [details]
The example file imported correctly in current master

I just rechecked this on a current master build and it seems to be able to import xlsx files as data source, even the sheet selection works when there are multiple sheets.

Could someone also check it? Then I guess we could set it to WFM.
Comment 10 John Smith 2017-12-16 08:14:12 UTC
Is it possible to test other formats import/export and their user friendliness?

https://blog.filestack.com/thoughts-and-knowledge/document-file-extensions-list/
Comment 11 Buovjaga 2017-12-16 12:00:18 UTC
(In reply to John Smith from comment #10)
> Is it possible to test other formats import/export and their user
> friendliness?
> 
> https://blog.filestack.com/thoughts-and-knowledge/document-file-extensions-
> list/

Can you verify Gábor's result? https://dev-builds.libreoffice.org/daily/master/?C=M&O=D
Comment 12 Gabor Kelemen 2017-12-17 15:58:52 UTC
So I bibisected it back to commit[1].

Looks like only accidentally adding the format to the filters list was needed. 
However, before we can close this it would be nice to have some unit test, as it is rather important to keep this feature in a working condition.

CC vmiklos as he did the deed :).

[1] https://cgit.freedesktop.org/libreoffice/core/commit/?id=83b43ef2223b66484e0e90e7b614886e06f955b5

$ git bisect log 
# bad: [aee1ff762e080f444514ce89aab3e442c278a445] source sha:54e1441949b83a76eecd02f2839693697773f711
# good: [b9d78ce81dc3481fccb0cb75d76fcb6ac939cbd5] source sha:9feb7f7039a3b59974cbf266922177e961a52dd1
git bisect start 'origin/master' 'oldest'
# bad: [2dc325e2914c0f19c12642479a823a747dd6424f] source sha:2b78b9feab9521e614b9edae17709cb6e2001292
git bisect bad 2dc325e2914c0f19c12642479a823a747dd6424f
# good: [ac95c990285f21355016a6a07903f7b0b193aa31] source sha:592ab6435b22f7d1a0d9d940ccc32059fed05b87
git bisect good ac95c990285f21355016a6a07903f7b0b193aa31
# bad: [e481e94e818e8cbfaff95d105634b116aaa6b034] source sha:123ea883b0643894c928bd8edd4203bdc60cc054
git bisect bad e481e94e818e8cbfaff95d105634b116aaa6b034
# good: [e5205a8af67a55dc554f7be39e2ed325dc0f2337] source sha:7892b4191b525162dd2cad63524922856b70fff5
git bisect good e5205a8af67a55dc554f7be39e2ed325dc0f2337
# bad: [7b51331a2581834e511a2c887493b6883d58d776] source sha:1802cfca17e7f12a0666dd884108e5b5a666b36f
git bisect bad 7b51331a2581834e511a2c887493b6883d58d776
# good: [7570f65619a123f4f9ff5873e17665942ea92999] source sha:d4bda10ceef4502ce4717368c6a39ecd709cff7f
git bisect good 7570f65619a123f4f9ff5873e17665942ea92999
# good: [92e0752931c9fab99a91b4613e6d70c7445b8ceb] source sha:2ce827f21cd11832cec5395c6c1c5fb2eef4b2d0
git bisect good 92e0752931c9fab99a91b4613e6d70c7445b8ceb
# bad: [f89af92fff1a9c1879a5ab5fae0066214971d7b4] source sha:5734c6fee6a959b86b5e7f9c391ae3bee6009a08
git bisect bad f89af92fff1a9c1879a5ab5fae0066214971d7b4
# bad: [79a54ddc1d3648b27e03a7478bf60e9afa60605d] source sha:e57aad8e43bc2617e88caac515b3fe5a09349813
git bisect bad 79a54ddc1d3648b27e03a7478bf60e9afa60605d
# bad: [936b6d0472c6affb34c2a3dd30e814c25879d6ba] source sha:83b43ef2223b66484e0e90e7b614886e06f955b5
git bisect bad 936b6d0472c6affb34c2a3dd30e814c25879d6ba
# good: [df79da4f42f7795e98f5d4f4d0de74c430c9a0eb] source sha:71c3113e9dbf61693fd947262370cc61cdf31ac7
git bisect good df79da4f42f7795e98f5d4f4d0de74c430c9a0eb
# good: [b56fa05d5bbd65716f28f62a64de2fdddd50b5d0] source sha:c49ed68c7e30f6963ecf56e542459f4fbd67a470
git bisect good b56fa05d5bbd65716f28f62a64de2fdddd50b5d0
# good: [51d671b1aaff7e2cc40e874da7aa27cc8af5dcfa] source sha:36f2df84fcfc21038b4d20df38a9e5addfcbde3f
git bisect good 51d671b1aaff7e2cc40e874da7aa27cc8af5dcfa
# first bad commit: [936b6d0472c6affb34c2a3dd30e814c25879d6ba] source sha:83b43ef2223b66484e0e90e7b614886e06f955b5
Comment 13 Commit Notification 2017-12-20 08:05:26 UTC
Miklos Vajna committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=28ddaf520da56701508acc258cf1d90f26134d28

tdf#98168 sw: add mail merge from xlsx testcase

It will be available in 6.1.0.

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

Affected users are encouraged to test the fix and report feedback.