Inserting Header Row into Multiple Excel Workbooks

-1

I'm regularly given a large number of single-sheet excel files (xlsx). They vary in the number of records, but they always have only five columns.

I need to insert the same header row into each file. Then I need to export all of them to CSV format. I need a way to automate this process.

I could get a macro to do them one at a time, but I'd like to just be able to process an entire folder of Excel files at once. I would appreciate it if you could point me in the right direction.

13ruce

Posted 2017-07-25T19:17:03.120

Reputation: 213

Please note that https://superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.

– DavidPostill – 2017-07-25T19:41:01.670

I understand and appreciate that this is not a free scripting/coding service. That's why I asked to be pointed in a direction, rather than having a turn-key solution provided with a ribbon. The sticking point for this project was the beginning, so there wasn't much I could try with my knowledge. As I said, I could set up a macro to do them individually, but I don't know how to go about batching a load of excel files all at once unless they're ALL open. I think 1NN's suggestion is going to be a great way to approach this. – 13ruce – 2017-07-25T19:50:01.440

Answers

I recommend you reverse the process:

1) batch convert your excel files to csv (there are multiple tools out there: google for your preferred solution, search for "Batch Excel To CSV Converter" or similar)

2) use some VB script to add the header row to the csv files, instead as to the excel files! CSV are basically text files, so they are much easier to handle.

1NN

Posted 2017-07-25T19:17:03.120

Reputation: 120

Using 1NN's excellent suggestion, I ended up downloading a free converter from here. I've offered the writer of this app some money, and I hope he accepts. Then I wrote a little batch file that inserts the header as follows:

for %%f in (*.csv) do (
    type header.txt >%%f.new
    type %%f >>%%f.new
    move /y %%f.new %%f
 )

13ruce

Posted 2017-07-25T19:17:03.120

Reputation: 213