Page 1 of 1
Upload CSV via TI Process in TM1 Web
Posted: Thu Jun 23, 2011 3:51 pm
by JFerguson
Hi
Has anyone (not as an admin user) uploaded data from csv files using a TI process over TM1 Web, over the internet?
We are looking at using TM1 Web as a possible method to upload data to TM1 from users located around the world not on our LAN.
One possible pitfall I can foresee is that the TI process will need to specify the file location of the csv located somewhere on the end user's network (will be different for each user) - I suppose we could use a websheet where the full network path is inserted by the user and an action button to 1. update the TI process with the path and 2. to run the upload process.
Are there any other things we need to consider before embarking on this project?
many thanks for your guidance.
Jane
Re: Upload CSV via TI Process in TM1 Web
Posted: Thu Jun 23, 2011 4:04 pm
by David Usherwood
TI processes run on the TM1 server. How is it going to see the local file shares?
You'll probably need to create a central share (or better, an FTP site), visible to the TM1 server, where the users drop their files before running the load process.
Re: Upload CSV via TI Process in TM1 Web
Posted: Fri Jun 24, 2011 7:47 pm
by image2x
We've done this sucesssfully for some time using the http upload capability.
You'll need to write/find an asp app to handle the http upload process with the csv saving to a directory TI can read. The user then kicks off the TI to load the file. Keep in mind that TI processses run essentially as Admin so no security (other than custom checks you'd implement in TI) will be applied. With a little creative wrapping, you can even convert any generated error files into html, saving them to a user specific directory which can browsed so that users can self-diagnose problems.
While this works, TM1 definitely needs a web-based bulk import solution especially given the problems with large copy+pastes thru tm1web.
Re: Upload CSV via TI Process in TM1 Web
Posted: Sat Jun 25, 2011 6:12 am
by lotsaram
image2x wrote:We've done this sucesssfully for some time using the http upload capability.
You'll need to write/find an asp app to handle the http upload process with the csv saving to a directory TI can read. The user then kicks off the TI to load the file. Keep in mind that TI processses run essentially as Admin so no security (other than custom checks you'd implement in TI) will be applied. With a little creative wrapping, you can even convert any generated error files into html, saving them to a user specific directory which can browsed so that users can self-diagnose problems.
While this works, TM1 definitely needs a web-based bulk import solution especially given the problems with large copy+pastes thru tm1web.
Sounds like this might be usefull for many folks out there. Are there any complnents of the solution that you would be willing/able to share?
Re: Upload CSV via TI Process in TM1 Web
Posted: Mon Jun 27, 2011 3:12 pm
by image2x
Sure.
The http upload part can be accomplished via several free and commercial options. Just search "http upload asp".
After the user completes the upload, they input the name of the their .csv file into a tm1 web form (passed as pFilename) and click to execute the TI process. The TI source points to the actual load file which is manipulated to become the user's load file.
Code: Select all
### Prolog
User = TM1User;
timestamp = TIMST(now, '\m-\d-\Y_\h_\i_\s_GMT');
# copy blank load file to source load.
# blank contains a message suggesting users check the filename they entered
# which will only be seen if the overwrite of the blank file fails
vCmd = 'cp /appdata/temp/gl_upload_blank.csv /appdata/temp/gl_template_load.csv';
ExecuteCommand(vCmd, 1);
# copy user load file to source load
vFilePath = '/opt/tm1_share/template_uploads_gl/' | pFilename ;
vDestPath = '/appdata/temp/gl_template_load.csv';
vCmd = 'cp "' | vFilePath | '" "' | vDestPath | '"';
ExecuteCommand (vCmd, 1);
# log activity
vCmdEcho = 'echo "'| timestamp | ',' | User | ',' | pFilename | '">> /appdata/temp/gl_upload.log';
ExecuteCommand (vCmdEcho, 0);
Code: Select all
### EPILOG
ErrLog = GetProcessErrorFilename;
if (ErrLog @<> '');
#copy to specific user error log directory
CopyFrom = '"' | GetProcessErrorFileDirectory | Errlog | '"';
CopyTo = '/opt/tm1_share/process_error_logs/' | User ;
vCmd = 'mkdir -p ' | CopyTo;
ExecuteCommand (vCmd, 1);
CopyTo = '"' | CopyTo | '/' | timestamp | '_gl_upload_error_results.html"';
vCmd = '/appdata/scripts/tm1process/copy_err_msg.pl ' | CopyFrom | ' ' | CopyTo | ' &';
ExecuteCommand (vCmd, 0);
EndIf;
Code: Select all
copy_err_msg.pl perl script
#!/usr/bin/perl
$CopyFrom = $ARGV[0];
$CopyTo = $ARGV[1];
sleep 4; # wait as error log file isn't always written immediately by TM1
system ("echo \"<html><pre>\" > \"$CopyTo\"");
system ("cat \"$CopyFrom\" >> \"$CopyTo\"");
system ("echo \"<\pre>\" >> \"$CopyTo\"");
On the bulk upload web form, include a link to the user error log directory with something like:
Code: Select all
=HYPERLINK("http://tm1/share/process_error_logs/" & TM1USER("TM1 Server"), "Click to view load process error reports")
The above was written for unix so you'll need to adjust paths and commands as appropriate. As I recall, the reason for executing the html insertion via an outside command (Perl in this case) instead of doing ExecuteCommands in TI was because the error file doesn't get written until after the TI process has completed.
-- John
Re: Upload CSV via TI Process in TM1 Web
Posted: Tue Jun 28, 2011 11:00 am
by lotsaram
Thanks. The TI side and calling external scripts from TI is pretty clear. Actually I was much more interested in the http file upload portion that comes first. How was this integrated into TM1 web or launched from TM1 web? Or was this done first via a separate process and separate web portal?
Re: Upload CSV via TI Process in TM1 Web
Posted: Tue Jun 28, 2011 8:46 pm
by PlanningDev
It doesn't appear that hyperlinks in excel files work in TM1 Web.
Re: Upload CSV via TI Process in TM1 Web
Posted: Tue Jun 28, 2011 9:16 pm
by lotsaram
PlanningDev wrote:It doesn't appear that hyperlinks in excel files work in TM1 Web.
They do. It is just a mater of storing the files on the web server in a known location and following the correct file path syntax in the hyperlink (IBM does have documentation on it but can't put my finger on exactly where.) It means you can link to external files such as word or PDF that are not loaded in the application folder tree and you can also load excel files as websheets which are not saved as application objects - note however that hyperlinks to Excel files in Tm1 web will open as websheets and NOT excel files (this is just how Tm1 web works, you could probably alter this by severely monkeying with the js functions if you really wanted to.)