Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
dwfiv327
Contributor II
Contributor II

STORE command adds extra characters

When I STORE as table as a csv (txt) file, it starts with extra characters.  The first line of the file, which should have only column headings, looks like this:

INDEX,RATE,PERIOD

The column is called INDEX.  How do I get rid of those first 3 characters?  The downstream application that reads this file has trouble with them.

Labels (4)
9 Replies
Taoufiq_Zarra
Master II
Master II

it's a common problem if you save the files as UTF-8 with BOM. You should save them as UTF-8 without BOM

look at

How can I use include files saved in UTF8

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
dwfiv327
Contributor II
Contributor II
Author

This example does not solve my problem.  I am using the STORE command to write a csv file.  The example talks about scripts and " File -> Save with encoding -> UTF-8 with BOM"  I don't even know what BOM is nor where to find this Save command.

Taoufiq_Zarra
Master II
Master II

Link

can you share a part of your qvw file ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
dwfiv327
Contributor II
Contributor II
Author

rateForecast:
LOAD
IndexNumber as INDEX,
// RateIndexCode as INDEXCODE,
Num(Rate,'#.###') as RATE,
RateMonth as PERIOD
Resident RateForecast
Order by IndexNumber, RateMonth
;

STORE rateForecast into .\ReferenceData\rateForecast.csv (txt);

 

The rateForecast.csv file starts with the extra characters that do not belong there.

Taoufiq_Zarra
Master II
Master II

I can't reproduce the same error, that's the result ( your code )

Capture.JPG

Can you also see this solution: Link

does ï "¿ appear only in the heider or also in the data

if only in the header, can you try to save the file without the first line (header ) ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
dwfiv327
Contributor II
Contributor II
Author

QlikView is not the application that will read in this txt file.  So there is no load script that I can change.

To see the characters, open the output file in Word and selected "Windows Default" encoding.

 ï "¿ appears only in the first line, before the first column heading.

Brett_Bleess
Support (Former)
Support (Former)

You are changing the encoding, so I would fully expect a difference in things, you are writing it out as UTF-8, but using a different encoding to open, that is going to be a problem, you need to use the same encoding it was saved as...  

Here is the Help link, which states we use UTF-8, so you would need to adjust the encoding in an interim step somehow if you need it to be Windows Default, PowerShell might be an option to do that, but no idea how to do so, would likely have to check Microsoft forums on that one.  

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
michael_anthony
Creator II
Creator II

I've come across same problem.  We are exporting a set of .csv files from Qlikview which are then sent to a third party to load into their application.  They have come back complaining of an invalid header caused by those three characters you mentioned.  This thread has helped me understand better including the link to UTF-8 BOM.

 

Not sure if you have resolved.  As I understand it Qlikview exports the csv's as UTF-8 BOM format.  The BOM is the 3 characters which is tells the reading program that it is in fact UTF-8 format.  Apparently that is unnecessary but part of the standard so should be recognised.  The application I am sending csv files to must not recognise it, and yours doesn't either.  Some info on the topic.

https://stackoverflow.com/questions/2223882/whats-the-difference-between-utf-8-and-utf-8-without-bom

 

Because mine is a once off exercise, I will probably just load the .csv into notepad++, change the encoding to UTF-8 (from UTF-8 with BOM) and save.

If also found this link - it may offer a solution that you could use:

https://stackoverflow.com/questions/8898294/convert-utf-8-with-bom-to-utf-8-with-no-bom-in-python

Good Luck.

brooksc57
Creator
Creator

This solution worked for my project.

I needed to remove the BOM and also needed to be able to Rename with a Date.

Background, I am exporting a large Fixed Width single column Record.

So, moving data out of Qlik into a text file and then the receiver is putting it into a Unix based No BOM allowed system.

Make sure the 1st row Value is something you don't need.  For me it is the field name itself.

That is the row that will contain the BOM.  

I've let a variable equal a date.  various ways to do that.  I made a qualify table with one date in it.

let vDate = FieldValue('dte.dte',1);

let fle = 'V:\MemDirect\mem\pos\bsls\memPOS.txt';

let flenew = 'V:\MemDirect\mem\pos\bsls\bsls_MEM_' & vDate & '.txt';

Store sdg into V:\MemDirect\mem\pos\bsls\memPOS.txt (txt);    //   stores my single column fixed width record into a text file...unfortunately, it is UTF-8 and has BOM in the first record...so we have to fix that.

EXECUTE cmd.exe /c findstr /V "YourFirstRowFiledName" "$(fle)" > "$(flenew)";   // this finds the BOM Fields (1st row) and ignores it on new file 
EXECUTE cmd.exe /c del "V:\MemDirect\mem\pos\bsls\memPOS.txt"; // this deletes the original file w BOM leave me just 1 new BOM less File

it is all done in the Qlik Script using DOS commands rather than have to use other programs or reference any .bat files even.

Note:  In something related to my project and something for you to think about if providing a similar solution.

I also had to remove all the single quotes, double quotes and commas from my file so they didn't throw off the fixed width the other company uses...this was unfortunate, but it is what it is.  replace(replace(replace(replace(YourFirstRowFiledName,'|',''),'''',''),'"',''),',','')