Extract data from excel file with lualatex

Question

I have an Excel file that I use as a database. Of course, there are a lot of entries (more than 4000) and I don't want to use all the data to create my PDF.

So I was thinking of using LuaLaTeX to create a script to use only the data I need.

But I don't know how to access to my Excel file with my script. Is there a proper way or not?

Answer 1

When you follow the advice of Joseph Wright and Andrew you could also use the pgfplotstable package to print your CSV file. Because you didn't provide an example, I just copied the code from this answer. To see the result, see there.

\documentclass[margin=5mm,preview]{standalone}
    \usepackage{siunitx} % Formats the units and values
        \sisetup{                           % setup siunitx ...
            round-mode        = places,     % rounds numbers
            round-precision   = 2,          % to 3 places
            per-mode          = symbol,     % kg/dm^3 instead kgm^{-3}
            group-four-digits = true,       % for 1 234,567
        }
    \usepackage{booktabs}           % for table rules

    \usepackage{pgfplotstable}      % Generates table from .csv
    \usepackage{filecontents}       % <--- important: enable table
                                    % refreshing at each compilation
    \usepackage[hang,bf,small]{caption}
%---------------------------------------------------------------%
\begin{filecontents*}{mytable.csv}
Chem.; Avg. Conc.; Avg. Conc. Norm.; Conc. unit; Mass sum; Mass unit
Ammonium ; 159083.33; 114450.21; \micro\gram\per\liter; 2839.463; \kilo\gram
Ammonium* ; 1234.123; 4567.890;  \micro\gram\per\liter; 2839.46; \kilo\gram
\end{filecontents*}
%---------------------------------------------------------------%

\begin{document}
Test of use \verb+siunitx+ units syntax in text \si{\micro\gram\per\liter} and
\si{\kilo\gram},

\captionof{table}{Some caption text}
    \pgfplotstabletypeset[
        multicolumn names,
        col sep=semicolon,  % the separator in our .csv file
        string type,        % added in hopes of enabling alphabetic input.
        header=has colnames,
        every head row/.style={before row=\toprule,after row=\midrule},
        every last row/.style={after row=\bottomrule},
        display columns/0/.style={string type},
        display columns/1/.style={column type={S[table-format=7.3]}},% use
        display columns/2/.style={column type={S[table-format=7.3]}},% siunitx
        display columns/3/.style={column type={s}}, % for units
        display columns/4/.style={column type={S[table-format=5.3]}},% for formatting
        display columns/5/.style={column type={s}}, % for units
            ]{mytable.csv}
\end{document}

---

Regarding the filtering of the Excel table: Why not just filter the Excel table directly, for example using the AutoFilter feature, copy the result to a new table and just save that as CSV file?

To copy just the "visible cells" have a look at this resource.

Answer 2

According to the comment from Joseph Wright and Andrew I'll explain my solution:

From Excel or LibreOffice: file > Save as > CSV

Using CVSsimple package on the file file.cvs with the column name and firstName:

\csvreader
{file.csv}{name=\name,firstName=\firstName}
{
\begin{tabular}
name & first name \\
\name & \firstName
\end{tabular}
}

You'll have as result a table for each entry.