Feillen Feillen - 1 month ago 5
Python Question

Exporting whole Excel file as CSV

in my scenario, there are some excel files (each of them with possibly more than one sheet) that I will be updating frequently and which contents are to be imported into a PostgreSQL database almost as frequently. This db manager does not work directly with

.xlsx
files, so I would need to export my
.xlsx
as
.csv
first and then import the
.csv
into the database.

I have looked into different questions on the topic but I don't think any of them really solves my problem. I am not looking for a plain piece of code that could solve this situation (although that would be great) but a general direction as to how to approach the solution (VBA, Java/C#/Python program,
.bat
executable...).

Any help will be more than welcome.

Side-notes:


  1. it has not been my decision to use PostgreSQL as DBM, I know there are other DBMs that work well with excel files.

  2. I have not learned VBA yet but I could very well use this as an excuse to start doing so if needed.

  3. I am aware of the existence of FDW but I have not found any for
    .xlsx
    (which is quite the surprise knowing how extended the use of Excel is). Writing one for the
    .xlsx
    file format would be an option but I would really like to avoid this since I may be a bit short in time and I am guessing that it will require much of it.


Answer

So, with VBA you may do the following:

  • Loop through each sheet in the Excel application.
  • Save it as a CSV file.

Something like this would do the work:

Sub vba_code_to_convert_excel_to_csv()

    Dim ws                  As Worksheet
    Dim l_counter           As Long

    For Each ws In ThisWorkbook.Sheets
        l_counter = l_counter + 1
        ws.Activate
        ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\new_file" & l_counter & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    Next ws

End Sub