topic Re: How to solve 'unknown file format' in store statement in QlikView

How to solve 'unknown file format' in store statement

Wed, 25 Nov 2020 16:16:04 GMT

Hello!

I try store a file but get the error:

Unknown file format specificationNo_Planned_Orders

STORE Latest INTO E:\QlikView\Converted\Recommendation_Report_(No_Planned_Orders)_752030820_Aug-07-16_183010.txt.qvd(qvd)

The problem is the parameters () in the path = (No_Planned_Orders).

The path is already set to a variable. So its not that causing the error!

Re: How to solve 'unknown file format' in store statement

Tue, 09 Aug 2016 16:10:13 GMT

Hey Niclas.

Add square brackets:

STORE Latest INTO ;

BR,

Kuba

Re: How to solve 'unknown file format' in store statement

Wed, 10 Aug 2016 06:49:29 GMT

Sorry but I get "table not found" error when using the square brackets.

I'm totally clueless how to solve this. Have try to Google and many different solution but nothing works.. Are there any others that have come up with a similar problem and solve that?

Re: How to solve 'unknown file format' in store statement

marcus_sommer — Wed, 10 Aug 2016 09:23:50 GMT

This meant that your specified table didn't exist - either there is a typo or these table was automatically concatenated with another previous loaded table. It would be helpful if you post here the relenat parts of your script.

- Marcus

Re: How to solve 'unknown file format' in store statement

Wed, 10 Aug 2016 15:31:27 GMT

Hey Niclas,

to be 100% sure I tested STORE statement I pasted and it was working fine (it was not without []). As Marcus said - please provide some sample code, so that we can replicate the problem.

BR,

Kuba

Re: How to solve 'unknown file format' in store statement

Wed, 10 Aug 2016 15:51:52 GMT

Purpose of the script is to convert .txt and .xls files into .qvd files. And doing so by looping true folders.

SET Verbatim=1;

rootfolder:

Load @1:n as path

FROM rootfolder.txt (ansi, fix, no labels, header is 0, record is line);

set vQVD = 'E:\QlikView\Converted\';

/*above path as base path where my year wise folder contains files */

SET vCounter = 0;

LET vFileType = '.txt';

LET vTable = '';

Sub readFiles(root)

if vCounter = 3 or vCounter = 8 or vCounter = 13 then

LET vFileType = '.xls';

END if

For each File in filelist (root & '\*$(vFileType)')

Call getFile(folder);

Latest:

LOAD [File Path],Max([File Time]) as [Max Time] Resident Files Group By [File Path];

Left Join(Latest)

LOAD [File Name] as [Max Time],[File Name] Resident Files;

DROP Table Files;

LET vName = '$(File)';

let vVal = SubField('$(vName)','\','8');

set vB = $(vVal);

LET vCombine=vQVD&vVal;

set vB = $(vCombine);

IF vCounter = 6 then

LET vCombine = 'E:\QlikView\Converted\Sanmina_MRP_Recommendation_Report';

END IF

if vCounter = 3 or vCounter = 8 or vCounter = 13 then

First 1 LOAD filename() as fileName

FROM [$(File)](biff, embedded labels, table is [$(vTable)]);

else

Concatenate

LOAD *, '$(vName)' as [Src File]

From [$(vName)] (utf8, delimiter is '/t', delimiter is '/', delimiter is 'spaces', delimiter is ',', header is 10);

END IF

STORE Latest INTO [$(vCombine).qvd](qvd);

DROP Table Latest;

let vName='';

LET vCounter = ($(vCounter) + 1);

if $(vCounter) = 13 then

EXIT Script;

End if

Call getRootFile

Next File;

End Sub

Sub getFile(folder)

For each File in filelist (root & '\*$(vFileType)')

Files:

LOAD '$(folder)' As [File Path], '$(file)' as [File Name] ,FileTime('$(file)') as [File Time] AutoGenerate(1) ;

if vCounter = 3 or vCounter = 8 or vCounter = 13 then

LET vFileType = '.txt';

END if

End Sub

Sub getRootFile

if $(vCounter) = 0 then

Call readFiles(fieldvalue('path', 1) & '\reports\OPS_Report');

ELSEIF $(vCounter) = 1 then

Call readFiles(fieldvalue('path', 1) & '\reports\MDS_Report');

ELSEIF $(vCounter) = 2 then

Call readFiles(fieldvalue('path', 1) & '\reports\Status_Report');

ELSEIF $(vCounter) = 3 then

LET vTable = 'Demand - Planned$';

Call readFiles(fieldvalue('path', 1) & '\reports\Planned_Report');

ELSEIF $(vCounter) = 4 then

Call readFiles(fieldvalue('path', 1) & '\reports\Data_Report');

ELSEIF $(vCounter) = 5 then

call readFiles(fieldvalue('path', 1) & '\reports\Order_Report');

ELSEIF $(vCounter) = 6 then

call readFiles(fieldvalue('path', 1) & '\reports\Recommendation_Report');

ELSEIF $(vCounter) = 7 then

call readFiles(fieldvalue('path', 1) & '\reports\Shipments_Report');

ELSEIF $(vCounter) = 8 then

LET vTable = '7BucketDetail$';

call readFiles(fieldvalue('path', 1) & '\reports\Holds_Report');

ELSEIF $(vCounter) = 9 then

call readFiles(fieldvalue('path', 1) & '\reports\Value_Report');

ELSEIF $(vCounter) = 10 then

call readFiles(fieldvalue('path', 1) & '\reports\Based_Report');

ELSEIF $(vCounter) = 11 then

call readFiles(fieldvalue('path', 1) & '\reports\Revenue_Report');

ELSEIF $(vCounter) = 12 then

call readFiles(fieldvalue('path', 1) & '\reports\All_Value_Report');

ELSEIF $(vCounter) = 13 then

LET vTable = 'APInvoices$';

call readFiles(fieldvalue('path', 1) & '\reports\AP_Report');

End if

End Sub

Call getRootFile

Re: How to solve 'unknown file format' in store statement

marcus_sommer — Wed, 10 Aug 2016 16:28:06 GMT

I suggest that you take a look within the logfile to see how often the loop will be executed - which meant to find the position where the script runs into an error and then to look which values the variables have. Also a run within the debugger might here very useful.

- Marcus

Re: How to solve 'unknown file format' in store statement

Wed, 10 Aug 2016 16:53:45 GMT

First, thank you all for your replies!

Second, I used the debugger and log file. They both pointing out that the file format is wrong in the file the script try to use.

I have used a ugly workaround to solve it. Feels like these "solutions" is the only solutions with QlikView. But they are not so fun to use if you want to make a good and stable script for use in production...

If anyone see this problem and get an great idea on hove to solve it better. Please write it here as a comment!

Re: How to solve 'unknown file format' in store statement

marcus_sommer — Thu, 11 Aug 2016 05:02:28 GMT

For me looked your script more complicated than it must be. I would try to avoid those counters and if-loops and query the fileextension within the filelist-loop, for example with: subfield(File, '.', -1) or with another loop through a list of various file-extensions, more like the example in the help by "for each ... next".

- Marcus

Re: How to solve 'unknown file format' in store statement

MarcoWedel — Thu, 11 Aug 2016 05:35:09 GMT

$(No_Planned_Orders)