Adding BOM to csv

[RomainG]

Hello everyone,

I am trying to create and send a csv file by email. However, in the output csv file, I am having trouble reading special characters such as "è", "à", "ù" or "ç".

My original idea was to use the built-in function Transform list like this:

Code: Select all

#FilePathMat := '/LANSA_ptdpgmlib/tmp/' + "CmdMateriel.csv"

#CITI01CMDLIB := 'Id Categorie,Categorie,Id Materiel,Materiel,Quantite'
Add_Entry To_List(#CmdHC)
Use Builtin(Transform_List) With_Args(#CmdHC #FilePathMat SU B T '.' N) To_Get(#IO$STS)
Clr_List Named(#CmdHC)

Select_Sql Fields(#CITI01CTMACH #CITI01LBCTMC #CITI01CDMACH #CITI01LBMACH #CITI01QTEMAT) Using(#SQLQUERY)
* #CITI01LBCTMC := #com_owner.CleanString( #CITI01LBCTMC )
* #CITI01LBMACH := #com_owner.CleanString( #CITI01LBMACH )
Add_Entry To_List(#ListGetMat)
Endselect
Use Builtin(Transform_List) With_Args(#ListGetMat #FilePathMat SU B T) To_Get(#IO$STS)

I tried changing the Output file format parameter using different options but none of them work in my case.

I then wanted to try and add the BOM to my file before inserting my data like this:

Code: Select all

Define_Com Class(#PRIM_IOC.FileStream) Name(#FileStream) Fileaccess(Write) Filemode(CreateNew) Fileshare(Write)
Define_Com Class(#PRIM_DC.UnicodeString) Name(#CsvContent)

#FilePathMat := '/LANSA_ptdpgmlib/tmp/' + "CmdMateriel.csv"

* Open FileStream
#FileStream.Path := #FilePathMat

* Write BOM to the file
#FileStream.WriteBytes('EFBBBF') * Using the property ('EFBBBF').AsBytes would be the ideal candidate but it does not exist.

* Add CSV Header
#CsvContent := 'Id Categorie,Categorie,Id Materiel,Materiel,Quantite' + (10).AsUnicodeString
#FileStream.WriteChars(#CsvContent)

* Add CSV Data
Select_Sql Fields(#CITI01CTMACH #CITI01LBCTMC #CITI01CDMACH #CITI01LBMACH #CITI01QTEMAT) Using(#SQLQUERY)
    #CsvContent := ('&1,&2,&3,&4,&5' + (10).AsUnicodeString).Substitute(#CITI01CTMACH #CITI01LBCTMC #CITI01CDMACH #CITI01LBMACH #CITI01QTEMAT.AsString)
    #FileStream.WriteChars(#CsvContent)
Endselect

* Attach the file to email
Use Builtin(MAIL_ADD_ATTACHMENT) With_Args(#FilePathMat 'CmdMateriel.csv') To_Get(#LEM_RETC)

But this does not work. I would therefore like to know if there is a way to do this. Or is there maybe another solution that I have not seen?

Thank you for your help,

Kind regards,

Romain

[Gilamonster]

Hi Romain,

Have a look on the BIFs STM_FILE_WRITE and STM_FILE_OPEN
You can use a code page parameter in the STM_FILE_OPEN first optional argument

By example :

#FilePathMat := '/LANSA_ptdpgmlib/tmp/' + "CmdMateriel.csv"
#FileOption := 'WRITE Text LineTerminator=CRLF CodePage=1252'

USE BUILTIN(STM_FILE_OPEN) WITH_ARGS(#FilePathMat #FileOption) TO_GET(...)

SELECT
USE BUILTIN(STM_FILE_WRITE) WITH_ARGS(...)
ENSELECT

USE BUILTIN(STM_FILE_CLOSE) WITH_ARGS(...)

Try first using the code page related to your IFS ! Then code page 1252 which is the most suitable on Windows for Western Europe ( it works for French !)

I hope this will help
kind regards
Christophe

[RomainG]

Hi Christophe,

Thank you for your response, I tried using your method and it works!

However, we did some testing with the BIF transform list and we realized that if we converted our csv file to UTF-8 BOM (using Notepad++), the characters were read correctly so that is why we wanted to try to add the bytes for the BOM (EF BB BF) in our file.

Out of curiosity, do you know if there is a way to write bytes to a file before writing our data?

Kind regards,

Romain

[Gilamonster]

Hi Romain,

No idea. But have you tried to use "Binary" instead of "Text" in the STM_FILE_OPEN Options (ie #FileOption := 'WRITE Binary LineTerminator=CRLF'
This should avoid code page conversion, and maybe doing the trick ?

Regards
Christophe

[RomainG]

Yes I have tried using 'Binary' but unfortunately it does not seem to work.

Thanks anyway