Do you need help?
Simutrans Wiki Manual can help you to play and extend Simutrans. In 9 languages.

[datSheet v1.2.0] Generate dats from spreadsheets (xlsx)

Started by An_dz, May 20, 2018, 03:52:21 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.


From the discussion about helping pak192.comic I recommended the team to use Microsoft Office Excel Online, you can see the reasons why here at the top of my reply.

So I created a command line tool that generates dat files from a sheet file. It's called datSheet and source is on GitHub.

It's compiled C++ code, so it's fast and you don't need to have Excel, anyone can use it.
It generates dat files for an entire pakset, generating them following the pakset hierarchy.

Currently only xlsx files are supported (LibreOffice Calc can load/save these). It works with any parameters because it does not make any checks, the basis is that each row in the sheet is a Simutrans object and each column a parameter. You can leave a value blank and that won't be output.

The first column and the first row are special. If there's any value in the first colunm of a row that row is considered a Simutrans object and the tool will generate a dat for that line, this allows you to add other data in the sheet, like comments and global calculations, without problems, just don't add anything to the first column and that row won't output. And for the first row, where the dat parameters are placed, the same thing happens, any column that doesn't have anything in the first row is ignored, this way you can add comments in each object without those being output in the dat files.

Another point is the sheets and their names, a xlsx file can hold multiple sheets and each sheet is considered an hierarchy in the pakset project, their names is the name of the folder where the dat will be generated into. For example, if a sheet is called factories all dats defined in this sheet will be created in a folder with the same name.

For a more detailed explanation you can download the template.xlsx file in the root of the project. It explains everything you might need to know about how to set it up correctly.

You can also import (from v1.2.0) a pakset directory into a xlsx file. Some warning may be given, the most common will be if the line looks invalid (doesn't have =) or if a parameter is duplicated. Add -i to import and first add the dir and then the xlsx name.

You can download compiled versions for Linux and Windows on GitHub

Call datSheet --help for usage help.


QuoteIt's compiled C++ code, so it's fast
I doubt speed would ever be a problem. To put it in perspective a Java based dat parser can process several thousand records in well under a second, and that includes parsing, export, import and update.

How does one bundle multiple records in a single file? It is common that dependant or related data be distributed in a single dat file so that it can be added/removed in a consistent way. For example in Simutrans Extended Pak128 Britain tenders are often part of the same file as the engines that depend on them if they are only used by 1 engine. Another example is an entire set of coaches might be provided in a single file.

If people need help converting an existing pakset into a spreadsheet I might be able to hack together some Java program to do it.


Quote from: DrSuperGood on May 20, 2018, 09:59:38 AM
I doubt speed would ever be a problem. To put it in perspective a Java based dat parser can process several thousand records in well under a second, and that includes parsing, export, import and update.
I was actually comparing with things like a VBA script.

Quote from: DrSuperGood on May 20, 2018, 09:59:38 AM
How does one bundle multiple records in a single file?
Yes, this feature is still not available as I could not think of a solution yet.


What a wonderful contribution to our community! Thank you, An_dz!
(Signature being tested) If you enjoy playing Simutrans, then you might also enjoy watching Japan Railway Journal
Available in English and simplified Chinese


New version allows to create a single dat for multiple dats, one needs to create a column titled filename and set the same filename for multiple consecutive rows. More info in the template.xlsx file.


New version can now import a pakset directory into a xlsx file. It does not import comments. I tested with pak192.comic source.



Thanks a lot for all this effort. I saw this a bit late sadly. It's exactly what I want, love it.

During the implementation I figured that there seems to be a conflict between having all data in one file and the idea of pakset reposities. Thus I'm to figure a clean solution to either keep the .dat files as main source of information, or to have the .xlsx file in a way multiple people can work with at the same time. In plus having an automated pakset build with excel features would be beneficial.

First thing in mind was to host the excel sheet in google docs. This would require an automated download from the google cloud, wich i can't imagine to be that much of an issue. Downside of this would be us depending on Google.

The other Idea is to keep the .dat files as main source of information. This would result to the new program to copy the .dat files in an ecxel sheet, merging that sheet with different one that contains the formulas. After that is has to copy the formulas to every object of its kind, and generate the new dats. This would mean that the pakset could be build without calling to the cloud, but require some kind of modified program.

Before I start trying to write the program for case 2, I want to ask if you have (had) something specific in mind regarding this "issue"?


I never really thought about that because I never worked on balancing a pakset, so I never thought about what a team of maintainers would expect the tool to do.

Basically I did the hard part of conversion first to help maintainers use the advanced features that sheet software offer, extra stuff would come later after usage.