Feillen Feillen - 1 year ago 99
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

files, so I would need to export my
first and then import the
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,

Any help will be more than welcome.


  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
    (which is quite the surprise knowing how extended the use of Excel is). Writing one for the
    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 Source

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
        ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\new_file" & l_counter & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    Next ws

End Sub
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download