Howdy Chaps.
I have a metric tonne of weather data. I have a file for each day. Each file is commadelimited with headings.
The columns are common throughout each file, eg:
Time,TemperatureC,DewpointC,PressurehPa, ...
Merging all these files and graphing them reveals that some files are (seemingly randomly) Temperature °F instead of Temperature °C.
I would like to change every temperature in °F into °C – the only means of being able to tell whether the temperature is in °F or °C is the heading of each file, or from context (time of day, time of year).

What’s the best way to systematically open each file, see if a comma delimited heading contains an F, and converting the vertical column below it into C and saving?
Got about 3,220 files.
I would prefer not to use Excel if at all possible… Python?

Excel is 100% the wrong thing for the job imho.

Lots of different ways to script it. I’d be using bash command line in Linux for most of it, though. Wouldn’t know where to start with windows scripting.

Would probably start with 1 script that converts all the F ones to C to get a clean set of files in deg C in a new folder. Then just cat the lot together (assuming the filenames are in alphanumeric order).

Lots of different ways to script it. I’d be using bash command line in Linux for most of it, though. Wouldn’t know where to start with windows scripting.

Teach me! I’ve got a linux VM all sync’d up and ready for this kinda stuff.

The merging isn’t a problem at all, just the investigation and subsequent conversion bit that I’m not really sure where to start on.

Excel was just to get some attention 😉

Fancy using Perl? I can get you started..

Open your first file (with all your files, you’ll need to add some way of parsing a directory tree and reading in each filename):

open my $file, ‘<‘, “filename.txt”;
my $firstLine = <$file>;

#See if firstline contains your C or F
$substr = “C”
if (index($firstLine, $substr) != -1)

#and if it’s true then read through your file doing your conversion:
while (my $line = <>) {
# do whatever you like with $line. Might be easiest to write it out to your new file
}