Combine Excel Template with csv data

Question

Hi - I'm trying to combine data generated in the form of .csv file with an existing excel template.

Excel template contains a sheet with data table that needs to be populated to contents of .csv file
and also have some of the cells written with current day etc.

Is this possible in OpenXML? i will be doing this in Azure Batch where there is no office installed.

Does anyone have any suggestions or pointers please?

Answer 1

Hi !

I can suggest an option to do this with Power Query.
To combine multiple csv files into one Excel workbook, these are the steps you need to follow:

1. Put all your CSV files into one folder. Make sure that folder does not contain any other files, as they may cause extra moves later.
2. On the Data tab, in the Get & Transform Data group, click Get Data > From File > From Folder.
3. Browse for the folder into which you've put the csv files and click Open.
4. The next screen shows the details of all the filles in the selected folder.
   In the Combine drop-down menu, three options are available to you:
   • Combine & Transform Data - the most flexible and feature rich one.
     The data from all csv files will be loaded to the Power Query Editor, where you can make various adjustments:
     choose data types for columns, filter out unwanted rows, remove duplicates, etc.
   • Combine & Load - the simplest and fastest one. Loads the combined data straight into a new worksheet.
   • Combine & Load To… - allows you to choose where to load the data (to an existing or new worksheet)
     and in what form (table, PivotTable report or chart, only a connection).

I hope this will help you !

Answer 2

Not sure if i failed to clearly explain.i'll try again -

I have a csv file and an excel with pre formatted table headers in one sheet, other sheets with specific cells showing current date.

Task is to populate the excel with csv data and update specific cells with datr and other static values. Finally save that excel with new name.

All this has to be done automatically and for 50 such excel templates and every day.

So everyone morning 50 excel reports need to be created.