I am handling a large-scale data migration project and need to convert hundreds of legacy Excel (.xls and .xlsx) files into CSV format for a SQL database import. Doing this manually in Excel is not an option. Can someone provide a Pythonic way to automate this conversion? I am specifically looking for a solution using the Pandas or Openpyxl library that can handle multiple sheets within a single workbook while maintaining the correct data encoding.
3 answers
The most efficient way to handle this is using the Pandas library along with the Openpyxl engine. You can use pd.read_excel('file.xlsx', sheet_name=None) to read all sheets at once into a dictionary of DataFrames. Then, you can loop through the dictionary and use the to_csv() method for each sheet. This approach is highly scalable for data science workflows because Pandas handles type inference automatically. Make sure to specify encoding='utf-8' in your to_csv call to prevent any character corruption, especially if your data contains special symbols or non-English characters.
That Pandas approach is excellent, but have you considered the memory overhead when dealing with exceptionally large Excel files? If a workbook is several hundred megabytes, loading it entirely into a DataFrame might crash a standard local environment. Is there a way to stream the conversion or use a lower-level library like xlrd to process the file row-by-row to save on RAM?
If you don't want to install heavy libraries like Pandas, you can use the tablib library. It’s very lightweight and specifically designed for format conversions like XLS to CSV with just a few lines of code.
I agree with Barbara. tablib is great for simple tasks. However, as Amanda mentioned, if the goal is a database import, the Pandas route is usually better because it allows you to perform data cleaning—like stripping whitespace or formatting dates—during the conversion process itself, saving you a lot of time later in the pipeline.
Michael, you've raised a very valid point for large-scale data engineering. In those cases, I recommend using the openpyxl library in "read-only" mode. By setting load_workbook(filename, read_only=True), you can iterate through the rows without loading the entire file into memory. You then use the standard Python csv module to write each row immediately to a file. This keeps the memory footprint extremely low, which is a best practice for Robotic Process Automation (RPA) tasks running on limited virtual machines.