Concatenating CSV Files and Producing Excel and HTML Versions

Goal of the Script

This Python script automates the process of combining multiple CSV files into a single cleaned dataset. It reads CSV files from a specified folder, combines them into one DataFrame, removes duplicate records based on the Link field, sorts the data, and produces Excel and HTML versions of the cleaned dataset with clickable hyperlinks.

Code Description

The script uses several Python libraries to perform data processing and generate output files:

• pandas is used to read CSV files, concatenate datasets, remove duplicates, sort records, and manage DataFrames.
• glob and os are used to locate input CSV files and manage file paths.
• openpyxl is used to create an Excel workbook and format the Link column as clickable hyperlinks.
• logging is used to display progress and execution messages.

Processing Steps

1. The script identifies all CSV files in the input folder.
2. Each CSV file is read into a pandas DataFrame.
3. The DataFrames are combined into a single dataset.
4. Duplicate records are removed using the Link column.
5. The cleaned dataset is sorted for easier review and analysis.
6. The final dataset is exported as CSV, Excel, and HTML files.

Summary

This script demonstrates practical Python data-processing techniques, including file handling, pandas DataFrame manipulation, duplicate removal, sorting, and automated report generation. The resulting Excel and HTML files provide convenient formats for reviewing and sharing the cleaned dataset.