Hi Team - My project has a requirement to provide users the flexibility to upload their data using a xlsx or csv file directly to a TM1 cube. This file gets generated from a different system.
I was testing the XLS upload feature to achieve this requirement as this will allow users to upload files directly from their local system into Apliqo.
I ensured that the upload file has the same row and column structure however the upload keeps failing as it doesn’t like the format of the Date and Time in the file as these elements are defined as strings within the dimension and while uploading they show as numeric within Apliqo preview window.
I tried downloading the file from Apliqo and uploading it as is which works hence I tried to copy paste the values from the Users file to the Apliqo generated file however the issue persists. I tried updating the excel formatting of the date and time as per the Apliqo generated file without any luck. Please refer screen-print for details.
Can you please advise how can we ensure that the values are correctly copied from the source file to Apliqo generated file or is it feasible to use the system generated file itself to upload to Apliqo by changing the formatting of Date and Time to Apliqo acceptable formats. Thank you in advance.
Hi @rkuma145 this isn’t really a Apliqo UX problem, it’s a generic MS Excel problem. Excel tries to be super helpful by automatically converting any cells which it recognizes as time or date into numeric values and _formatting_the value as date time.
There are a few things which you can do to get around this.
-
make sure the couumns in the Excel file are set to Text in the cell format > number format options. This will stop Excel automatically converting the values to numeric (but this can be difficult since users can always easily accidentally convert the cell type back while editing.)
-
allow the data and time columns to be numeric but have additional helper columns which convert the value to text. Use these columns in the Excel upload sheets.
-
create aliases in the time dimensions equivalent to the numeric index value (easy for dates but time could be a bit more problematic as you are dealing with fractions of 1/86400). E.g. from the example above element “2023-01-01” inthe date dimension would have an alias of “44927” which is Excel’s date index value (of number of days since 1st Jan 1900).
Thank you for the response.
I tried to convert the Cell Format to “Text” as per suggested option 1 however the issue persists, In order to rule out formatting, I tried converting .xlsx file to both .csv or .txt without any luck. I verified that the .txt file has the date and time in the correct format, refer attachment.
Can you please confirm in which format data gets downloaded in Load Format in Apliqo, I tried updating the format for date and time in the same format as I could see in the download file without any luck. Is there a way to retain the cell formatting as per the load format file post values from uploading from the external file.
Can you please confirm how data is getting uploaded while using feature, I don’t see any TI process listed in the Button or nothing is showing up in tm1server.log within content store either.
Calendar_Upload.txt (881 Bytes)
Hi Team - Can you please share an update, I have a deadline coming up hence appreciate your inputs.
Precisely, I am looking for an option that allows me to convert any external csv file having date and time data into a format acceptable by Apliqo xls upload feature.
Please let me know If there are any alternative widgets/features within Apliqo apart from XLS Upload that allows users to upload files directly to Apliqo
Thanks,
Rupesh
To use the “upload from Excel” feature the file being uploaded must be an Excel file, not a txt or csv.
I think I have already been clear on explaining what needs to be done on either the TM1 side with aliases, or in the Excel document with cell formatting in order to solve the issue. If this doesn’t make sense to you then I suggest you reach out locally to a collegue with deeper Excel knowledge.
