topic Re: STORE command adds extra characters in QlikView

STORE command adds extra characters

dwfiv327 — Sat, 16 Nov 2024 01:27:30 GMT

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.

Re: STORE command adds extra characters

Taoufiq_Zarra — Tue, 21 Jan 2020 14:13:15 GMT

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

Re: STORE command adds extra characters

dwfiv327 — Tue, 21 Jan 2020 14:26:25 GMT

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.

Re: STORE command adds extra characters

Taoufiq_Zarra — Tue, 21 Jan 2020 14:30:47 GMT

Link

can you share a part of your qvw file ?

Re: STORE command adds extra characters

dwfiv327 — Tue, 21 Jan 2020 14:34:06 GMT

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.

Re: STORE command adds extra characters

Taoufiq_Zarra — Tue, 21 Jan 2020 14:52:23 GMT

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

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

Re: STORE command adds extra characters

dwfiv327 — Tue, 21 Jan 2020 14:57:38 GMT

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.

Re: STORE command adds extra characters

Brett_Bleess — Tue, 18 Feb 2020 19:52:18 GMT

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

Re: STORE command adds extra characters

michael_anthony — Wed, 08 Jul 2020 12:55:16 GMT

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.

Re: STORE command adds extra characters

brooksc57 — Tue, 27 Apr 2021 22:30:24 GMT

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,'|',''),'''',''),'"',''),',','')