1

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.
Feillen
  • 109
  • 9
  • csv files don't have mulitple sheets. You would have to create 1 file per xlsx sheet. – Jean-François Fabre Nov 02 '16 at 09:10
  • I am aware of that – Feillen Nov 02 '16 at 09:11
  • 1
    I could help you converting a xlsx in a list of csv in python. but your question is really too broad. – Jean-François Fabre Nov 02 '16 at 09:13
  • That would be too nice of you, just with the knowledge that it can be done that way is enough. I'd post an answer with the code I developed if I follow this option in the end. Also, I also think that the scope of the question may be too broad too, but this programmers community is the best in terms of expertise and helpfulness, while also being quick to give an answer. Thus I was hoping someone as helpful as you would save me countless minutes/hours of researching and offer me a general direction (which is what I needed). The problem would finally be solved from start to end. Great, huh? – Feillen Nov 02 '16 at 09:21

1 Answers1

1

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

Important Edit:

Make sure that you make a save, before using the code. Because it would change the names of the Excel tabs. Read more here:http://windowssecrets.com/forums/showthread.php/33489-Sheet-name-changes-when-saved-in-CSV-format-(XL2000)

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    This won't work - it'll just save the currently active sheet multiple times under different names. Moreover, it doesn't deal with the issue of getting the `.csv` files into the database. – asongtoruin Nov 02 '16 at 09:56
  • True, I forgot to activate the sheet through the loop. Now we should get a different csv file per sheet. From there, it is another question how it would go to the DB. – Vityata Nov 02 '16 at 10:01
  • Thanks, @Feillen :) – Vityata Nov 02 '16 at 10:02
  • This looks gorgeous! Thank you both for the input. I will deal with the DB-import separately but this is a **great** step forward :) – Feillen Nov 02 '16 at 10:05
  • @Feillen, just make sure that you make a save, before using the code. Because it would change the names of the Excel tabs. Read more here:http://windowssecrets.com/forums/showthread.php/33489-Sheet-name-changes-when-saved-in-CSV-format-(XL2000) – Vityata Nov 02 '16 at 10:39
  • @Vityata one easy way to get around this is to `Copy` each sheet in turn - when you don't specify an output location it copies to a new workbook. This workbook then becomes the `ActiveWorkbook`, thus you can save it as a csv and then close it without affecting the original file. – asongtoruin Nov 02 '16 at 10:50
  • @asongtoruin, true, but I was a little just scared, because I tried the code in a wb, which I used for something else. Thus, a take away note for me for SO - always test code in a new wb :) – Vityata Nov 02 '16 at 10:52
  • @Vityata a different work around would be to keep the sheet's name as the name of the file to be exported, `Filename:=ThisWorkbook.Path & "\" & ws.Name & ".csv"`, _heh_ – Feillen Nov 02 '16 at 11:03
  • @Vityata : I have edited the answer inlcluding the final piece of code I have adopted, could you give it a look and point out any errors it could produce? I don't really know any VBA so... :D – Feillen Nov 02 '16 at 11:58
  • @Feillen Which answer? – Vityata Nov 02 '16 at 12:10
  • @Vityata I think that due to my low influence it requires someone's approval, and only I am able to preview it for now. Anyway, you can view it [here](https://s22.postimg.org/o96eygnj5/Untitled.png) – Feillen Nov 02 '16 at 12:59
  • @Vityata I totally forgot about pastebin, sorry. [Here you go](http://pastebin.com/WBkN6t5b) – Feillen Nov 02 '16 at 15:01
  • @Feillen, this is what I came up with. http://pastebin.com/wG7xmWrh I don't know why you need to close the application with application.quit, but it is probably ok. In the header of the code I have written what I have done. Cheers :) – Vityata Nov 02 '16 at 15:25
  • I don't really understand the need for all that code but thanks a lot @Vityata after giving it a thought I don't really need to use `Application.Quit`since I will be doing it _from outside_ anyways. My objective is to call this macro without opening the excel file, what I plan on achieving using [this](http://stackoverflow.com/questions/2050505/way-to-run-excel-macros-from-command-line-or-batch-file), where _MyWorkbook.xls_ is actually _MyWorkbook.xlsm_ and _MyMacro_ is _MyWorkbook!MyMacro_ if I am not mistaken – Feillen Nov 02 '16 at 15:37
  • @Feillen, the error handlers are just a good practise in VBA and the OnStart and OnEnd are something that I usually use. I have added it, because I noticed that you used `Application.DisplayAlerts = False` – Vityata Nov 02 '16 at 15:39