Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Been trying to find for ways to export some data to a txt file using double pipe '||' but it appears that the following statement only takes the first character and export with single pipe. Is there any way more than one characters can be used as delimiter?
STORE COMPANY INTO 'COMPANY.txt' (txt, delimiter is '||');
Thanks,
John
can you provide a sample app
Hi,
This is the script.
Thanks,
John
COMPANY:
load
[Essbase Member Name]
, Description
, [CO_To Currency Code]
, [CO_Default Country Num]
, '' AS [SET_OF_BOOKS_ID]
, [CO_Level 1]
, '' AS [FRINGE_ENTITY]
, [CO_Allocation Flag]
, [CO_Enabled Flag]
, [CO_Active Flag]
, '' AS [COMPANY]
, [CO_Expense Reports]
, '' AS [LEGACY_SET_OF_BOOKS_ID]
, [CO_New Num]
, [CO_Ledger ID]
, '' AS [STATUTORY_FLAG]
, '' AS [COMPANY_ATTRIBUTE1]
, '' AS [COMPANY_ATTRIBUTE2]
, '' AS [COMPANY_ATTRIBUTE3]
, '' AS [COMPANY_ATTRIBUTE4]
, '' AS [COMPANY_ATTRIBUTE5]
from COMPANY.qvd (qvd);
STORE COMPANY INTO 'COMPANY_double_pipe.txt' (txt, delimiter is '||');
Hello John,
From the QlikView online help:
This specifier is relevant only for delimited .txt files.
delimiter is char
For delimited table files, an arbitrary delimiter (from the 127 ASCII characters) can be specified through the delimiter is specifier, char specifies a single character.
There are some special cases:
"\t" representing a tab sign, with or without quotation marks.
"\\" representing a backslash ( \ ) character.
the word "spaces" representing all combinations of one or more spaces. Non-printable characters with an ASCII-value below 32, with the exception of CR and LF, will be interpreted as spaces.
If nothing is specified, delimiter is ',' is assumed.
So, in other words, if you define your delimiter as a two-character string, only the first shall be considered.
Maybe you could consider exporting your data with another type of delimiter, such as the tilde ~ character, if the pipe character is used within certain of your fields?
Hope this helps, regards
Philippe
John
I am pretty sure the delimiter can only be one character and if more the one it just uses the first & ignores the rest.
You could do a frig and build a single string for yourself, like this :
BB :
load
'Field One' & '||' & 'Field Two' as Text
Autogenerate 6
;
Store BB into BB.txt (txt , delimiter is '' ) ;
It does enclose the whole thing in double quotes though, which is probably not ideal. I'd be interested if anyone knows how to get rid of them.
Best Regards, Bill
Hi
From Help, you can understand about delimiter
This specifier is relevant only for delimited .txt files.
delimiter ischar
For delimited table files, an arbitrary delimiter (from the 127 ASCII characters) can be specified through the delimiter is specifier. char specifies a single character.
There are some special cases:
"\t" representing a tab sign, with or without quotation marks.
"\\" representing a backslash ( \ ) character.
the word "spaces" representing all combinations of one or more spaces. Non-printable characters with an ASCII-value below 32, with the exception of CR and LF, will be interpreted as spaces.
If nothing is specified, delimiter is ',' is assumed.
Hi,
As you have instructed, I have made the changes as below.
load
[Essbase Member Name] & '||' & Description & '||' & [CO_To Currency Code] & '||' & [CO_Default Country Num] & '||' & ''
& '||' & [CO_Level 1] & '||' & 'NA' & '||' & [CO_Allocation Flag]
& '||' & [CO_Enabled Flag] & '||' & [CO_Active Flag] & '||' & 'NA'
& '||' & [CO_Expense Reports] & '||' & 'NA'
& '||' & [CO_New Num] & '||' & [CO_Ledger ID] & '||' & 'NA' & '||' & 'NA' & '||' & 'NA'
& '||' & 'NA' & '||' & 'NA' & '||' & 'NA'
from COMPANY1.qvd (qvd);
Extracted file looks ok but the file started with below header. Is there anyway it can be removed?
Thanks,
John
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<QvdTableHeader>
<QvBuildNo>11414</QvBuildNo>
<CreatorDoc>C:\Users\john_byun\DRM_Manipulation.qvw</CreatorDoc>
<CreateUtcTime>2013-10-10 20:45:32</CreateUtcTime>
<SourceCreateUtcTime></SourceCreateUtcTime>
<SourceFileUtcTime></SourceFileUtcTime>
<SourceFileSize>-1</SourceFileSize>
<StaleUtcTime></StaleUtcTime>
<TableName>COMPANY1</TableName>
<Fields>
<QvdFieldHeader>
<FieldName>[Essbase Member Name] & '||' & Description & '||' & [CO_To Currency Code] & '||' & [CO_Default Country Num] & '||' & ''
& '||' & [CO_Level 1] & '||' & 'NA' & '||' & [CO_Allocation Flag]
& '||' & [CO_Enabled Flag] & '||' & [CO_Active Flag] & '||' & 'NA'
& '||' & [CO_Expense Reports] & '||' & 'NA'
& '||' & [CO_New Num] & '||' & [CO_Ledger ID] & '||' & 'NA' & '||' & 'NA' & '||' & 'NA'
& '||' & 'NA' & '||' & 'NA' & '||' & 'NA'</FieldName>
<BitOffset>0</BitOffset>
<BitWidth>8</BitWidth>
<Bias>0</Bias>
<NumberFormat>
<Type>UNKNOWN</Type>
<nDec>0</nDec>
<UseThou>0</UseThou>
<Fmt></Fmt>
<Dec></Dec>
<Thou></Thou>
</NumberFormat>
<NoOfSymbols>202</NoOfSymbols>
<Offset>0</Offset>
<Length>22674</Length>
<Comment></Comment>
<Tags></Tags>
</QvdFieldHeader>
</Fields>
<Compression></Compression>
<RecordByteSize>1</RecordByteSize>
<NoOfRecords>202</NoOfRecords>
<Offset>22674</Offset>
<Length>202</Length>
<Lineage>
<LineageInfo>
<Discriminator>c:\users\john_byun\norton zone\da vinci\drm\company1.qvd</Discriminator>
<Statement></Statement>
</LineageInfo>
</Lineage>
<Comment></Comment>
</QvdTableHeader>
Thanks for the info.
Looks like Qlikview takes only single character as a delimiter.
John
John
Could you reply with a copy & paste of the actual STORE command you are using.
Best Regards, Bill
COMPANY:
load
[Essbase Member Name]
, Description
, [CO_To Currency Code]
, [CO_Default Country Num]
, '' AS [SET_OF_BOOKS_ID]
, [CO_Level 1]
, '' AS [FRINGE_ENTITY]
, [CO_Allocation Flag]
, [CO_Enabled Flag]
, [CO_Active Flag]
, '' AS [COMPANY]
, [CO_Expense Reports]
, '' AS [LEGACY_SET_OF_BOOKS_ID]
, [CO_New Num]
//, [CO_Ledger ID]
, '' AS [STATUTORY_FLAG]
, '' AS [COMPANY_ATTRIBUTE1]
, '' AS [COMPANY_ATTRIBUTE2]
, '' AS [COMPANY_ATTRIBUTE3]
, '' AS [COMPANY_ATTRIBUTE4]
, '' AS [COMPANY_ATTRIBUTE5]
from COMPANY.qvd (qvd);
STORE COMPANY INTO 'Company_Ref.txt' (txt, delimiter is '||');