Dynamic columns/variables for CSV import

[fleaster]

Hi all,
Just wondering if anyone had any tips on this - I have an import file where say Columns 1-10 are fixed, however some versions of the file may have an additional Columns 11-12 or 11-15 . Some options I have though of to address this are:

(1) have a different process for each file format - but would rather not do this, as is preferrable for TI to "autodetect" it

(2) I've tried mapping out the max number of variables (e.g. V12,V13,V14,V15 etc), then writing logic to detect if the additional columns are populated or not... however it seems to cause errors with the shorter files, where the additional variables become "undefined" (not sure how to get around this)

(3) Another option may be to change the file format to "Fixed Width", take each line as one big block of text, then loop through to find the commas/fields... but this feels a bit clunky to me

(4) ...anyone have any other ideas?

Thanks!

Matt

[Wim Gielis]

Hello

One other possibility might be the EXPAND function, though I'm not 100 % certain it will work correctly when the specified variable Vxx does not exist.
You might want to give it a try based on code from my most recent article on my website (balance sheet accounts dimension with variable hierarchies).

If it doesn't work, maybe creating several processes (1 per input format) and write to a temporary text file with correct fixed number of columns. Then have 1 process that can treat that one layput that imports the temporary file.

[dan.kelleher]

Hi Fleaster,

I have a process that loads data from csv into multiple cubes with varying dimensions - you can load data to different cubes within the same source file.

The source file fields are as follows:

Cube - string - name of the cube to be loaded
Type - string - either 'string' or 'numeric' depending on the row to be loaded
String - string - if Type = 'string', the string data to be loaded
Numeric - numeric - if Type = 'numeric', the numeric data to be loaded
Dimension_1 - string - element reference for dimension 1 in the cube
Dimension_2 - string - element reference for dimension 2 in the cube
...
Dimension_12 - string - element reference for dimension 12 in the cube

So if I was loading data to a reporting_cube with 5 dimensions, the source file row might be:

reporting_cube, numeric, , 100, AAA, BBB, CCC, DDD, EEE, , , , , , ,
reporting_cube, string, comment, , AAA, BBB, CCC, DDD, EEE, , , , , , ,

My data tab is something like the below:

Code: Select all

# Check if cube exists
# ================
If( CubeExists( vsCubeName) = 0);
	ItemReject( 'Cube: ' | vsCubeName | ' does not exist');
	ProcessError;
EndIf;

# Skip data that can't be written - this is to avoid throwing an error when updating the abbreviations for unmapped divisions.
# ========================================================================================================
vnCounter = 1;

vsElement = EXPAND( '%' | 'vsDimension_' | NumberToString(vnCounter) | '%');

While( vsElement @<> '');
	vsDim = TABDIM( vsCubeName, vnCounter);
	If( DIMIX( vsDim, vsElement) = 0);
#		TextOutput( 'debug_data.txt', vsElement | ' missing from ' | vsDim);
		ItemSkip;
	EndIf;
	vnCounter = vnCounter + 1;
	vsElement = EXPAND( '%' | 'vsDimension_' | NumberToString(vnCounter) | '%');
End;

# Populate numeric and string static data
# ===============================
If(vsType @= 'string');
	If(vsDimension_10 @<> '');
		CellPutS( vsString, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5, vsDimension_6, vsDimension_7, vsDimension_8, vsDimension_9, vsDimension_10); 
	ElseIf( vsDimension_9 @<> '');
		CellPutS( vsString, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5, vsDimension_6, vsDimension_7, vsDimension_8, vsDimension_9);
	ElseIf( vsDimension_8 @<> '');
		CellPutS( vsString, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5, vsDimension_6, vsDimension_7, vsDimension_8);
	ElseIf( vsDimension_7 @<> '');
		CellPutS( vsString, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5, vsDimension_6, vsDimension_7);
   	ElseIf(vsDimension_6 @<> '');
		CellPutS( vsString, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5, vsDimension_6);
	ElseIf(vsDimension_5 @<> '');
		CellPutS( vsString, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5);
	ElseIf(vsDimension_4 @<> '');
		CellPutS( vsString, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4); 
	ElseIf(vsDimension_3 @<> '');
		CellPutS( vsString, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3); 
	ElseIf(vsDimension_2 @<> '');
		CellPutS( vsString, vsCubeName, vsDimension_1, vsDimension_2); 
	Else;
		ItemReject('Cube: ' | vsCubeName | ' needs to have at least 2 vsDimensions defined');
		ProcessError;
	EndIf;
ElseIf(vsType @= 'numeric');
	If(vsDimension_10 @<> '');
		CellPutN( vnNumeric, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5, vsDimension_6, vsDimension_7, vsDimension_8, vsDimension_9, vsDimension_10); 
	ElseIf(vsDimension_9 @<> '');
		CellPutN( vnNumeric, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5, vsDimension_6, vsDimension_7, vsDimension_8, vsDimension_9);
	ElseIf(vsDimension_8 @<> '');
		CellPutN( vnNumeric, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5, vsDimension_6, vsDimension_7, vsDimension_8);
	ElseIf(vsDimension_7 @<> '');
		CellPutN( vnNumeric, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3,vsDimension_4,  vsDimension_5, vsDimension_6, vsDimension_7);
	ElseIf(vsDimension_6 @<> '');
		CellPutN( vnNumeric, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5, vsDimension_6);
	ElseIf(vsDimension_5 @<> '');
		CellPutN( vnNumeric, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4, vsDimension_5);
	ElseIf(vsDimension_4 @<> '');
		CellPutN( vnNumeric, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3, vsDimension_4); 
	ElseIf(vsDimension_3 @<> '');
		CellPutN( vnNumeric, vsCubeName, vsDimension_1, vsDimension_2, vsDimension_3); 
	ElseIf(vsDimension_2 @<> '');
		CellPutN( vnNumeric, vsCubeName, vsDimension_1, vsDimension_2); 
	Else;
		ItemReject('Cube: ' | vsCubeName | ' needs to have at least 2 vsDimensions defined');
		ProcessError;
	EndIf;
Else;
	ItemReject('Incorrect data type: ' | vsType); 
	ProcessError;
EndIf;

Thanks,

Dan

[Wim Gielis]

Thanks for the contribution Dan.
I see that you also use EXPAND to make it dynamic.

Doesn't give this formula an error within your loop, when EXPAND is used on a number that is too high (such that the corresponding variable does not exist anymore ?
I would set some kind of MAX value for the loop, to escape from that error.

[dan.kelleher]

Hi Wim,

The variables always exist, however where they are not needed, they are empty strings, which is why I use:

Code: Select all

If( vsDimension_X @<> '');

To determine how may dimensions the cube has.

Thanks,

Dan

[fleaster]

Hi all,
thanks for the responses - just to clarify: I have no issue with loading a csv file to multiple cubes of varying dimensionality - the issue is if the csv file itself has a varying number of columns.

So far, it seems like the EXPAND function is the only alternative - however, I'm a bit confused on how it would work in this context - does anyone have any sample code of this by any chance?

thanks

Matt

[Wim Gielis]

So far, it seems like the EXPAND function is the only alternative - however, I'm a bit confused on how it would work in this context - does anyone have any sample code of this by any chance?

Hi Matt

Didn't you see the code by dan.kelleher? Or the code on my website? Or other topics with the EXPAND function?

[dan.kelleher]

Yes, just tested my code and it works with a csv file with only 4 dimension columns, the other 'unused' variables (vsDimension_5 to vsDimension_12) are treated as empty strings at run-time.