Combine Excel Template with csv data

Rakesh Singh 16 Reputation points
2022-01-28T09:47:29.82+00:00

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?

Azure Batch
Azure Batch
An Azure service that provides cloud-scale job scheduling and compute management.
301 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Anonymous
    2022-01-28T19:38:27.507+00:00

    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 !

    2 people found this answer helpful.
    0 comments No comments

  2. Rakesh Singh 16 Reputation points
    2022-01-31T19:18:09.86+00:00

    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.

    0 comments No comments