Ask Your Question

How can I combine the data from ~2000 calc files into a single csv file? [closed]

asked 2014-12-12 17:49:15 +0200

henryh13 gravatar image

updated 2016-03-07 01:38:57 +0200

Alex Kemp gravatar image

I have around 2000 calc files that contain customer information; name, phone numbers, address, etc. The documents are all identical except for the unique customer information, ie. they were all produced from the same template. I need to import all of this data into a new database and the only supported format is csv. Is there any easy way to extract the data from my files en masse into a single csv file, or into a calc file that can then be exported to csv? I'm running Windows 8.1 and LibreOffice 4.3, and I do have some experience with the command line and simple scripting, though not specifically with LibreOffice. Thanks!

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-07 01:39:03.572640

1 Answer

Sort by » oldest newest most voted

answered 2014-12-12 20:14:32 +0200

Lupp gravatar image

updated 2020-01-15 22:01:18 +0200

You can use references into your 2000 ods files created in a single collecting ods file if the files you have to get the data from have reasonably chosen names and if the files are saved in one folder or may be coppied into a single folder.

Create a new ods and therein an empty new sheet. Open one of the source files. In the new sheet enter a formula "=Reference" creating the reference by clicking in a cell of the source sheet. Parse the new formula by other formulae for the common path string ... Concatenate your source file names from the known part. ,.. Finally concatenate the full access addresses for the data wanted. ... Use INDIRECT().

If your files were located and named in a clever (or not too dull) way, this all is easier in practice than it may seem from my explanation. Best study the attached example (I had to add a fake extension to the file because the forum does not accept zip. Download the file, replace the .ods by .zip, extract the archive into an arbitrary empty folder - now 4 ods files not faked - and open the file with the long name.)

edit flag offensive delete link more


Wow Lupp, that's basically perfect. All of my files are named <lastnamefirstname_date>.ods but I can take a copy of the set and bulk rename them to match the scheme you started; the info in the filename is in the file itself so I won't lose anything doing that. Thanks a million, you just saved me countless hours of mind-numbing data entry.

henryh13 gravatar imagehenryh13 ( 2014-12-12 20:38:56 +0200 )edit

Brilliant solution. It seems to work also in LibreOffice under Linux Neon (KDE).

alanw gravatar imagealanw ( 2020-01-15 16:18:29 +0200 )edit

Question Tools



Asked: 2014-12-12 17:49:15 +0200

Seen: 1,048 times

Last updated: Jan 15