I wonder if there's an easy way, maybe one liner, with unix cli tools to split a CSV file with ISO-8601 UTC timestamps in millisecond precision (+%FT%T.%3NZ
, e. g. 2021-05-27T13:59:33.641Z
) along a defined time offset / break / difference, like for example two hours.
As always there are certain different ways of having it and while for other users with similar questions, other options might also be relevant in a comprehensive answer, I ...
- ... use/have git 2.31.1's
GNU Bash 4.4.23
,GNU sed 4.8
,GNU Awk 5.0.0
(and all the other tools it bundles),xsv 0.13.0
andjq 1.6
on Windows 7 - ... would rather use this in a script that in an interactive shell
- ... use a semicolon (
;
) as delimiter, no comma - ... do not have my values quoted (e. g. in single (
'
) or double quotes ("
)) - ... do not have a header
- ... would already have the entire CSV in a variable and would also want to have the result in variables (an array?) in order to be able to further analyze them
- My columns do not have a fixed lengths in reality and may contain spaces and hyphens besides alphanumerical characters
- The timestamp is the fifth of eight columns in my real world data
- The file can be assumed to be at most 250k lines and 20 MiB
- While it would be preferable if the script/command took less than half a second on my i5-4300U, 5 to 10 seconds max would still not be a dealbreaker
Example
If I had 2 hours
as offset to use for my split (and I did not mix anything up), this file:
abc;square;2021-05-27T14:15:39.315Z
def;circle;2021-05-27T14:17:03.416Z
ghi;triang;2021-05-27T14:45:13.520Z
abc;circle;2021-05-27T15:25:47.624Z
ghi;square;2021-05-27T17:59:33.641Z
def;triang;2021-05-27T18:15:33.315Z
abc;circle;2021-05-27T21:12:13.350Z
ghi;triang;2021-05-27T21:15:31.135Z
would get split to the following three parts
abc;square;2021-05-27T14:15:39.315Z
def;circle;2021-05-27T14:17:03.416Z
ghi;triang;2021-05-27T14:45:13.520Z
abc;circle;2021-05-27T15:25:47.624Z
ghi;square;2021-05-27T17:59:33.641Z
def;triang;2021-05-27T18:15:33.315Z
abc;circle;2021-05-27T21:12:13.350Z
ghi;triang;2021-05-27T21:15:31.135Z
disclaimer: I am no native speaker, so if rewording makes this question more comprehensible please go for it. The verbosity re. e.g. also specifying the options that do not apply to my use case (comma, quotes) or using both the word semicolon
and the sign ;
in this question's text is for SEO purposes
would already have the entire CSV in a variable and would also want to have the result in variables (an array?) in order to be able to further analyze them
- for efficiency, robustness, simplicity, brevity, clarity, portability, etc. having your entire CSV in a variable is a bad starting point, outputting the result into a shell array or variable is a bad ending point, and continuing to analyze the CSV in shell afterwards is a bad approach. A shell is an environment from which to create/destroy files and processes and sequence calls to tools such as awk to analyze text.