Skip to Content

CSV Datasource Generator Excel Macro Template v2.0

[Changes on last version]


1. Hierarchy setting enablement


2. Renaming rule from zip to rar as the old zip file is not recognized by Windows Explorer (but working fine with WinRAR).




With the release of 1.6 Design Studio, an outstanding feature – local csv datasource – has been introduced to support local demo purpose.


CSV datasource is not a common csv file. It’s a file set with at least two csv file – datasource.csv and datasource_metadata.csv. The usual way we get the CSV datasource is (1) add online datasource, (2) edit initial view, (3) Extract CSV Data


To simplify the process, I create a 1st version of data source generator template to simulate HANA datasource. With the assistance of this template, the first two steps could be saved.


Please bear in mind: This excel template is only an assistance with limited error-detection. Always remind the right data type is a good deed.


[Step by Step]


#1. Open template. This template is an excel with Macro with file suffix “.xlsm”. Two worksheets available – “Data Sheet” for dataset, “Dataset Metadata” for dataset metadata configuration. By default, some sample data contains in “Data Sheet” worksheet.




#2. Paste your own dataset into the area from line 4 under the “Data Set Area” title line (Like the sample starting line). The first line should always be the title of each column of the dataset. Only up to 100 columns/dimensions+measures are supported.


#3.  Click “Generate Metadata” button, select the whole dataset area (your dataset area), and then the titles will be added into “Dataset Metadata” worksheet corresponding area as dataset field. “HANA Dataset Metadata” worksheet will be activated. (Click pic to see animation)



generate metadata.gif


#4. Configurate metadata for each field. “Role” and “Data Type” are foundamentally important. Any empty value for non-empty field will rise error when generating datasource.

  • Role: Category of the field. “CHARACTERISTIC” – this field should be regarded as a characteristic or a dimension. “KEYFIGURE” – this field should be regarded as a key figure or a measure.
  • Field Names: The name of the filed
  • Data Type: Data type of a field. “DOUBLE” for float, double or decimal. “INTEGER” for integer. “STRING” for vchar, nvarchar… string-like data. “CALENDAR_DAY” for date.
  • Description: The description of a field. Like label in HANA. Any charaters supported.
  • With Unit: Assign a unit charateristic field name to a key figure field. e.g, if a characteristic field “UNIT” is a unit for price, at PRICE field, select “UNIT” at “With Unit” column.
  • Aggregation Mode: For key figures, the Aggregation Mode should always be “SUM”



metadata conf.png


#5. Global Settings is to set the global formatter function. From my test, “GROUP SEPARATOR” and “DECIMAL SEPARATOR” in design studio does not have any effect although I have changed “STANDARD TIMEZONE” to Europe/Berlin. So keep the default and everything will work.


#6. At “Navigation Path” column, if you want to enable hierarchy, put the dimensions/characterastics in expected order. Otherwise, leave this column blank. “Refresh” button is  a quick way to pick up all dimensions/characteristics and fill the column with them in data-defined order. Later can change the sequence.


Sample: Hierarchy definition: Date->Country->City


Define Hierarchy.gif


#7. Final step. Click “Generate DataSource”.


    -If “Navigation Path” is not empty, hierarchy function is enabled and first you should input the hierarchy name.

    -If “Navigation Path” is empty, datasource does not contain hierarchy.

    -Input the datasource name and select folder to store.


Finally, 2 files will be created together.


Then you can add this local CSV datasource to design studio and happy to use with it. (Click pic to see animation)


Sample: Data source in design studio with hierarchy enabled:


datasource in ds.gif



[Download Place]


This excel template is not so large, so I compressed it with a png and attached in this artical. Please follow the steps below to get it:


1. Save the picture below to your laptop


/wp-content/uploads/2015/12/ds_generator_958449.png(<– Save me #1)



(<– Click the link and save it #2.)


2. If you download the picture #1, change the file suffix from “png” to “rar”. (Ignore any warnings due to file type change).

If you download the picture #2, you may get a file named Delete .zip suffix to make it as “Datasource-Gernerator-v2.xlsm”. Now you  can open it directly and skip step 3.

3. Open and extract the rar to your laptop. xlsm excel template now is there!


You must be Logged on to comment or reply to a post.
  • Hi Terry,

    I was not able to convert it into an ZIP format and it kept throwing out an error. Can you please provide us with snapshots of it. My whole team tried and we still couldn't get it working. Do we need a mandatory tool like WinZip or something?

    Please provide us a method.



      • Hi Terry,

        Thank you and i am able to use it now 🙂 ..! May i share the same link with my other colleagues? is it fine?



          • Hi Terry,

            I just stumbled across your blog and tried to download/extract the data generator above.  Unfortunately, I am getting a error that this is not a valid zip file when I change the extension and try to open the .rar file.  Would it be possible for you to place the excel file in a shared directory again since the 90 days has expired?

            Thank you!

    • Hi Terry,

      I'm not able to download from this link due to our security policies. Can you please give me a hint why the first file is not recognized when renaming to ZIP?

      Thanks and regards,


      • Hi Bálint,

        First download the pic which says "Datasource Generator v2.xlsm". The name of the pic is "ds_generator.png". Then rename the file to "ds_generator.rar" and ignore the warning. You should be able to extract ds_generator.rar with WinRAR.

        I find Windows Explorer to extract old zip is not able so I make a little bit changes.

        Have a try and feel free to ask.


  • Hey guys.

    I'm just facing an issue at the moment, when I generate a .csv dataset, all my measure names do not appear in Design Studio (their values appear however).

    Am I missing something?



    • I've found what I was "missing", seems like the Description column on Metadata was "corrupted" - not working properly, by going from a brand new .xlsm the issue disappeared.

      Very nice tool by the way.



  • Hi Terry,

    I downloaded the sheet above and pasted my dataset. But when I tried to generate meta data, I get an error which says " Data selection is not correct". I have selected the data as per the animation you have provided above. Please advise.



    • Hi Devi,

      Sorry for late reply. I'm not sure if you have found the solution yet.. Data selection should starting from data title line (in animation, line 4, DATE COUNTRY CITY UNIT...), not from line 3 in grey "Data Set Area...". From my test, starting from line 3 does have that error.

      Could you please check first whether the data set range you selected is right?

      Should you have any other questions, feel free to leave comment.

      Best Regard,

  • Hi Terry,

    I just stumbled across your blog and tried to download/extract the data generator above.  Unfortunately, I am getting a error that this is not a valid zip file when I change the extension and try to open the .rar file.  Would it be possible for you to place the excel file in a shared directory again since the 90 days has expired?

    Thank you!

  • Hi Terry,

    This is a fantastic tool and opens up lot of opportunities for offline dash boarding by empowering power users to work in self service mode without much IT help to build POC's using offline data.

    I was also exploring the possibility of using this tool to see if we could publish dashboards for users by refreshing the data behind the scenes in the CSV data file  so that when a user opens it up each morning they get the most current data without going through performance issues etc in DS. There would of course be some work involved for IT to replicate the data into the CSV file but could benefit the users significantly. We use this similar concept to publish Xcelsius dashboards by refreshing XML files from BEx queries which then feed into the dashboards to mimic the functionality for publishing dashboards.

    So to simulate this I used your tool to build a CSV and Metadata files wit the sample data. I am then able to setup a datasource and view the data in the file in DS. I then edited the CSV data file to add a couple of more records for different countries by essentially copying some of the other records you have in the sample data but when I try to refresh the new data file in DS I get a failed to instantiate Datasource error even though the metadata for the CSV datafile hasn't changed at all.

    Any ideas what could be going wrong and if this functionality could be supported in your tool ?

    Thanks again for sharing this tool with the community.


    • Hi Shailendra,

      Technically, copying another record line to the end of csv and making some modification is possible. One thing should be noticed is that you have to change pair of conjunctive data  rather than one since you've observed the first row that containing paired columns, for example, two "COUNTRY", two "DATE".

      Moreover, please make sure that the data type is exactly right otherwise the DS resolution of csv will fail.

      Last but not least, since csv is quite formatted, please make sure you don't break any format during modification. (e.g. quotation marks)

      For your publishing idea, currently, CSV offline data is not supported when using DS online mode. With offline mode, is not possible to publish a webpage-like app to users. Therefore, I think, you should think about it more. CSV offline data is only for PoC and testing purpose when no server available.


  • Hi,

    I am getting weird error when I click on Generate DataSource. Its happening even with just 5 col and 5 rows. When I click on Debug macro it highlights

    If Range(fieldNameSeq(0)).Cells(rowI, 1).Value <> "" Then 

  • I just made the same mistake when I first tried this. You should really go exactly by that step by step manual provided above. Especially don't click "Generate DataSource" immediately after clicking "Generate Metadata" but rather fill in the meta data (Role, Data Type, Description) as described in step #4. Then it should work like a charm.

    Cool macro, thanks, Terry Yang!

  • Hi all,

    I am not able to find the metadata file generator file in the link provided,

    please share the same file with me either via a new link or any other means;

    Also it will be really helpful if anyone shares some existing csv datasources and all associated files for demo purpose to build offline csv datasource dashboards;

    Thank you

    • Hi Venkat,


      Already extended the share permission. Try again.


      By the way, As the article says, could you download the picture(at [Download Place, step 1]), change the file suffix from png to rar and then extract it by WinRAR?



  • Hi Terry,

    Would it be possible for you to place the excel file ((new version) in a shared directory again since the 90 days has expired?
    This link (at zip-file from November 17, 2016 at 1:55 am ) is unavailible:

    Thank you!
    Mikhail Makarov


  • I am able to generate the csv file and metadata file but when I add it to Design Studio

    I get failed to instantiate error.  Below is the screenshot of my file.  I have over 8000 rows.

    I have viewed the csv and metadata files and they look fine.  Not sure what's the problem.  Please advise.




  • I found the problem.  It turned out that the product name had special characters in it.  Once I cleaned up the product name I was able to bring the data in the design studio without an issue.

    Below is an example of special characters in the product name.

    Eldon Simplefile® Box Office®


  • Hi,


    The converter seems extremly useful, but I had few issues with it. It ignores every data type that isn't a string. Everytime i put in an Integer, Double etc. i just get left over in csv. Examples of basic data and how metadata converter and finished csv look :

    As you can see the only thing thats get properly converted into csv are objects that are string. Any idea how to resolve this issue?




  • I was looking for something like this.

    But Terry, I receive an error stating “Data Selection is not correct”. I have 2007 Excel on Win10. Not sure if I am doing something wrong. Please let me know.

    EDIT : Found the issue to be Spaces & special characters like hyphen and &. Resolved. Thanks

  • Hi Terry,

    Can you please share the zip file again, or the templates, image #1 and #2 are not working for me.

    Please share with me the links to download Datasource generator v2.xlsm.
    Above links are expired.

    Can anyone please share this zip files.




  • Hi Terry


    First of all; fantastic work and many thanks for this development. I have an issue that I am not able to add hierarchy of more than 3 members? is there any work around.

    Question 2. Can i add more than 1 hierarchies? If yes how?


    Kind regards