Eport to ASCII files

[nickykoller]

I have a Ti process which exports data to a text file.
Instead of one big file, I would like it to create several smaller files changing on the department code.
ie: one file per department
The data is NOT in the order of the department code.
Any ideas?

[jim wood]

If I was doing this I would order the data source by department. (Easily done if a view is the source). Then the rest is easy. Generate a variable for the file name based on the department and update the ascii out to use the variable as the file source. Job done.

[lotsaram]

Did you look at this very recent thread?
http://www.tm1forum.com/viewtopic.php?f=3&t=6744
It would seem to contain an answer for you.

[nickykoller]

So easy! Two minutes and it's working.
Interestingly enough I didn't have to order the source and its coming out correctly.
Thank you!

[nickykoller]

Although the files all export great, it looks like my headers aren't getting inserted.
If I export one big file, the headers are inserted, but not when I export several files.

This is the code(simplified) in the Data tab:
If (Headers = 0);
Headers = 1;
AsciiOutput(Dir | vmapdept | '.csv','Year','Month','Account','Account No','Account Description','Accpac Dept');
Endif;

AsciiOutput(Dir | vmapdept | '.csv',vyear,vmo,vAccID,vAccount,vaccdesc,vbranch);

[David Usherwood]

You need to write the header for each departmental output file - loop through a subset of dept and write the header to the relevant file.

[nickykoller]

Think I am missing something. Still not exporting with the headers.

I set my counters to 0 in the prolog and then have this in DATA:

#### loop through adding the headers to each file####
sDimName = 'departments';
nCounter = 1;
nMaximum = DimSiz(sDimName);
WHILE(nCounter <= nMaximum);

#### Set the headers in the output file ####
If (Headers = 0);
Headers = 1;
AsciiOutput(Dir | vmapdept | '.csv','Year','Month','Account No','Account Description','Accpac Dept','Mapped TM1 Dept','Source','Type','User','Batc
h No','Transaction Description','Amount');
Endif;
#### Set the headers in the output file ####


nCounter = nCounter + 1;
END;
#### loop through adding the headers to each file####



#### Exports out all the ASCII files ####
#To not include balance sheet accounts
#IF(VAccount @> '1000');
#AsciiOutput(Dir | vmapdept | '.csv',vyear,vmo,vAccount,vaccdesc,vbranch,vmapdept,vsource,vtype,vuser,vbatch,vdesc,vamt);
#ENDIF;
#### Exports out all the ASCII files ####

[David Usherwood]

You are looping round department, but using the department from the data source for the filename. Use the department you are looping round.

[lotsaram]

David beat me to it. But here is the complete answer.

Your approach is good EXCEPT that you are not changing the value of your vmapdept variable which I assume you are getting from the data source and will correspond to your "Mapped TM1 Dept" field in the output, and your logic would be better if the While loop was within the test for 1st record condition.

I would suggest that you declare SEPARATE variable for the department file name and set this on each pass through the loop, assuming you only have N level departments in the extract then the code should test for this also. Presently it looks like you are putting in headers but the header row is being repeated and being added only in the first file.

This should fix it:

Code: Select all

#### test for the first record of data source ####
If( Headers = 0 );
  Headers = 1;

#### loop through adding the headers to each file####
  sDimName = 'departments';
  nCounter = 1;
  nMaximum = DimSiz(sDimName);
  WHILE(nCounter <= nMaximum);
    sDept =   DimNm(sDimName, nCounter);

#### Set the headers in the output file ####
    If( DType(sDimName, sDept) @= 'N' );
      AsciiOutput(Dir | sDept | '.csv','Year','Month','Account No','Account Description','Accpac Dept','Mapped TM1 Dept','Source','Type','User','Batch No','Transaction Description','Amount');
    Endif;

#### increment the department counter ####
    nCounter = nCounter + 1;
  END;
EndIf;

[nickykoller]

Starting to feel like I'm not completly undertsanding. Clearly, because now the first file it exports is 100% and thereafter they just have headers.
Starting feel most blonde, but really appreciating the help!
I assume its now got something to do with where I put the actual data ascii output part???

#### test for the first record of data source ####
If( Headers = 0 );
Headers = 1;

#### loop through adding the headers to each file####
sDimName = 'departments';
nCounter = 1;
nMaximum = DimSiz(sDimName);
WHILE(nCounter <= nMaximum);
sDept = DimNm(sDimName, nCounter);

#### Set the headers in the output file ####
If( DType(sDimName, sDept) @= 'N' );
AsciiOutput(Dir | sDept | '.csv','Year','Month','Account No','Account Description','Accpac Dept','Mapped TM1 Dept','Source','Type','User','Batch No','Transaction Description','Amount');
Endif;

#### increment the department counter ####
nCounter = nCounter + 1;
END;
EndIf;

#### Exports out all the ASCII files ####
#To not include balance sheet accounts
IF(VAccount @> '1000');
AsciiOutput(Dir | vmapdept | '.csv',vyear,vmo,vAccount,vaccdesc,vbranch,vmapdept,vsource,vtype,vuser,vbatch,vdesc,vamt);
ENDIF;
#### Exports out all the ASCII files ####