Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

loading data from excel

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

Labels (2)
1 Solution

Accepted Solutions

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.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
Partner
Partner

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

Work smarter, not harder
Contributor III
Contributor III

Hi Climber,

 

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

how to do it in the script.

 

Regards,

raZor

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.

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post