loading data from excel

[raZor]

Hi All,

I want to know whether we have a solution to load data from excel if the name of the excel file is maintained on version wise eg.

data_V1.xlsx

data_V2.xlsx

version number will keeps on increasing every month when the data get extracted but file name will be like, data_V3.xlsx, data_V4.xlsx

Regards

[JordyWegman]

Hi Razor,

Yes that is possible, load the file and instead of data_V2.xlsx, create: data_V*.xlsx. This will get always the latest version.

The thing is, if someone creates two versions, it will get them both! So make sure that there is only one version.

If there are more versions, you should get something a bit more complicated that is counting the amount of files and checking the latest version.

Jordy

Climber

[raZor]

Hi Climber,

thanks a lot for the reply, can you give me an example?

how to do it in the script.

Regards,

raZor

[jonathandienst · Accepted solution]

Here is a script pattern to :

Let vPath = '... path to data files ...';
Let vMaxVer = 0;
Let vLoadFile = '';

For Each vFile in FileList(vPath & '\data_V*.xlsx')

	Let vFileVer = Alt(TextBetween(vFile, 'data_V', '.xlsx'), 0);
	
	If vFileVer > vMaxVer Then
		Let vMaxVer = vFileVer;
		Let vLoadFile = vFile;
	End If
Next

If Len(vLoadFile) > 0 Then

	Data:
	LOAD *,
		BaseFileName() as Source
	FROM $(vLoadFile)
	(ooxml, ... excel file settings ...);
	
End If

Set vPath = ;
Set vMaxVer = ;
Set vLoadFile = ;
Set vFile = ;

Replace the ... section with a valid file path and the Excel file load settings (like embedded labels, Sheet is...., etc).

This will get the highest version number providing they follow the vfile_Vxx pattern.