Convert XLS File to CSV

Question

Has anyone worked on a program to convert Excel XLS files to CSV? I have found numerous resources, but can't seem to get them to work in Visual Studio. I'm most familiar with C#, but if anyone has had luck with other languages please let me know. Thanks in advance.

Answer 1

You can do it in Python using numpy and one of the Python Excel libraries. It’s not so complicated

Answer 2

One way to overcome the learning curve is to make a copy of the Excel file, change the extension from .xlsx to .zip, extract the zip to a folder, and open that folder using VS Code, Sublime, Notepad++, etc like it's a project.

You can then see how all the XML files and elements map to the loaded object model. Just place a breakpoint after the OpenXML document is loaded and expand on the object properties while debugging.

By having the files accessible in a text editor, you quickly search against all XML files for quick reference.

--------
Another Option: Create an SSIS Job
--------
SSIS would be an easier option where you can use it to load an Excel file and map columns from the source file to a target table and / or a CSV file. This could then be saved as an SSIS job which could run as Excel files are dropped into a specific location.

Answer 3

Have you attempted to do this with OpenXML?

If not, that should be what you need to extract data from the Excel Document Object Model.

What other resources have you seen so far?

Answer 4

I second David Carroll 's suggestion.

Assuming you have a need to do this task programmatically, I have used OpenXML with C# for this. There's a bit of a learning curve in using it, but once you understand the structure of a document/spreadsheet, it's a breeze. Mind you it's easier to read and parse a file than it is to build something from scratch with OpenXML. There are many examples out there that will show you how to do what you need.

Answer 5

thank you all for your suggestions!

Answer 6

David Carroll no problem. I was trying to remember the name of the tool earlier but it slipped my mind until today when I actually used it for one of my own projects lol.

Answer 7

One thing to add... if you want a program to help you understand the structure of a document in OpenXML, download the OpenXML productivity tools. It can help you understand how to write your code.

Answer 8

Mike... Bruh... I had no idea that little tool existed. 🤦‍♂️

I've actually learned a lot on my own by extracting the document XML files out into a folder, then opening the folder as a project in Sublime, Notepad++, or VS Code for closer review.

I've also learned a ton by reviewing the source code in OpenXML and OpenXML PowerTools as well as stepping through the code debugger while running their unit tests.

Next time, I'll know to check this tool out. Thanks for sharing that bit of knowledge man. 🤓👌