How to look up a cell value of an Excel file?

Question

Suppose I have an Excel spreadsheet file data.xlsx whose content is

        A             B
1  Third Parameter    7
2  First Parameter    5
3  Second Parameter   3

and I would like to look up the value in column B using the corresponding text in column A.

In other words, I need to have a Latex command like, for instance, \getValue{<macro name to save the value in>}{<Search word>}{<Excel file name>}. So, for example, \getValue{\rdPar}{Third Parameter}{data.xlsx} will store the value of 7 in \rdPar to be used later in my latex document.

P.S. Maybe, this question is helpful but I don't know how to edit the answers for my need.

Answer 1

Assuming you can use .csv instead of .xls(x), there are several tools for manipulating CSV files, for example csvsimple.

Though if you want to reinvent some wheels: you can use \ReadCSV to read in the .csv file to a key-value structure, then \getValue to fetch those values when needed. The syntax of \ReadCSV is:

\ReadCSV [*] {<label>} [<name>,<value>] {<file>}

\ReadCSV will read the CSV <file>, and take columns <name> and <value> (<name> and <value> are the number of the columns; defaults are <name>=1 and <value>=2) and save them under the <label> for later. If the optional * is used, the file is read as a string.

Once the file is read, you can fetch the saved values using \getValue:

\getValue <macro> {<name>} {<label>}

\getValue will fetch the <value> column for the respective <name> in the CSV file read under <label>, and save that in the <macro>.

In your example (removing header row and column), you'd have:

\ReadCSV{mydata}{test.csv}
\getValue\rdPar{Third Parameter}{mydata}
\rdPar % prints 7

Here's the expl3 implementation (it is minimalistic: there's no error checking for wrong input, missing data, etc.):

\begin{filecontents*}{test.csv}
Third Parameter  , 7
First Parameter  , 5
Second Parameter , 3
\end{filecontents*}
\documentclass{article}
\usepackage{xparse}
\ExplSyntaxOn
% Step 1: reading the file
\ior_new:N \l__diaa_csv_ior
\bool_new:N \l__diaa_csv_str_bool
\NewDocumentCommand \ReadCSV { s m >{ \SplitArgument {1} {,} }O{ 1,2 } m }
  {
    \IfBooleanTF {#1}
      { \bool_set_true:N \l__diaa_csv_str_bool }
      { \bool_set_false:N \l__diaa_csv_str_bool }
    \diaa_csv_read:nnnn {#2} #3 {#4}
  }
\cs_new_protected:Npn \diaa_csv_read:nnnn #1 #2 #3 #4
  {
    \prop_new:c { g__diaa_csv_#1_prop }
    \ior_open:NnTF \l__diaa_csv_ior {#4}
      { __diaa_csv_read:cnn { g__diaa_csv_#1_prop } {#2} {#3} }
      { \msg_error:nnn { diaa } { file-not-found } {#4} }
  }
\msg_new:nnn { diaa } { file-not-found }
  { File~`#1'~not~found. }
\cs_new_protected:Npn __diaa_csv_read:Nnn #1 #2 #3
  {
    \bool_if:NTF \l__diaa_csv_str_bool
      { \ior_str_map_inline:Nn }
      { \ior_map_inline:Nn }
        \l__diaa_csv_ior
        {
          \prop_put:Nxx #1
            { \clist_item:nn {##1} {#2} }
            { \clist_item:nn {##1} {#3} }
        }
  }
\cs_generate_variant:Nn __diaa_csv_read:Nnn { c }
%
% Step 2: getting the values
\NewDocumentCommand \getValue { m m m }
  { \tl_set:Nx #1 { \diaa_csv_item:nn {#2} {#3} } }
\NewExpandableDocumentCommand \CSVItem { m m }
  { \diaa_csv_item:nn {#1} {#2} }
\cs_new:Npn \diaa_csv_item:nn #1 #2
  { \prop_item:cn { g__diaa_csv_#2_prop } {#1} }
\ExplSyntaxOff
\begin{document}
\ReadCSV{mydata}{test.csv}
\getValue\rdPar{Third Parameter}{mydata}
\rdPar
\edef\rdPar{\CSVItem{First Parameter}{mydata}}%
\rdPar
\end{document}