Show HN: sheet2dict – simple Python XLSX/CSV reader/to dictionary converter | Hacker News Reader

to dictionary converter

github.com

59 pointspytlicek26 comments

I created this tool for myself because I often work with xlsx and csv files. This is usually done through Python Pandas. But if you just need to read these files and work with the values in the lines, you don't need to import the whole Pandas library. It is not even necessary to install Pandas for easier deployment. This can save you up to 2GB space if you do docker images with Pandas.

One of the things I still don't understand are services like snyk.io, which are supposed to do security analysis. But they penalize a tool like this for not having CoC, Contributing in the GitHub repository, and what is most shocking to me is that they measure Popularity. I understand that if more people are involved in the SW, it is probably safer. But penalizing someone for having few stars on GitHub seems weird to me. Especially when the tool is used by several people / companies and it has over 5,000 downloads.

Thanks, I just bookmarked this. I often don’t want all of Pandas, and this looks more convenient than just using the library CSV.

Sure. The same here. One tool for many formats. Like it!

Would you be able to comment on how this compares to the standard CSV module [0] / PEP 305 [1]?

[0] https://docs.python.org/3/library/csv.html

Not OP nor well-versed with any of this, but the standard csv module doesn't parse xlsx files. Since the majority of people who work with spreadsheets use excel [citation needed], this probably greatly increases the application potential of the library.

You won't be able to use this if your file doesn't fit in RAM. This unnecessarily clones the file into a list instead of returning a generator and leaving the list conversion up to the user.

Thanks for pointing this out. I'll definitely look into it.

I maintain a similar project, load any CSV, manipulate and get stats, detect sensitive data, etc

https://github.com/capitalone/DataProfiler

My question, how do you do header detection? That's a _very_ difficult problem.

You could emulate what data.table does and allow a search string variable to identify the header row.

https://github.com/h2oai/datatable/blob/385a9b370db32de90135...

Isn’t this already in the csv module with dictreader?

Xlsx I know nothing about.

yes. if you are only going to be working with CSV files then this is overkill

It is true. I usually use it in tandem with flask / fast-api to parse both: CSV and XLSX. In the near future I will add XLS (yes, someone still uses it) and also the open document format.

openpyxl has good xlsx support and is easy to use. This seems to be an abstraction layer on top of both. Unfortunately this will use a lot of RAM working with large files.

There are several light (compared to pandas) xlsx/xls libraries for Python, but none that I know of have a simple read-to-dict API.

Correct, it's a wrapper around csv.DictReader

https://github.com/Pytlicek/sheet2dict/blob/main/sheet2dict/...

There are many data engineers at companies who have to write custom little scripts to take data from spreadsheets into an analytics DB.

Thanks for removing some boilerplate from that process for people!

> Thanks for removing some boilerplate from that process

Do you mean "boilerplate" or something else? Because a shell script (e.g. bash with calls to sed, awk, grep, and Perl) is not "boilerplate". It's an implementation. And much more efficient than some of the unstable, high-complexity solutions that claim to be "simple".

TBH, the choice of "solution" doesn't matter much for a small dataset. It is of course overkill to run a glorified REPL just to do some math on a small dataset.

As datasets grow, the introduction of unstable complexity can cause problems.

The tooling that you say is being removed may well be the the fastest and most reliable tools, proven over decades of use.

For the opposite direction, I have had good luck with the XlsxWriter library

https://github.com/jmcnamara/XlsxWriter

Speaking of excel files, Does anyone know of a good way to port sheets with equations/functions to python? Sometimes I need a calculations from a sheet and I have to manually copy them over.

I usually import CSVs into a Python Pandas Dataframe and then iterate over the dataframe in a loop or manual line by line interventions and then beam the data out somewhere else...

Is this a better approach?

Here it is the same. But with the difference that you don't have to install a relatively "large" Pandas library. It always depends on what you want to achieve. If you just read the lines and values, you can always use something small to help. If you want to Dockerize similar solution, you have the difference that Pandas needs specific system libraries and it will increase your Docker Image to almost 2GB compared to sheet2dict where it is a couple of KB.

> I usually import CSVs into a Python Pandas Dataframe and then iterate over the dataframe in a loop

Isn’t a big point of dataframes providing tools that are more efficient so you don’t have to use Python loops for operations across a body of data?

Yes. Manually looping over rows in a dataframe is wildly inefficient.

Nice, I did something like this and made it a gist ages ago for XLS, it's good you're putting up something that's more maintained and working with multiple formats.

It’d be better to use namedtuple to avoid repeating same dictionary keys imo

I know what you mean. Now it returns a field with dictionaries. Repeating keys as such is not possible and this is the desired state. But yes, with namedtuple you could manoeuvre more and add line numbers for example. Good idea, I'll think about it in the future.

Reply on news.ycombinator.com