TI procedure to upload data from flat files.

[bhinbest]

Hello All,

I need to create a process that can retrieve data from .txt files in a specific folder. I believe their is a way to get TM1 to automatically retreive the data from the latest file(s) in the folder on a nightly basis.

For example, every night a program is going to create a new .txt file with data (it may be possible that more than one file is created per night, in which case all of these new files need to be loaded) with a naming convention that has yet to be devised, but that I can create to facilitate this process. I need TM1 to access this 'newest' file(s) and upload the data, ignoring all other files that have already been uploaded.

Also, I may need to access these files through something called "FTP", which I'm not familiar with. Anyone know what this means and how to set-up a process to access a file using "FTP"?

Any thoughts, or points of clarification? Thanks in advance for any and all help!!

[Alan Kirk]

Hello All,

I need to create a process that can retrieve data from .txt files in a specific folder. I believe their is a way to get TM1 to automatically retreive the data from the latest file(s) in the folder on a nightly basis.

For example, every night a program is going to create a new .txt file with data (it may be possible that more than one file is created per night, in which case all of these new files need to be loaded) with a naming convention that has yet to be devised, but that I can create to facilitate this process. I need TM1 to access this 'newest' file(s) and upload the data, ignoring all other files that have already been uploaded.

Also, I may need to access these files through something called "FTP", which I'm not familiar with. Anyone know what this means and how to set-up a process to access a file using "FTP"?

Any thoughts, or points of clarification? Thanks in advance for any and all help!!

Holy heaven, you ain't asking much.

OK, to start with, I'd suggest that you use a datestamp to name your files. Your bigger problem is that you don't know how many files there will be, so I'd suggest making a numeric addition at the end of the datestamp for this purpose. That means that your filenames will come out in the form, say:

08-08-21_01.cma
08-08-21_02.cma

and so on. I'm going to assume that there will be no more than 9 of them each day; if there are, the string manipulations get a little more tricky but not by much. (I've left a leading 0 in case the numbers do eventually extend beyond 10; it will probably be easier to change the TI code than change the process which names the files. If it's more than 99 files, though... time to rethink your file generation method.)

With your files named by date you can use the Today() function to work out the file name. After that, you can use the FileExists() function to check for the files and to filter out any which relate to other days.

The next step is to define a TI process which will process the files. That will be called by your parent TI process using the ExecuteProcess function. If you're on a version which predates that function, you're screwed. Well, not really but it would be much easier if you ARE on a version which has that function.

In the second TI process, you need to define a string parameter defined to receive the file name. You do that on the Parameters tab of the Advanced tab, if you haven't done it before.

In short then, you have two processes:
(a) One which loops through the list of possible file names. For each file found, it calls;
(b) Another TI process which processes the actual file.

Your basic loop in the parent process (in the Prolog sub-tab of the Advanced tab) will therefore be something like this:

Code: Select all

# Path to the data files; this could be picked up from a control cube instead,
# but let's keep it simple.
SC_PATH = 'D:\TM1\Temp\';

s_Today = Today;

l_FileCounter = 1;

While ( l_FileCounter <= 9);

    s_FileName = s_Today | '_0' | Trim ( Str ( l_FileCounter, 1, 0) ) | '.cma';

    If ( FileExists ( SC_PATH | s_FileName ) = 1);
# Found a file by that name. This is where we do the main code.
# This is done by creating a process to process the file,
# creating a parameter to receive the file name,
# then changing the data source to that file name.
# In this example z080821B is the name of the other process.

        ExecuteProcess('z080821B', 'FileName', SC_PATH | s_FileName);

    Else;

#No more files found, exit the loop.

        l_FileCounter = 9;

    EndIf;

    l_FileCounter = l_FileCounter + 1;

End;

In the second process, you'll have something like this in the Prolog tab. (I've assumed that the Parameter has been defined using the name "FileName"):

Code: Select all

DatasourceNameForServer=FileName;

That will change the data source for the second TI process to the filename that you passed it.

This method enables you to use a single process to run through multiple data files without having to know their names in advance, or even how many of them there will be.

FTP stands for File Transfer Protocol. Just as a Windows server can run a www service to serve out HTML pages, it can also run an FTP service to serve out files. This is run out of IIS, which you'll come across if you ever inflict Web on yourselves.

To connect to an FTP server you typically need an FTP client. Most of them are freeware, but those aren't designed to do automated file pulls as you'll probably need.

The one I use is Core FTP; you may want to take a look at it. You'll probably need one of the industrial strength versions rather than the Lite one for what you have in mind though:

http://www.coreftp.com/

[Alan Kirk]

One other thing that I should have mentioned in the initial reply; I do suggest that you assign the return value from ExecuteProcess to a variable, like so:

l_RetVal = ExecuteProcess('z080821B', 'FileName', SC_PATH | s_FileName);

and check l_RetVal against the possible return values of the function. (See the Help topic for details of these.) If it doesn't equal ProcessExitNormal(), you may want to put a line of code in stopping the processing of the remaining files, though that will depend on what types of error(s) the files may conceivably generate.

The example above is of course merely a "point you in the right direction" primer, not a working example of code.

[bihints.com]

In short then, you have two processes:
(a) One which loops through the list of possible file names. For each file found, it calls;
(b) Another TI process which processes the actual file.

This method enables you to use a single process to run through multiple data files without having to know their names in advance, or even how many of them there will be.

Very nice piece of code.
But why not concatenate the files beforehand and load the resulting cma?
DOS:

Code: Select all

copy *.cma TheMotherOfAll.cma

or
Unix:

Code: Select all

cat *cma > TheMotherOfAll.cma

The one I use is Core FTP; you may want to take a look at it. You'll probably need one of the industrial strength versions rather than the Lite one for what you have in mind

lftp is free and the most fully featured command line ftp client http://lftp.yar.ru/
u might find a windows version floating around if u google it.

[Steve Vincent]

You ddin't say which operating system you are on, but you can automate FTP file retrieval using the built in FTP command prompt.

You need 2 files to do this, 1st one is a .BAT (batch file) which calls the program, the 2nd is a .TXT which holds the parameters to pass to the program.

GETFILES.BAT

Code: Select all

@ECHO OFF
ftp -s:»//servername/sharename/filepath/«getfiles_params.txt

GETFILES_PARAMS.TXT

Code: Select all

open »name of ftp server«
»user name«
»password«
get '»name of file to grab«' »file path and name of destination«
bye

the values in between the »« are what you need to enter. Depending on where the file is on the remote system you may also need to give it a full path and filename (ie. pub/exports/tm1file.txt).

Something you have to be *really* careful about tho is file paths. When a TI is run, its doing so using the same account as your TM1 service is running with. It's no good using local file paths (C:\windows) because it won't go where you want, you'll need to use the full UNC filepath and name (ie. \\servername\sharename\filepath).

[Alan Kirk]

In short then, you have two processes:
(a) One which loops through the list of possible file names. For each file found, it calls;
(b) Another TI process which processes the actual file.

This method enables you to use a single process to run through multiple data files without having to know their names in advance, or even how many of them there will be.

Very nice piece of code.

Wha' thenk-yew kind sah...

But why not concatenate the files beforehand and load the resulting cma?
DOS:

Code: Select all

copy *.cma TheMotherOfAll.cma

or
Unix:

Code: Select all

cat *cma > TheMotherOfAll.cma

Yes, that would be another option, I agree. However one problem in the OP's case is that he mentioned that he'd be leaving the processed files in the same folder. Doing a universal concatenation like that would merge the old files into the resulting one as well.

Admittedly that could be gotten around by shifting the processed files into a separate sub-folder after processing by using a batch file.

The other things that would worry me would be:
(a) The size of the files; would you end up with one humongous one;
(b) The possibility that one of the x number of files could be corrupt, in which case you'd lose the whole upload rather than just that one file;
(c) Related to that, processing them one at a time will probably help to more easily identify where any rejects or errors are sourced from;
(d) Whether the files had header row(s), though that could be something that could be taken care of in the original export process.

But yes, if the OP is confident about the source files, concatenation is definitely an option.

[Alan Kirk]

You ddin't say which operating system you are on, but you can automate FTP file retrieval using the built in FTP command prompt.

{Snip some good and useful hints}

Something you have to be *really* careful about tho is file paths. When a TI is run, its doing so using the same account as your TM1 service is running with. It's no good using local file paths (C:\windows) because it won't go where you want, you'll need to use the full UNC filepath and name (ie. \\servername\sharename\filepath).

Technically, you don't need to use UNC though it's a good practice to get into. What you DO have to do is ensure that any path you specify is as it's seen by the server, not your own PC. Thus D:\TM1\Temp is the relevant folder on the D:\ drive of the server, not your PC.

Also as of version 9.1 (and possibly earlier) you can't use mapped network drives. We have the folder D:\TM1 mapped as the U:\ drive on the server (the same drive letter as is generally used by clients). In 8.2.12 you can happily (for example) ASCIIOutput to U:\Temp. In 9.1, that won't work; you have to use either the UNC reference, or the local (D:\ drive) path.

[wudznet]

Hi,

I am just new here. the code really works. One question, what if the file doesn't exists yet can you create a loop and then once the file found it will process. This is kinda a trigger to process.

Thanking you

Wudz

[Alan Kirk]

I am just new here. the code really works. One question, what if the file doesn't exists yet can you create a loop and then once the file found it will process. This is kinda a trigger to process.

Yes and no.

By default if:
- You hit the end of the Prolog tab, and
- The data source doesn't exist, and
- You haven't changed the data source to one which does exist via the DatasourceNameForServer TI variable...
then the process will exit with an error.

In theory it would be possible for you to put a loop into your Prolog tab which calls the FileExists() function until it finds the file... but that would lock up your server until the file arrives which may not excessively impress the other users.

The workarounds are to either:
- Put a chore on a relatively short cycle; maybe 5, 10 minutes, depending on when / how often you expect the file to arrive. Have the first process in the chore do a FileExists() test; if the file is there then continue, otherwise quite the chore. Be aware that this will still have some performance impact; whether it's a problematic one can only be determined by experience.
- Write an external application which can scan a folder for files, and use an API call to trigger the process or chore when the file turns up. You don't want to do that unless you have no alternative.

[lotsaram]

However one problem in the OP's case is that he mentioned that he'd be leaving the processed files in the same folder. ..

Alan I don't know if I read that the same way ...

... every night a program is going to create a new .txt file with data (it may be possible that more than one file is created per night, in which case all of these new files need to be loaded) with a naming convention that has yet to be devised, but that I can create to facilitate this process. I need TM1 to access this 'newest' file(s) and upload the data, ignoring all other files that have already been uploaded.

IMO it's more likely the OP hasn't fully thought everything through just yet and/or is open to suggestion as to ways of managing to "load only the new files and ignore the old ones."

If the number of new files is > 1 but unknown my suggestion would be to use a while loop and WildcardFileSearch to go through the source directory finding all files where the root of the file name matches whatever the naming convention. Then feed in the matches as a datasource to another process with an ExecutePorcess. (or as per BIHints suggestion use a batch file to concatenate all source files together and then just process once, I probably wouldn't bother doing this though, just a preference.) In the epilog of the process which has its data source set at the "found file" I would use ExecuteCommand to call a batch file to move the processed file to another directory, that way you can be sure everything is processed only once. If you wanted to start getting cute you could have a variable destination folder for the processed files from monitoring the process return code (for files uploaded with no errors, minor errors, major errors, etc.)

[Alan Kirk]

However one problem in the OP's case is that he mentioned that he'd be leaving the processed files in the same folder. ..

Alan I don't know if I read that the same way ...

... every night a program is going to create a new .txt file with data (it may be possible that more than one file is created per night, in which case all of these new files need to be loaded) with a naming convention that has yet to be devised, but that I can create to facilitate this process. I need TM1 to access this 'newest' file(s) and upload the data, ignoring all other files that have already been uploaded.

IMO it's more likely the OP hasn't fully thought everything through just yet and/or is open to suggestion as to ways of managing to "load only the new files and ignore the old ones."

Considering that the original post was the better part of 2 years ago, it's probably safe to assume that the original poster has worked through the issue by now.

[lotsaram]

Sheesh, I would have to say I hope you're right!

I only noticed the dates in the last couple of posts. The new search feature must be working if a new poster was able to dredge up something that old!! (and that was relevant!)