HELP: ASCIIOUTPUT issues

[harrytm1]

Hi All,

I ran into some problems while trying to use TI to Ascioutput data from a cube. Here’s the details:

• The cube has 5 dimensions, including 1 measure dim.
• The measure dim has 10 elements, a mixture of S and N.
• Out of the 10 elements, I need to export data from the 5 elements, also a mix of S and N.
• The condition to include in the export is a Status measure element. If Status is “sending”, then include in file, else itemskip.
• This is how I want each line of the text file to look like, across columns:
o Column A: Dim A
o Column B: Dim C
o Column C: Measure Element 1
o Column D: Measure Element 2
o Column E: Measure Element 3
o Column F: Measure Element 4
o Column G: Measure Element 5
• I wrote the Asciioutput as follows:
o ASCIIOUTPUT(filename, dim A, dim C, vValue) where vValue is a variable that points to the data of the measures.
• Looking at the code, I know something is wrong. True enough, the export file ends up having Dim A and Dim C repeated over 5 rows, the 3rd column of each row contains the data of each of the 5 measure elements. This has to do with how TI reads the codes (i place this code in Metadata tab) as it loops from 1 measure element to another, thus treating each as a row entry.
• I tried to write the code this way but TI prevents me from saving, prompting a string error:
o ASCIIOUTPUT(filename, dim A, dim C, elt1, elt2, elt3, elt4, elt5) where each “elt” is a CellGetS or CellGetN of the actual measure element that corresponds to the dimensions.
• What should I do to achieve the desired result?
• Also, I notice if I open the export file using Notepad, each cell is encased in double quotes.
• In addition, numeric elements will end with a “.”, e.g. “550.”.
• If the receiving system insists on removing the double quotes and “.”, how should I do it?

Many thanks!

Regards,
Harry

[Wim Gielis]

Hello Harry

For creating the correct output, loop ONLY through the Status measure element, as far as the measures dimension is concerned.

From there on, you can piece together the string containing what you would like to output (the 5 elements).

Use CellGetN's and CellGetS's to get to the other elements.

The Roundp function could also be useful.

Also note that Turbo Integrator contains functions to format the output in the text file. Consider the DatasourceASCIIQuoteCharacter function as well as other DatasourceASCII[xxx] functions.

Wim

[Misty]

Hi Harry,

Did you get this to work? If so, could you please post the code? I'm trying to do the same thing, except simpler, because I just want to export 1 dimension, and all the measures associated. (It's a very simple cube that shouldn't really be a cube!).

Wim, can you possibly help out?

Thanks

[Alan Kirk]

Did you get this to work? If so, could you please post the code? I'm trying to do the same thing, except simpler, because I just want to export 1 dimension, and all the measures associated. (It's a very simple cube that shouldn't really be a cube!).

It would have been useful to know the exact structure of the cube; number of dimensions and data type for a start.

Working off the assumption that you have:
* One dimension (the one that you describe as wanting to "export"),
* A second dimension which contains your measures (which you want output into a single row of data), and
* Perhaps an unknown number of other dimensions each of which has a "Total" element,

you could approach this as follows (Prolog tab):
- Use the DimSiz function to get the size of the dimension that you want to export.
- Use the same function to get the size of your measures dimension.
- Use a While loop to iterate through the elements of the dimension that you want to export. Use the DimNm function to get the name of the element.
- Inside that While loop, use a second loop to iterate through the members of the measures dimension. Again, use DimNm to get the element name.
- For each element of that dimension, use a CellGetN or CellGetS function (as the case may be) to retrieve the value from the cube for the current element of the export dimension, the current element of the measures dimension, "Total" elements for all other dimensions. Convert that to a string using Trim() and Str(), and append it to a string variable.
- End the loop that iterates through the measures dimension.
- AsciiOutput the string variable.
- End the loop that iterates through the dimension that you're exporting.

If this isn't what you're after, some further clarification would be useful.

Edit: Corrected the function name; should be DinmNm not DimIx.

[Misty]

Hi Alan,

Thanks so much for the information. I'm very new to TM1 and don't have any experts around me.

I only have one Dimension, and it's only at leaf level. (It's a really silly, totally unsuitable way of collecting relational data from business users through Perspectives - don't ask!)

So my cube is as simple as this: 1 normal dimension with 1 measures dimension with 5 members. I've got no 'Total' element. I simply want to create a flat file structure so that I can export into an Oracle table.

One thing I've noticed from the outset is that in Turbo Integrator, shouldn't there just be 2 variables (the normal dimension and the measures dimension)? I've got something called 'Value' which is really confusing me. Actually the whole 'Variables' tab in TI is a bit confusing but I will consult the user guide. Any tips though?

I'm not much of a programmer, but I'm getting some help from someone who is (although he's never seen TM1 before) - can you think of any other info he might require?

Thanks again.

[Alan Kirk]

Thanks so much for the information. I'm very new to TM1 and don't have any experts around me.

I only have one Dimension, and it's only at leaf level. (It's a really silly, totally unsuitable way of collecting relational data from business users through Perspectives - don't ask!)

So my cube is as simple as this: 1 normal dimension with 1 measures dimension with 5 members. I've got no 'Total' element. I simply want to create a flat file structure so that I can export into an Oracle table.

If it's a two dimensional cube it's exactly the same; you just don't worry about any other dimensions because there aren't any. I did just notice that I typed DimIx in that last post rather than DimNm; the disadvantage of rushing a reply.

One cube that's simiilar to the one that you describe is the }ClientProperties cube. The general principles for exporting the content of that should be easily translatable to your own situation:

Code: Select all

# The following cube consists of two dimensions. 
# The }Clients one is the one that we're exporting.
# The }ClientProperties one is effectively the "measures"
# one though in reality this is a meaningless concept in TM1.

# Just to add some spice to the mix, }ClientProperties consists
# of a mixture of string and numeric values so that we can see 
# how to do both.

# We'll export the file as a tab delimited text file.

SC_CUBE = '}ClientProperties';

# Make sure that the rows of output aren't surrounded
# by double quotes.
DatasourceASCIIQuoteCharacter = '';

# How long are the two dimensions?
l_DimSizClients = DimSiz ( '}Clients');
l_DimSizClientProperties = DimSiz ( '}ClientProperties');

# Initialise the counters.
l_ClientIdx = 1;
l_PropertyIdx = 1;

# Now we loop through the clients

While ( l_ClientIdx <= l_DimSizClients );

#Get the name of the client element.

    s_Client = DimNm ( '}Clients', l_ClientIdx );

# This is the variable that we'll use for output.
# We reinitialise it for every row by putting in the name
# of the client.

    s_ExportRow = s_Client;

# Now iterate through the "measures".
# You have to reinitialise the counter with each loop.
    l_PropertyIdx = 1;

    While ( l_PropertyIdx <=  l_DimSizClientProperties );

# Get the name of the "measure"
        s_Property = DimNm ( '}ClientProperties', l_PropertyIdx );

# What type of element is that?
        s_ElType = DType( '}ClientProperties', s_Property );
        
        If ( s_ElType @= 'N' );

# Get the value and convert it to a string.
# The next two could be done as a single step; this is just for clarity.

            l_Value = CellGetN( SC_CUBE, s_Client, s_Property );
            s_Value = Trim ( Str ( l_Value, 15, 0 ) );

# Get the string value, no conversion required..
        ElseIf (s_ElType @= 'S' );

            s_Value = CellGetS( SC_CUBE, s_Client, s_Property );

        EndIf;

# Append the measure that we've just read to the output string..
# Char(9) is a horizontal tab character to create a tab delimited string.

        s_ExportRow = s_ExportRow | Char(9) | s_Value;

        l_PropertyIdx = l_PropertyIdx + 1;

    End;

#Write the full output string to the file.
    AsciiOutput ( 'D:\TM1\Temp\Output.txt', s_ExportRow);

    l_ClientIdx = l_ClientIdx+1;

End;

One thing I've noticed from the outset is that in Turbo Integrator, shouldn't there just be 2 variables (the normal dimension and the measures dimension)? I've got something called 'Value' which is really confusing me. Actually the whole 'Variables' tab in TI is a bit confusing but I will consult the user guide. Any tips though?

A value in a TM1 cube is stored at the intersection of one element from every dimension. A two dimensional cube is actually very easy to visualise; just think of an Excel worksheet.

A single value (say, 500) might be stored in say, cell D10. To get to that value, you need to provide two coordinates, the row (10) and the column (D). Those are like the two variables that you're describing. However they're merely the coordinates, they aren't the value. Therefore you need a third variable to tell you what the actual value is.

To get all of the information from that excel sheet you'd therefore have three variables, not two:
Column = D
Row = 10
Value = 500

Variables only come into play if you need to define (say) a cube view as a data source. In the instance above, however, you don't need to have a data source and go to the trouble of defining a view and destroying it afterwards, though you could if you really wanted to; it's just as easy to simply loop through the two dimensions.

If you have a multi-dimensional cube where you need to use zero suppression, that's a different matter.

[Misty]

Thanks again so much for your help!

For the benefit of the forum, here is the code I used. (I chose 'none' as the data source, and just had the following code in the Prolog tab)(I've changed a couple of things to make it anonymous but hopefully it will make sense still).

I know it's a hack, but the 'TM1 Dummy Row' is a dummy dimension allowing me to have all other data as elements in one 'data' dimension. This is how I've managed to defy the sensible use of TM1 to allow my users to input relational data into an OLAP tool!

I also wanted to convert the Business Effective From Date to a date (in case they put the wrong format into the cell). Also, if 'Department' or 'Business Effective From Date' were empty for any of the rows of data, I did not want to export those rows.

#****Begin: Generated Statements***
#****End: Generated Statements****

DatasourceASCIIQuoteCharacter = '';

s_TABLE_NAME = 'HIERARCHY';
SC_CUBE = 'Hierarchy';


ODBCOpen('database','schema','password');
ODBCOutput('database','delete from '|s_TABLE_NAME);
DUMMY_DIMENSION = 'Hierarchy_Dummy_Row';

l_NumRows = DimSiz (DUMMY_DIMENSION);
l_RowNum = 1;
While ( l_RowNum <= l_NumRows);

s_RowNumName = DimNm (DUMMY_DIMENSION,l_RowNum);

s_SQL = 'INSERT INTO '|s_TABLE_NAME|' (';
s_SQL = s_SQL | 'TM1_DUMMY_ROW, ';
s_SQL = s_SQL | 'DEPARTMENT, ';
s_SQL = s_SQL | 'DIVISION_SECTOR, ';
s_SQL = s_SQL | 'DIVISION, ';
s_SQL = s_SQL | 'BUSINESS_GROUP, ';
s_SQL = s_SQL | 'BUSINESS_EFFECTIVE_FROM_DATE, ';
s_SQL = s_SQL | 'DELETE_FLAG, ';
s_SQL = s_SQL | 'BATCH_ID, ';
s_SQL = s_SQL | 'SOURCE_SYSTEM_ID, ';
s_SQL = s_SQL | 'SOURCE_CREATE_DATETIME';
s_SQL = s_SQL | ')';

s_DEPARTMENT = CellGetS(SC_CUBE,s_RowNumName,'Department');
s_DIVISION_SECTOR = CellGetS(SC_CUBE,s_RowNumName,'Division_Sector');
s_DIVISION = CellGetS(SC_CUBE,s_RowNumName,'Division');
s_BUSINESS_GROUP = CellGetS(SC_CUBE,s_RowNumName,'Business Group');
s_BUSINESS_EFFECTIVE_FROM_DATE = CellGetS(SC_CUBE,s_RowNumName,'Effective From Date');
s_DELETE_FLAG = CellGetS(SC_CUBE,s_RowNumName,'Delete Flag (Y/N)');

s_SQL = s_SQL | ' VALUES (';
s_SQL = s_SQL | ''''| s_RowNumName |''',';
s_SQL = s_SQL | ''''| s_DEPARTMENT |''',';
s_SQL = s_SQL | ''''| s_DIVISION_SECTOR |''',';
s_SQL = s_SQL | ''''| s_DIVISION |''',';
s_SQL = s_SQL | ''''| s_BUSINESS_GROUP |''',';
s_SQL = s_SQL | 'to_date('''| s_BUSINESS_EFFECTIVE_FROM_DATE |''',''dd/mm/yyyy hh24:mi:ss''),';
s_SQL = s_SQL | ''''| s_DELETE_FLAG |''',';
s_SQL = s_SQL | 'NULL,';
s_SQL = s_SQL | '''27'',';
s_SQL = s_SQL | 'sysdate';
s_SQL = s_SQL | ')';

If ((s_DEPARTMENT @= '') % (s_BUSINESS_EFFECTIVE_FROM_DATE @= ''));

Else;
ODBCOutput('database',s_SQL);
Endif;

l_RowNum = l_RowNum + 1;

End;

[dfrench77]

Hello,

I read through this post and it has been very helpful to me in developing and modifying my ODBCOUTPUT to a SQL Table. I have leverage both Alan and Misty's code to develop my code. However, I am still having trouble with writing all the cube data to the SQL table. My source cube currently has two entries that I expect would produce two unique records in the SQL table. I am receiving an error from from Turbo Integrator

Error: Prolog procedure line (60): Invalid key: Dimension Name: "SNO_Model", Element Name (Key): ""

. Below is the code that I am using. Any help or advice will be greatly appreciated.

Desi.

Code: Select all

[b]Prolog tab[/b]

#****Begin: Generated Statements***
#****End: Generated Statements****


# Make sure that the rows of output aren't surrounded
# by double quotes.
DatasourceASCIIQuoteCharacter = '';

s_TABLE_NAME = ' [SNO].[dbo].[SafetyStocks] ';
SC_CUBE = ' SNO_SafetyStock1';

ODBCOpen('ODS', 'tm1svc', 'Tm!$vc');

## Truncate data on the SNO Data Model table (Storage)

vStatement1 = 'Truncate Table  [SNO].[dbo].[Storage] ';

ODBCOutput('ODS',vStatement1);

vsCreateTime=TODAY(1);
Model = ''| SNO_Model|'' ;
UserID = ''| vsUserID|'';
RowID = ''| SNO_RowID|'';

# How long are the dimensions?
#l_DimSizSNO_Model = DimSiz ( 'SNO_Model');
#I_DimSizClient = DimSiz( '}Clients');
I_DimSizSNO_RowID = DimSiz( 'SNO_RowID');
#l_DimSizSNO_SS_Measures = DimSiz ( 'SNO_SafetyStock_Measures');

# Initialise the counters.
#l_SNO_Model_Idx = 1;
#I_SNO_Clients_Idx = 1;
I_SNO_RowID_Idx = 1;
#l_SNO_SS_Measures_Idx = 1;

# Loop through the SNO_RowID

While ( I_SNO_RowID_Idx <= I_DimSizSNO_RowID );

# Get the name of the SNO_RowID element.

    s_SNO_RowID = DimNm ( 'SNO_RowID', I_SNO_RowID_Idx );

# INSERT INTO [SNO].[dbo].[SafetySyock] ( [Model], [ItemCode], [Location], [SSType], [Safety], [UserID], [CreateDate] )

s_SQL =  'INSERT INTO '|s_TABLE_NAME|' (';
s_SQL = s_SQL | '[Model],';
s_SQL = s_SQL | '[ItemCode],';
s_SQL = s_SQL | '[Location],';
s_SQL = s_SQL | '[SSType],';
s_SQL = s_SQL | '[Safety],';
s_SQL = s_SQL | '[UserID],';
s_SQL = s_SQL | '[CreateDate]';
s_SQL = s_SQL | ')';

s_ItemCode = CellGetS(SC_CUBE,Model, UserID, RowID, 'Item');
s_Location = CellGetS(SC_CUBE,Model, UserID, RowID,'Location');
s_SSType = CellGetS(SC_CUBE,Model, UserID, RowID,'SSType');
n_Safety = CellGetN(SC_CUBE,Model, UserID, RowID,'Safety');
s_Safety = Trim ( Str ( n_Safety, 15, 0 ) );
s_CreateTime = vsCreateTime;

#VALUES ( ''%vsModel%'', ''%ItemCode%'',''%Location%'', ''%SSType%'',%Safety%, ''%UserID%'', ''%CreateDate%'' )' ) ;

s_SQL = s_SQL | ' VALUES (';
s_SQL = s_SQL |''''|Model | ''',';
s_SQL = s_SQL |''''|s_ItemCode | ''',';
s_SQL = s_SQL |''''|s_Location | ''',';

s_SQL = s_SQL |''''|s_SSType | ''',';
s_SQL = s_SQL |''|s_Safety |',';
s_SQL = s_SQL |''''|UserID | ''',';
s_SQL = s_SQL |''''|s_CreateTime |''' ';
s_SQL = s_SQL | ')';

If  ( Model @= '')  ;

Else;
ODBCOutput('ODS',s_SQL);

Endif;

I_SNO_RowID_Idx = I_SNO_RowID_Idx + 1;

End;

[b]Epilog tab[/b]


#****Begin: Generated Statements***
#****End: Generated Statements****

asciioutput('temp.txt', s_SQL);

ODBCCLOSE('ODS');

[Duncan P]

Hi Desi,

I am confused at the use of both s_SNO_RowID and RowID. Is this deliberate and if so what is the difference?

Cheers,
Duncan.

[dfrench77]

I am confused at the use of both s_SNO_RowID and RowID. Is this deliberate and if so what is the difference?

Duncan,

There is not difference. The s_SNO_RowID is leftover code from following Misty's code that i carried forward. I can replace s_SNO_RowID with RowID.

Thanks,

Desi

[tomok]

The error message is directing you to line 60 of the Prolog tab. Have you bothered to look at the code on line 60 and see why you are passing an empty parameter for the SNO_Model dimension?

[dfrench77]

Tomok,

The error message is directing you to line 60 of the Prolog tab. Have you bothered to look at the code on line 60 and see why you are passing an empty parameter for the SNO_Model dimension?

I did look at the referenced code in the prolog tab. See below. Why is the Cellgets statement returning a null?

LINE 60 -

Code: Select all

s_PenaltyCostType = CellGetS(SC_CUBE,Model, UserID, RowID, 'Cost Type');
s_ModelSelection = CellGetS(SC_CUBE,Model, UserID, RowID,'Model Selection');
s_NodeType = CellGetS(SC_CUBE,Model, UserID, RowID,'Node Type');
n_PenaltyCostValue = CellGetN(SC_CUBE,Model, UserID, RowID,'Penalty Cost');
s_PenaltyCostValue= Trim ( Str ( n_PenaltyCostValue, 15, 0 ) );
s_CreateTime = vsCreateTime;

[dfrench77]

Tomok,

Thanks for directing my attention to the CellGetS.

I changed the code to include following and it is now working. However, I do want to ensure that this is the best way of doing what I am doing. Any advise or guidance will be greatly appreciated.

Regards,

Desi

Code: Select all

# How long are the dimensions?
l_DimSizSNO_Model = DimSiz ( 'SNO_Model');
I_DimSizClient = DimSiz( '}Clients');
I_DimSizSNO_RowID = DimSiz( 'SNO_RowID');
#l_DimSizSNO_SS_Measures = DimSiz ( 'SNO_SafetyStock_Measures');

# Initialise the counters.
l_SNO_Model_Idx = 1;
I_SNO_Clients_Idx = 1;
I_SNO_RowID_Idx = 1;
#l_SNO_SS_Measures_Idx = 1;

# Loop through the SNO_RowID

While ( I_SNO_RowID_Idx <= I_DimSizSNO_RowID );

# Get the name of the SNO_RowID element.

    RowID = DimNm ( 'SNO_RowID', I_SNO_RowID_Idx );
    Model = DimNm ( 'SNO_Model', l_SNO_Model_Idx );
    UserID = DimNm ( '}Clients', I_SNO_Clients_Idx );

[lotsaram]

Desi / you're not making it easy to help. Darned difficult actually.

Your problem stems from what I assume to be line 22 when you assign the value to the string variable "Model"
Model = ''| SNO_Model|'' ;
incidentally quite bizarrely as SNO_Model wrapped in 2 empty strings (why not simply Model = SNO_Model; ?)
I would assume from the error that the value for SNO_Model is empty and therefore Model has the same fate.

So your issue must start back in the parameters as since this is the start of the prolog I assume that SNO_Model must be a string parameter.

also I assume that since Model is empty this would not only be causing all the CellGets to fail but also I presume the preceding SQL inserts would also fail due to Model not being specified ? ...

------
The above posted before seeing your last post. the reason your code is now working is simple. The variable for Model is now being set with a value!

[dfrench77]

Hello All,

I need help with modifying my code below so that it exports all the UserIDs with data entered to SQL Server table. I have several UserIDs with data entered in the cube, so when I perform an Export data as Text on the cube I see all the data. The requirement is that for any user that executes the export to SQL server process will get all the records for every UserID with data entered in the cube. As always, any help, guidance, or assistance is greatly appreciated.

Thank you,

Desi

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****


# Make sure that the rows of output aren't surrounded
# by double quotes.
DatasourceASCIIQuoteCharacter = '';

s_TABLE_NAME = ' [SNO].[dbo].[BOM] ';
SC_CUBE = ' SNO_BOM1';

ODBCOpen('ODS', 'tm1svc', 'Tm!$vc');

# Truncate data on the SNO Data Model table (BOM)
vStatement1 = 'Truncate Table  [SNO].[dbo].[BOM]  ';

ODBCOutput('ODS',vStatement1);

vsCreateTime=TODAY(1);
Model = ''| SNO_Model|'' ;
UserID=TM1USER();
RowID = ''| SNO_RowID|'';

# How long are the dimensions?
l_DimSizSNO_Model = DimSiz ( 'SNO_Model');
I_DimSizClient = DimSiz( '}Clients');
I_DimSizSNO_RowID = DimSiz( 'SNO_RowID');


# Initialise the counters.
l_SNO_Model_Idx = 1;
I_SNO_Clients_Idx = 1;
I_SNO_RowID_Idx = 1;


# Loop through the SNO_RowID

While ( I_SNO_RowID_Idx <= I_DimSizSNO_RowID );


# Get the name of the Dimesnions element.

    RowID = DimNm ( 'SNO_RowID', I_SNO_RowID_Idx );
    Model = DimNm ( 'SNO_Model', l_SNO_Model_Idx );
    UserID = DimNm ( '}Clients', I_SNO_Clients_Idx );


# INSERT INTO [SNO].[dbo].[BOM] ( [Model], [LocationName], [Process], [Step], [InputOutput], [ItemCode], [Quantity], [UserID], [CreateDate] )

s_SQL =  'INSERT INTO '|s_TABLE_NAME|' (';
s_SQL = s_SQL | '[Model],';
s_SQL = s_SQL | '[LocationName],';
s_SQL = s_SQL | '[Process],';
s_SQL = s_SQL | '[Step],';
s_SQL = s_SQL | '[InputOutput],';
s_SQL = s_SQL | '[ItemCode],';
s_SQL = s_SQL | '[Quantity],';
s_SQL = s_SQL | '[UserID],';
s_SQL = s_SQL | '[CreateDate]';
s_SQL = s_SQL | ')';

s_ItemCode = CellGetS(SC_CUBE,Model, UserID,  RowID, 'Item');
s_LocationName = CellGetS(SC_CUBE,Model, UserID, RowID,'Location');
s_Process = CellGetS(SC_CUBE,Model, UserID, RowID,'Process');
s_Step = CellGetS(SC_CUBE,Model, UserID, RowID,'Step');
s_InputOutput = CellGetS(SC_CUBE,Model, UserID, RowID,'InputOutput');
n_Quantity = CellGetN(SC_CUBE,Model, UserID, RowID,'Quantity');
s_Quantity = Trim ( Str ( n_Quantity, 15, 0 ) );
s_CreateTime = vsCreateTime;

#VALUES ( ''%vsModel%'', ''%ItemCode%'',''%Location%'', ''%TransType%'',%Quantity%, ''%UserID%'', ''%CreateDate%'' )' ) ;

s_SQL = s_SQL | ' VALUES (';
s_SQL = s_SQL |''''|Model | ''',';
s_SQL = s_SQL |''''|s_LocationName | ''',';
s_SQL = s_SQL |''''|s_Process | ''',';
s_SQL = s_SQL |''''|s_Step | ''',';
s_SQL = s_SQL |''''|s_InputOutput | ''',';
s_SQL = s_SQL |''''|s_ItemCode | ''',';
s_SQL = s_SQL |''|s_Quantity |',';
s_SQL = s_SQL |''''|UserID | ''',';
s_SQL = s_SQL |''''|s_CreateTime |''' ';
s_SQL = s_SQL | ')';

If ( ( s_ItemCode @= '') % (Model @= '') % (s_ItemCode @='') % (s_LocationName @= '') % (s_Process @= '') % (s_Step @= '') % (s_InputOutput @= '')  );

Else;
ODBCOutput('ODS',s_SQL);

Endif;

I_SNO_RowID_Idx = I_SNO_RowID_Idx + 1;


End;

[dfrench77]

Hello,

Any ideas why I am not getting all the data from the cube when executing this process?

Thanks,

Desi

[dwood09]

thnaks heaps for this thread. It has given me an answer to a long standing issue that I've had with the leading and trailing double quotes in the asciioutput funciton!

BTW, I also use ascii characters for tab delimiters and single quote characters as well.

EG if I wanted to output the following line:

field1 <tab> field2's <tab> field3

I would code:

Code: Select all

DatasourceASCIIQuoteCharacter='';
asciioutput ( 'c:\file.txt' , 'field1' | char(9) | 'field2' | char(39) | 's' | char(9) | 'field3' ) ;

thanks again!
Darren

[Wim Gielis]

Hello Darren

If you use a variable, the code will be more readable:

Code: Select all

DatasourceASCIIQuoteCharacter='';
vTab=Char(9);
vDoubleQuote=Char(39);
asciioutput ( 'c:\file.txt' , field1 | vTab | field2 | vDoubleQuote | 's' | vTab | field3 );

assuming your fields are not numeric.

[jcortes]

Hi guys, i think, i can use the same aproach for: create multiple csv files as dimension elements, I mean, I have a cost center dimension into a cube, so I need to export data cube as scv files, one for each cost center. How can I achieve it?. Thanks in advanced.

[Wim Gielis]

The filename could be dynamic and contain the cost center:
asciioutput ( 'c:\data_' | vCC | '.txt', ... );