detect if csv data source or layout has changed

[gcue]

hello.

on a very regular basis, i use several csv extracts from multiple databases and run scripts to extract data from them.  sometimes a data may change, where the field expected in column 8 for instance is not longer in column 8 but rather in column 15 or not in the csv at all anymore.  one could say this of course relies heavily on error catching when processing the data and the data type is not what is expected.  this might be hard to do sometimes though.  has anyone thought of a way to automatically compare outdated data source A to new data source A to make sure the values are the same?  I know this is a hard ask as data sources can vary significantly - some have column names other dont, or values can just be very different between old and new.  Just polling to see if anyone has given some thought to this topic and if anyone has creative solutions.

Thanks

[Deye]

Please post a sample data output here where the columns are separated by a delimiter so that we can take a look.

that is sampling the old and the new output

Edited February 7, 2022 by Deye

[gcue]

this hasnt happened yet.  just afraid it might.  so it is just a fear i have that it will happen to me one day and i wont know it did 

[water]

Does your CSV file contain a header line holding the name of each column?

[gcue]

sometimes 😕

[Nine]

What I would probably do, is to read one line.  You can count the number of elements in the line, You can also determine the type (string, number) of each element.  I would store all those info into an .ini file, so I can compare it the next time I need to deal with a new file.

[water]

You need some kind of information describing the structure of the CSV file.

• A line with column names like "Date, PartNumber, Price" as header line in the CSV file. So your script can detect every modification
• Data type for each column like "YYYY/MM/DD, Text, Float" as line 1 in the CSV file. So your script can at least detect when the data type changes
• A second file for each CSV file holding the database, table or query result structure. This way you can detect every modification
• ...

 

[gcue]

very good ideas.  thank you

i will try to build something around them and come back if needed

thanks again!

[water]

I think the best solution to your problem is "A line with column names like "Date, PartNumber, Price" as header line in the CSV file. So your script can detect every modification"
So ALL CSV files you process should have this header lines.
This allows to detect new and moved columns and tells you where a moved column has been moved to (or even re-moved)