# Using dashes with siunitx with pgfplotstable

I have a table setup that is mostly numerical columns. However, there are occasionally some cells that have a blank entry, currently the in the excel csv file output as "---". Everything worked fine, until I wanted the cells aligned on the decimal place. I had found good results with the S column of the siunitx package, which is in use elsewhere in my document. So I decided to use those. However, now the "blank" cells with the "---" are causing trouble and conflict. I have tried to use some of the methods in the thread here to resolve the issue:

Siunitx: how to deal with invalid numerical input '-'?

Without any success (using \textemdash, or protecting the cell with braces {---}). I also tried adding string type to the column style as I read in the pgfplotstable manual that this causes pgfplotstable to simply read the cell without further processing.

Depending on the method I use, I either get Missing \endcsname inserted when I try the textemdash method, or Could not parse input '---' as a floating point number I'm also not at all married to one method or another. I do want to use pgfplotstable, as this is only one of about 45 or so external tables of this format I will need to include in my document, and I would prefer not to typeset them all into latex code manually.

Here is a "mwe", since it doesn't actually work :(

\documentclass{memoir}
\usepackage{pgfplotstable}
\usepackage{siunitx}
\usepackage{longtable}

\pgfplotstableset{
begin table=\begin{longtable},
end table=\end{longtable},
}

\begin{document}
\pgfplotstabletypeset[%
col sep=comma,%
columns/atomnum/.style={int detect,column type=c},%
columns/atomtype/.style={string type,column type=c},%
columns/x/.style={string type,column type=S},%
columns/y/.style={string type,column type=S},%
columns/z/.style={string type,column type=S},%
columns/nmrshieldtens/.style={column type=S},%
columns/nmrscaledshifts/.style={column type=S},%
every last row/.style={after row=\bottomrule}%
]{nankakurineSI.csv}%
\end{document}

I use an external file, and I'm not sure how provide that here, so I've included the file contents below. You can see in lines 43 and 48, the trouble spots.

atomnum,atomtype,x,y,z,nmrshieldtens,nmrscaledshifts
1, C ,2.645924,-0.284964,1.121739,145.6803,38.1985
2, C ,3.061887,-0.314747,-1.738533,138.1991,45.6797
3, C ,4.192868,-0.744373,-0.81303,152.3163,31.5625
4, C ,3.609622,-1.293585,0.487818,158.2814,25.5974
5, H ,2.179934,-0.733573,2.004648,29.7536,2.1014
6, H ,3.222428,0.584598,1.466674,30.9379,0.9171
7, H ,2.482083,-1.204262,-2.037409,28.7647,3.0903
8, H ,3.460033,0.135058,-2.653243,29.1953,2.6597
9, H ,4.8172,-1.495056,-1.309066,30.3061,1.5489
10, H ,4.826006,0.126259,-0.596497,30.5419,1.3131
11, H ,4.407002,-1.530092,1.200573,30.3571,1.4979
12, H ,3.088045,-2.236207,0.278539,30.219,1.636
13, C ,-0.54058,-1.145592,0.929915,143.9137,39.9651
14, C ,0.528585,-0.902319,-0.152946,143.2553,40.6235
15, C ,1.550392,0.221805,0.156845,124.581,59.2978
16, C ,0.851532,1.450518,0.80068,133.676,50.2028
17, C ,0.027293,1.042491,2.023216,147.7008,36.178
18, C ,-1.109706,0.152585,1.53285,140.8497,43.0291
19, H ,-0.057217,-1.683382,1.759072,30.1175,1.7375
20, H ,0.034879,-0.650236,-1.095222,30.0622,1.7928
21, H ,1.056471,-1.849267,-0.318437,29.5661,2.2889
22, H ,1.648183,2.146325,1.095154,30.7693,1.0857
23, H ,0.631687,0.517402,2.770335,29.9253,1.9297
24, H ,-0.375923,1.941621,2.506378,30.4385,1.4165
25, H ,-1.749305,-0.12778,2.381335,30.3971,1.4579
26, C ,-0.086446,2.231626,-0.127235,121.2219,62.6569
27, C ,-2.015491,0.916314,0.549213,113.5813,70.2975
28, H ,-0.424133,3.132366,0.42693,29.8436,2.0114
29, H ,0.446378,2.597638,-1.011354,28.8531,3.0019
30, C ,-1.676245,-2.03098,0.39515,138.255,45.6238
31, H ,-2.274539,-2.393781,1.244793,30.6875,1.1675
32, H ,-1.25699,-2.917557,-0.100028,30.3609,1.4941
33, C ,-2.623346,-1.297773,-0.5652,156.7968,27.082
34, H ,-2.066223,-1.014811,-1.468623,29.9263,1.9287
35, C ,-3.14384,-0.01614,0.093255,140.5753,43.3035
36, H ,-2.46664,1.767252,1.104586,29.9266,1.9284
37, H ,-3.843967,0.497571,-0.574314,29.7727,2.0823
38, H ,-3.719835,-0.292135,0.988647,31.0288,0.8262
39, C ,-3.780339,-2.203913,-0.982867,159.2459,24.6329
40, H ,-4.373272,-2.494855,-0.106965,31.3512,0.5038
41, H ,-4.447095,-1.695357,-1.687373,30.882,0.973
42, H ,-3.413599,-3.118855,-1.46021,30.8992,0.9558
43, N ,-1.242229,1.456093,-0.582487,{---},{---}
44, C ,-2.064595,2.286645,-1.451361,138.6569,45.2219
45, H ,-1.433234,2.735618,-2.223064,29.7894,2.0656
46, H ,-2.833329,1.693382,-1.950341,29.0105,2.8445
47, H ,-2.559918,3.103363,-0.892348,30.514,1.341
48, N ,2.23005,0.694822,-1.070475,{---},{---}
49, H ,1.515066,0.996756,-1.729488,30.5467,1.3083
-

You're almost there: To use the S column type, you'll also have to use string type, and if you do that, replacing the --- with \textemdash works. You can let PGFPlotstable take care of the replacement by setting string replace={---}{\textemdash}.

Note that when you use the S column type, you should also specify the number format so siunitx knows how much space to allocate. If your numbers have one place before the decimal separator and six after it and they include a sign, you would say column type={S[table-format=-1.6]}.

\documentclass{memoir}
\usepackage{pgfplotstable}
\usepackage{siunitx}
\usepackage{longtable}

\pgfplotstableset{
begin table=\begin{longtable},
end table=\end{longtable},
}

\begin{document}
\pgfplotstabletypeset[%
col sep=comma,
string replace={---}{\textemdash}, %
columns/atomnum/.style={int detect,column type=c},%
columns/atomtype/.style={string type,column type=c},%
columns/x/.style={string type,column type={S[table-format=-1.6]}},%
columns/y/.style={string type,column type={S[table-format=-1.6]}},%
columns/z/.style={string type,column type={S[table-format=-1.6]}},%
columns/nmrshieldtens/.style={string type, column type={S[table-format=3.4]}},%
columns/nmrscaledshifts/.style={
string type,
column type={S[table-format=2.4]}},%
every last row/.style={after row=\bottomrule}%
]{
atomnum,atomtype,x,y,z,nmrshieldtens,nmrscaledshifts
1, C ,2.645924,-0.284964,1.121739,145.6803,38.1985
2, C ,3.061887,-0.314747,-1.738533,138.1991,45.6797
3, C ,4.192868,-0.744373,-0.81303,152.3163,31.5625
4, C ,3.609622,-1.293585,0.487818,158.2814,25.5974
5, H ,2.179934,-0.733573,2.004648,29.7536,2.1014
6, H ,3.222428,0.584598,1.466674,30.9379,0.9171
7, H ,2.482083,-1.204262,-2.037409,28.7647,3.0903
8, H ,3.460033,0.135058,-2.653243,29.1953,2.6597
9, H ,4.8172,-1.495056,-1.309066,30.3061,1.5489
10, H ,4.826006,0.126259,-0.596497,30.5419,1.3131
11, H ,4.407002,-1.530092,1.200573,30.3571,1.4979
12, H ,3.088045,-2.236207,0.278539,30.219,1.636
13, C ,-0.54058,-1.145592,0.929915,143.9137,39.9651
14, C ,0.528585,-0.902319,-0.152946,143.2553,40.6235
15, C ,1.550392,0.221805,0.156845,124.581,59.2978
16, C ,0.851532,1.450518,0.80068,133.676,50.2028
17, C ,0.027293,1.042491,2.023216,147.7008,36.178
18, C ,-1.109706,0.152585,1.53285,140.8497,43.0291
19, H ,-0.057217,-1.683382,1.759072,30.1175,1.7375
20, H ,0.034879,-0.650236,-1.095222,30.0622,1.7928
21, H ,1.056471,-1.849267,-0.318437,29.5661,2.2889
22, H ,1.648183,2.146325,1.095154,30.7693,1.0857
23, H ,0.631687,0.517402,2.770335,29.9253,1.9297
24, H ,-0.375923,1.941621,2.506378,30.4385,1.4165
25, H ,-1.749305,-0.12778,2.381335,30.3971,1.4579
26, C ,-0.086446,2.231626,-0.127235,121.2219,62.6569
27, C ,-2.015491,0.916314,0.549213,113.5813,70.2975
28, H ,-0.424133,3.132366,0.42693,29.8436,2.0114
29, H ,0.446378,2.597638,-1.011354,28.8531,3.0019
30, C ,-1.676245,-2.03098,0.39515,138.255,45.6238
31, H ,-2.274539,-2.393781,1.244793,30.6875,1.1675
32, H ,-1.25699,-2.917557,-0.100028,30.3609,1.4941
33, C ,-2.623346,-1.297773,-0.5652,156.7968,27.082
34, H ,-2.066223,-1.014811,-1.468623,29.9263,1.9287
35, C ,-3.14384,-0.01614,0.093255,140.5753,43.3035
36, H ,-2.46664,1.767252,1.104586,29.9266,1.9284
37, H ,-3.843967,0.497571,-0.574314,29.7727,2.0823
38, H ,-3.719835,-0.292135,0.988647,31.0288,0.8262
39, C ,-3.780339,-2.203913,-0.982867,159.2459,24.6329
40, H ,-4.373272,-2.494855,-0.106965,31.3512,0.5038
41, H ,-4.447095,-1.695357,-1.687373,30.882,0.973
42, H ,-3.413599,-3.118855,-1.46021,30.8992,0.9558
43, N ,-1.242229,1.456093,-0.582487,---,---
44, C ,-2.064595,2.286645,-1.451361,138.6569,45.2219
45, H ,-1.433234,2.735618,-2.223064,29.7894,2.0656
46, H ,-2.833329,1.693382,-1.950341,29.0105,2.8445
47, H ,-2.559918,3.103363,-0.892348,30.514,1.341
48, N ,2.23005,0.694822,-1.070475,---,---
49, H ,1.515066,0.996756,-1.729488,30.5467,1.3083
}%
\end{document}
-
Perfect! You know I was sure I had tried every iteration for combining the string type option, and the \textemdash but I guess I hadn't quite tested them all. Thanks! –  J M Aug 28 '12 at 6:41