Delimiter

Report Inappropriate Content · ‎2013-10-10

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

rustyfishbones · ‎2013-10-10

can you provide a sample app

Report Inappropriate Content · ‎2013-10-10

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 '||');

pgrenier · ‎2013-10-10

Hello John,

From the QlikView online help:

Delimiter

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

Anonymous · ‎2013-10-10

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

MayilVahanan · ‎2013-10-10

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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

Report Inappropriate Content · ‎2013-10-10

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"?>

<CreatorDoc>C:\Users\john_byun\DRM_Manipulation.qvw</CreatorDoc>

<TableName>COMPANY1</TableName>

<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>

<Type>UNKNOWN</Type>

</NumberFormat>

</QvdFieldHeader>

</Fields>

<Discriminator>c:\users\john_byun\norton zone\da vinci\drm\company1.qvd</Discriminator>

</LineageInfo>

</Lineage>

</QvdTableHeader>

Report Inappropriate Content · ‎2013-10-10

Thanks for the info.

Looks like Qlikview takes only single character as a delimiter.

John

Anonymous · ‎2013-10-10

John

Could you reply with a copy & paste of the actual STORE command you are using.

Best Regards, Bill

Report Inappropriate Content · ‎2013-10-10

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 '||');

Exporting Qlikview sheet using double piple

Delimiter