pgfplotstabel - create table with time column

Question

I try to create a table with a column for date and time from a file data.txt. I can create a diagram but when I type \pgfplotstabletypeset for a table following error occurs:

! Package pgfplots Error: Table 'data.txt' appears to have too many columns in line 1:

I also tried adding the option \pgfplotstabletypeset[col sep=comma]{data.txt}. Then I get this error:

! Package PGF Math Error: Could not parse input '2015-09-13 21:00:00' as a floating point number, sorry.

How can I fix this? Thank you!

\documentclass{article}
\usepackage{pgfplotstable}
\usepgfplotslibrary{dateplot}
\pgfplotsset{compat=1.15}
\usepackage{filecontents}
\begin{filecontents*}{data.txt}
date, value
2015-09-13 21:00:00, 922
2015-09-13 22:00:00, 3993
2015-09-13 23:00:00, 3003
2015-09-14 00:00:00, 991
2015-09-14 01:00:00, 2021
2015-09-14 02:00:00, 841
2015-09-14 03:00:00, 2812
2015-09-14 14:00:00, 991
2015-09-14 15:00:00, 231
2015-09-14 16:00:00, 678

\end{filecontents*}
\begin{document}

\pgfplotstabletypeset[
    columns/date/.style={date type={\day.\month.\year},
                                       column name=Datum},
    columns/date/.style={date type={\hour:\minute},
                                       column name=Zeit},
    columns/value/.style={column name=Value},
    ]{data.txt}

\end{document}

Answer 1

There are two problems here:

First, you need to use col sep=comma, because that's how the columns are seperated in data.txt.

And second, date type does not support time. Therefore the commands \hour and \minute are not defined.

If you have controll over the format of data.txt, then the easy way out is to change it to three columns (date, time, value). If you then leave out the seconds, the time column already contains the format you want.

Then use the column type string type for the new column, which will print the contents as is (i.e. it will not be parsed as a number).

The code:

\documentclass{article}
\usepackage{pgfplotstable}
\usepgfplotslibrary{dateplot}
\pgfplotsset{compat=1.15}
\usepackage{filecontents}
\begin{filecontents*}{data.txt}
date, time, value
2015-09-13, 21:00, 922
2015-09-13, 22:00, 3993
2015-09-13, 23:00, 3003
2015-09-14, 00:00, 991
2015-09-14, 01:00, 2021
2015-09-14, 02:00, 841
2015-09-14, 03:00, 2812
2015-09-14, 14:00, 991
2015-09-14, 15:00, 231
2015-09-14, 16:00, 678

\end{filecontents*}
\begin{document}

\pgfplotstabletypeset[col sep=comma,
    columns/date/.style={date type={\day.\month.\year},
                                       column name=Datum},
    columns/time/.style={string type,
                                       column name=Zeit},
    columns/value/.style={column name=Value},
    ]{data.txt}

\end{document}

If you also want to plot the data, you can make four columns like this:

dt, date, time, value
2015-09-13 21:00, 2015-09-13, 21:00, 922

In this case, you have to add a list of columns to the table, so the first is ignored:

\pgfplotstabletypeset[col sep=comma,
    columns={date,time,value},