1

I have hundreds of csv files downloaded from an EXO instrument that I want to reformat. The first part of every file contains general information on the instruments followed by a table with header and measurements.

My goal is to delete everything in these files up to my header, which starts with "Date". This has been working seamlessly on practice files using the command:

#!/bin/bash

sed '/Date/,$!d' test.csv > newfile.csv

The problem is that when I try to run this command on my raw csv data files the output file is empty. If I copy and paste this raw data file into a new sheet and save it as a csv file it works. I am able to use the sed command replacing digits and deleting specific lines using line numbers in my raw data files, so I know that sed is generally accessing these raw csv files. Does anyone have any idea where the root of my issue might lie? Thank you, and please let me know if clarification is needed.

Example of file:

sep=,
Unit ID:,
User ID:,
Site:,hrA
Log Interval:,60.00

Model, Submodel, S/N, S/W Ver
Handheld,2, 18C102285,1.0.33
4P Sonde,1, 17F104914,1.0.73
CT,1, 17F101355,3.0.5
ODO,1, 15J101782,3.0.0
pH/ORP,1, 18F103460,3.0.0
Turbidity,1, 15H104135,3.0.0
Depth,3, 17E101397,3.0.0

Date,Time,°C,mmHg,DO %,DO mg/L,DO %L,SPC-uS/cm,TDS mg/L,SAL-ppt,pH,ORP mV,NTU,Chl RFU,Chl ug/L,DEP m,Lat,Lon
07/22/2019,08:20:22,24.861,757.7,98.0,8.12,98.7,5.6,3.629,0.00,7.15,276.5,6.20,,,0.000, 41.93080,-70.06403,

Example of the file:

enter image description here

8
  • Can you provide the test.csv file that is not working. At least one line before the one containing 'Date' and one line after that line. Jun 17, 2022 at 15:51
  • From where do you get that csv file? From a Linux, MacOS, or Windows operating system? There might be different line feed syntax, that causes confusion.
    – sudodus
    Jun 17, 2022 at 15:53
  • 1
    I am working with Ubuntu on a Windows operating system. When I copy paste the file into a new file and rename it, it does work. I will paste an example of what the file looks like into the question. Jun 17, 2022 at 15:55
  • 1
    Take an od on the raw file and see how the line endings are set up. A CR left at the beginning of the line might mess things up.
    – ubfan1
    Jun 17, 2022 at 16:02
  • @ubfan1 Thanks for your quick reply! Looking at notepad I don't see any issues in the file. The sed command works using digits, it's just not working with text. Jun 17, 2022 at 16:25

2 Answers 2

0

I have tested your input file and I have no problem, using end of line both for win and for linux.

Please try to use grep:

<test.csv grep -P '^(Date|[0-9]+/)' > newfile.csv

It's not exactly what you ask, but I hope it works for you.

2
  • Thank you for your answer! Your command works on the test files I created, but unfortunately not on my raw input files, they are still creating empty new files. Jun 21, 2022 at 12:03
  • @SophiaFeuer I can help you, only if I have something similar to your raw input. Could you share something more similar to the raw data?
    – aborruso
    Jun 21, 2022 at 13:58
0

I believe your csv file contains a formatting character called NBSP (possibly inherited from Windows).

NBSP stands for "no-break space". It is like an ordinary space, but it's invisible. It's the two bytes c2 a0 (hex values) in UTF-8 encoding and in HTML it's usually written " ". Check this Wikipedia link for more details https://en.wikipedia.org/wiki/Non-breaking_space

As you have noticed it's a nuisance in command-line data work, because a command that relies on recognising an ordinary space and doing something about it will fail if the space is a NBSP. You can run a test using this sed command to "see" in green colour the offending NBSP gremlins:

$ sed 's|\xc2\xa0|\x1b[102m\xc2\xb7\x1b[0m|g'

copy/pasting may get rid of NBSPs but isn't always successful. NBSPs are retained when copy/pasting into an ODT or ODS document. Your best bet is to past your file into text editors or a terminal emulator to be sure you have clean your documents. Good luck!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .