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:
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.
it's a common problem if you save the files as UTF-8 with BOM. You should save them as UTF-8 without BOM
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.
can you share a part of your qvw file ?
IndexNumber as INDEX,
// RateIndexCode as INDEXCODE,
Num(Rate,'#.###') as RATE,
RateMonth as PERIOD
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.
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 ) ?
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.
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.
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.
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:
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,'|',''),'''',''),'"',''),',','')