[Solved] Open CSV and save to XLSX in OO 3.4

[rhersom]

I'm not able to save a 106,000 row spreadsheet in XSLX format using )) 3.4. I open a CSV successfully but the "Calc MS Excel 2007 XML" option is not available and I get a Write Error trying to save to "MS Excel 2003 XML". The end goal is saving to the XSLX under program control, but I don't expect to get there if I can't do it through the menu. Is there an installable add-on that adds the "Calc MS Excel 2007 XML" opion to OO 3.4?

[Villeroy]

Apache OpenOffice does not save xlsx and hopefully it never will. If you want to work with these file formats you should use MS products. There should be no technical reason to store raw data (csv) in such a complex and proprietary file format.

[rhersom]

The technical reason is obvious - to make a file with 106,000 rows that is readable in the most popular spreadsheet. CSV does achieve 80% of what we need, but we lose some formatting and use of color that we'd like to keep. If a vertical application shows you an attractive spreadsheet that you can email to a customer when Excel is loaded and a drab spreadsheet that you can email to a customer when Open Office is loaded, then that does not reflect well on OpenOffice.
I am puzzled why I found google hits that told me the exact FilterName to use. Is there some other flavor of OpenOffice that does support XSLX?

[rhersom]

A second look at
http://wiki.openoffice.org/wiki/Framewo ... st_OOo_3_0
shows that "Calc MS Excel 2007 XML" is indicated as import only. I have to credit your solve as technically correct while disagreeing that that is how it should be.

[Villeroy]

Save as dBase. Excel can read dBase. And it should be able to read csv as well or as badly as this software (there are always issues when misusing spreadsheets as databases).
Recent versions of Excel can also read ODF files (unformatted raw data at least).
But Calc must not write any file format that is more complex than its own. And in my personal opinion it should not gerenate any file format that has been bribed through the international standardization boards for the only purpose to fight ODF.

[cjbush]

Villeroy, maybe you should try being helpful instead of one of the pretentious people that give open source a bad name. Asking "Why would anybody want to save a file in XLSX?" is like asking "Why would anybody want to use PDF?" It's the standard for most business applications, and it's what most people have to deal with. Dismissing it doesn't help anyone.



That being said, I'm pretty sure you can save XLSX with LibreOffice, but not OpenOffice. I'm not sure the conversion process is pleasant, however.

[Villeroy]

Please do yourself a favour and get some copy of MS Office. Since you are a Windows user and they removed their "Genuine Advantage" program from their office suite, it should be fairly easy to steal a free copy of it. Borrow a disk from someone and google what to do with it.

MSO is the one and only software designed to fully support OOXML. As a matter of fact OOXML is designed to be as incompatible as possible with OpenOffice despite the "Open"and "XML" in its name.

There is not a single reason why the original poster should store the most simple raw data (text, dates and numbers) in the most complicated file format availlable. There is no reason whatsoever. Point!

LibreOffice tries to write OOXML, but apart from MS Office there is not a single procuct on the whole software market which is able to support each and every possible feature that can be stored in that file format. This is the reason why LibreOffice writes poor OOXML where it should simply refuse to write any OOXML.

This dilemma is fully intentional from the Microsoft point of view. The main reason why they threw OOXML into the pit is fighting the free and fairly simple ODF standard instead supporting it.

[geotrouvetout]

It is a very valid request to be able to save XLSX in OpenOffice. XLSX is now widely used as the default Excel format. If a client send you an XLSX sheet he wants you to work on and you are using OpenOffice, you can work on the file but not keep the original formatting and more people are taking advantage of XLSX advanced formatting not available in XLS format. If you encounter that problem often enough it defeats the purpose of OpenOffice. That was probably MS intention when they came up with this crap but if OO continues not to be able to save in XLSX format, it won't play in its favor.

[Villeroy]

Did you understand anything of what I tried to explain?

-- Nothing can be done about it without investing thousands of development hours. Unless you contribute some of your time and money instead of posting pointless complaints, you have no legitimate point.
-- LibreOffice spoils complex OOXML documents because it tries to do something which no software product other than MS Office can do yet. You are free by any means to try this experimental office suite.
-- There are very good reasons why AOO does not support OOXML yet. You are free by any means to not install AOO.
-- For the OP of this topic there is not a single technical reason to convert plain text data (csv) into the most complex file format availlable. Since the topic is marked as [Solved], I assume that the OP accepted this matter of fact. There is no reason at all.
-- Microsoft's default file format is not relevant as long as MS fully supports the old format which is much better supported by a wide variety of non-MS software.
-- It may take another decade until non-MS software will be able to support OOXML decently. From the MS point of view this will be a problem. When this problem arises, MS will introduce the next even more complicated rubbish, declare it at standard, extend this standard at will, impose patent threats and all the Microsoft morons will eat that shit gratefully.
-- You are free by any means to use Microsoft office if you are convinced so strongly that their hyped up file formats are the way how you want your precious data to be stored.

[Hagar Delest]

It is a very valid request to be able to save XLSX in OpenOffice. XLSX is now widely used as the default Excel format. If a client send you an XLSX sheet he wants you to work on and you are using OpenOffice, you can work on the file but not keep the original formatting and more people are taking advantage of XLSX advanced formatting not available in XLS format. If you encounter that problem often enough it defeats the purpose of OpenOffice. That was probably MS intention when they came up with this crap but if OO continues not to be able to save in XLSX format, it won't play in its favor.

AOO will never be able to cope with OOXML. It can't be a clone of MS Office.
As long as the users don't understand that their own data are at stake, we will have such discussions.
If your client is trapped in that vendor lock-in policy, then you are too. But you can't ask other applications to comply with that. If you need to work with OOXML, then use MS Office.

[georgetroy]

As a financial contributor to Open Office I am very disappointed to read the response from Villeroy.

I have been committed to Open Office for years now. I've promoted Open Office any opportunity I can. I think It's a great product and (up till now) it's been close to 100% compatible with the parts of the Microsoft Office Suite that I use. I started to use Open Office because it was compatible with Microsoft products. If it wasn't, I would not have tried to use it. I live in a world where almost all the people I exchange documents with use Microsoft Products. It is impractical for me to change the format of every document I receive.

Yes I could get hold of a copy of Microsoft Office and write xlsx files that way, but I'm only going to use one "Office" product. I would prefer it to be Open Office, but I'll be forced back to Microsoft products if compatibility is not maintained.

As a software developer I can appreciate that a lot of effort may be required to write xlsx files. But if Open Office can't adhere to industry standard it's user base will fade drastically. If I read a response that said "it's a difficult job but we'll get to it" then I'd be prepared to stick with Open Office. But Villeroy's response that the problem is "solved" is pretty disheartening to any Open Office users trying to work in a Microsoft dominated environment.

[Hagar Delest]

OOXML is not an industry standard, far from it. It's a false open standard produced to have a MS alternative to ODF. The best proof is that MS use a customized version of OOXML, not the version that was ISO certified (with irregularities in the ISO ballot but that's another story, there is a discussion about that here).

That's vendor lock-in policy, period. If you need to work with MS Office file formats, then just stick to MS Office. The risks are too high when using external import/export filters.

Note that the developers may have in mind a better support of MS Office formats, you should ask them directly (see the AOO web page for the mailing list).
OOXML export filter for example is available in the code but was not activated by choice (whereas LibreOffice use it). This policy may change now that the Apache Foundation handles AOO.

[Villeroy]

So far nobody explained why OpenOffice should write OOXML.
"Because I want it to" is not a reason.

As a financial contributor to Open Office I am very disappointed to read the response from Villeroy.

Software can be as complex as financial products.

[RoryOF]

As a financial contributor to Open Office I am very disappointed to read the response from Villeroy.

"Ah, what a dusty answer gets the soul when hot for certainties in this our life" [George Meredith (1828-1909), 'Modern Love' ]

[komiiro]

Here's a valid reason:

I work in an environment where I have to supply my own laptop. I had to also upgrade my laptop to a newer laptop as my old XP wasn't cutting it anymore. What does this have to do with anything?

I was attracted to OO because it was an affordable and open source solution to keep up with my work colleagues and at school. Before the big upgrades to MS2007 my version of OO worked just fine and no one bugged me to pay for an upgrade I can't afford and LibreOffice sucks and just doesn't do the job it says it does as cleanly as OO does.

The whole point is those of us who would love to be compatible to MS is not because "we just want to" it is because in some of our cases it is because "we NEED to" to simply be able to afford to keep up with what everyone is touting as the new industry "standard". And what alot of people don't know (my own boss did not until it was far too late) is that their older version of MS Office isn't going to run on anything newer than Win7 and not only are they stuck with the oft expensive bill of the upgrade to the newer Windows computer but now also sunk on buying the newer version of MS Office which does not come included. On a student's budget that is all too often too expensive and no, asking my parents is not an option as they're not much better off than me. I'm an adult/mature student and one parent is dead from age and the other is in a home - I have enough expenses on my plate, thanks.

So why should OO keep up? I was under the impression that it was supposed to be the alternative to MS Office. If it doesn't keep up then it isn't the big alternative and I may as well stop using it and break my budget and go the rest of the MS Office lemmings - much as I'd hate to. Oh wait, I can't. So in my case I don't want OO to be xlsx compliant I need it to be xlsx compliant.

[Villeroy]

All this has been discussed more than a hundred times. It is MICROSOFT who does not want you to be compatible unless you buy their latest shit. No 3rd party is able to follow their terms of "compatibility". And surely they have absolutely no interest in being compatible with anything they do not control like they do not want any competitor to be compatible with their file formats. Fighting our free ODF standard was the one and only reason why they came up with their new file format.
If you really need full compatibility with their file formats then you've got to buy their software. I can assure you that this is the one and only solution and they will throw something new into the pit as soon as alternatives come close enough.

And the topic was: How do I save the most simple tabular file format (csv) in the most complicated tabular file format (xlsx) using this office suite. The technically correct answer was: It makes no sense to do so.

[tech1x10]

I can understand the sentiment here but I can also understand users needs to be compatible with their clients.

So I have therefore discovered a workaround. It's not pretty but it does work:-

1. Receive XLSX file from Client
2. Convert it to ODS and work on the file as normal.
3. Save the file keeping the ODS format.
4. Get a google email account and email the file to yourself.
5. Open Gmail and open your message attachment as a "Google Spreadsheet"
6. Save the file to your desktop using "Download file as... (choose XLSX from the drop down)

Job done, one XLSX file to send to your client.

As I said, it's a bit convoluted and certainly it's not pretty but it does work. I'm an avid supporter of open source, and I'm certainly no fan of Microsoft but given just how many people are using this format now, it can't be ignored. Reluctantly I was drawn to MS for an Excel licence earlier today, until I discovered this work around.

Best wishes.

[Villeroy]

So I have therefore discovered a workaround. It's not pretty but it does work:-

It happened to work in your particular case. For online conversion we use to recommend http://zamzar.com.
Again, this has nothing to do with the original topic.

[tech1x10]

Ok thanks will take a look and yes I agree with your point, it's just that I couldn't find a topic to do with general XLSX conversion, if there is one would love to be pointed in the right direction

[Villeroy]

It does not matter what OpenOffice users say. A former Microsoft coder http://www.joelonsoftware.com/items/2008/02/19.html tells everything that needs to be told.
This paragraph describes the essense of the problem:

They have to reflect all the complexity of the applications. Every checkbox, every formatting option, and every feature in Microsoft Office has to be represented in file formats somewhere. That checkbox in Word’s paragraph menu called “Keep With Next” that causes a paragraph to be moved to the next page if necessary so that it’s on the same page as the paragraph after it? That has to be in the file format. And that means if you want to implement a perfect Word clone than can correctly read Word documents, you have to implement that feature. If you’re creating a competitive word processor that has to load Word documents, it may only take you a minute to write the code to load that bit from the file format, but it might take you weeks to change your page layout algorithm to accommodate it. If you don’t, customers will open their Word files in your clone and all the pages will be messed up.

A fully Excel compatible software needs to be a complete rewrite of MS Excel. It needs to interprete every single attribute of every possible XML node that may occur in some xlsx file in the same way as MS Excel does.

[temmons]

All I can do is say WoW. After reading comments like Vileroy and others the reasoning for OO not to save MS XLSX surprises me. I started using OO to support open format but I don't see OO doing that but rather doing the same thing MS has done. Make proprietary formats. I really thought they would be better then MS but I guess I was wrong. I will take Vileroy's advice and go back and support MS and my recommendations of MS support.

[Hagar Delest]

You don't seem to have understood: ODF is an open standard (ISO certified well before OOXML), supported by other productivity suites, and even by MS Office in a limited way (not enough to ruin their business of course).
OOXML (.xlsx, .docx, ...) is a proprietary format that has the look of an open standard. So if you need to work with the proprietary format from MS Office, then of course you need MS Office and that's all.

[Villeroy]

The first posting of this topic describes the stubbornness and incompetence. Conversion of plain text data to OOXML for the sake of OOXMLness.

[ananda.pryana]

Just out of curiosity, do you know broadly what's so different about reading and writing the xlsx format? I can understand that microsoft probably designed it that way (easy to read but hard/impossible to write), but based on the examples and arguments above I can't see how that is possible technically. If we can interpret the format enough to be able to import and display it properly, shouldn't we be able to write to it as well?

[Villeroy]

Just out of curiosity, do you know broadly what's so different about reading and writing the xlsx format?

Reading a document, you interprete incoming data and fit them into the document model of your own application. You can simply ignore things you do not support or handle them otherwise.
Writing a document you need to generate data to be interpreted by a completely different application where the application's source code is a best kept secret.
In case of OOXML ("Office Open" XML, haha), the document format has been bribed through the standardization boards for the only purpose to fight our own open document format with something that is solely owned by Microsoft and not really open. Each newly written copy of an OOXML document helps to spread that format.

[kjvincent1979]

I've just installed OpenOffice as I didn't want to be required to pay for MS Office as it is just too expensive for me.

When I found I could not save to XLSX I looked for solutions, such as add in filters to allow it to save.

An alternative is Libra Office, which does the job. It does force me to "save as" currently as it has literally just been installed (may be something I can change).

I've gone to OpenOffice for years as a really good alternative to MS Office if you can't afford Microsoft Office.

I understand some of the posts about the development hours to add saving to XLSX to the software. I develop myself. I'm actually having to reverse engineer my own code since having it lost due to hard drive failure so I've hours upon hours to write. I also realise some elements of Microsoft software are closely guarded so unless they release the file format specifications of these files it is very difficult to successfully save back to a format that MS Office will read back. Whether they are open on those I don't know.

I don't get, nor do I support, the whining attitude of people when someone asks a legitimate question about supporting a widely used file format with comments concerning the hardships and hours of coding. A simple reply saying that the complexity of the programming means that it isn't planned at this time would be more polite.

I often have to search forums to assist my work in IT Support. I don't know, or ever will know, everything about all systems I have to work with. The times I come across reposts of the same comment 4 or 5 posts ago that make no contribution to the forum is beyond me, unhelpful comments such as "ditch the pc, use a Mac" or the reverse, "why do you want to do that" type comments, etc.

Forums are here to help people. If I responded to a client's request in the manner people have on this single thread I'd be out of the door and find it very hard to get a reference.

Be polite and helpful please. If not, don't reply.

[Villeroy]

I've just installed OpenOffice as I didn't want to be required to pay for MS Office as it is just too expensive for me.

This is exactly the user profile which complains that a free 300 MB software does not do the exact same things as the commercial product with a 1.5 GB footprint. This is a completely inappropriate and unfair expectation. Stay here for a while an try to contribute by answering compatibility questions. After spending some hours comparing advanced features you did not even know they exist, you will draw some conclusions. There is another commercial office suite called SoftMaker Office. It is completely built around the MS Office file formats and supports these file formats almost as well as MS does. But even this product has not each and every feature implemented.
There are a dozend of very good reasons NOT to work with foreign file formats. Same with MS Office, picture editors, movie editors, desktop publishing software etc. They all do not work as designed with foreign file formats. All you can expect is a fairly well done import filter, so you can continue working with the native file format of your application.

Once again and ON TOPIC "Open CSV and save to XLSX in OO 3.4". There is not a single reason why anybody on earth should store plain text data (csv) in the most convoluted file format on the planet. And every single xlsx/docx/pptx document undermines the ODF standard a little bit more.

If you only want to save money while giving a shit on open file formats, then I urge you to remove Open/LibreOffice from your computer and install http://www.softmaker.de/
This is all about ODF and nothing else.

[kjvincent1979]

Grow up.

I am very grateful to the open source community for spending their own time in developing really good programs. It allows people who can't afford the high prices of MS and other suppliers to use such programs.

I don't expect it to do everything that the big software companies put into their programs. I agree that Microsoft don't pay a damned bit of attention to any kind of standards. Their implementation of HTML for example does not meet the global standards set down by the industry. Others end up confirming to their version to make things work, which is wrong.

On topic is the wish for this function. If it isn't going to be there then sure, I can and will find other solutions.

What is off topic is your rude, obnoxious and disgusting treatment of others on the forum who dare to ask for a feature. I've asked similar questions to Microsoft and they advise it gets added to their feature list. Whether that is true, whether there is a hope in hell in it getting into their code I don't know. I seriously doubt it.

Whether they'll totally screw up their software in the next release as they did with 2013, most likely.

The point is, be flipping polite. Open source is about allow people to be able to develop features the big companies can't be arsed adding. It is about collaboration not dicks like you.

[Villeroy]

I am very grateful to the open source community for spending their own time in developing really good programs.

No, you are not grateful at all. You are ignorant.

You can stomp your feet.
You can scream.
You can blame the volunteers on this forum.
You can sue the Apache foundation for a refund.

Like the other fools you do not answer any questions nor do you respond to anything that has been written in this topic and hundreds of similar topics.
All you say is "I want, I want, I want".

Grow up.

[kjvincent1979]

Sorry, where is the blaming? I hope the software will have the ability to save to XLSX in the future. If not, well there are others to use.

I've only just joined this forum as I was looking for information about XLSX. Your pathetic responses can be left off forums.

I don't blame anyone - what is there to blame and to whom? If the software doesn't do something then it is requested. If it isn't put in, well I either help write it myself or use something else. Exactly how could I ask Apache to refund something where there is no cost? Have I demanded anything? Well, aside from you to be polite and not just an insulting, pathetic prat.

You are not answering any questions. You are just being an arsehole because someone asked a question. No idea why given your previous posts seem to be largely helpful.

Plus, you were never named, well aside from your posts.

I hope you can help people with their queries, as I will if I can. I hope someone can help you with your spite.