1. Andrew Davis
  2. PowerBuilder
  3. Friday, 17 February 2023 10:53 AM UTC

Hi,

I am trying to load up a text file which contains special characters 

 

an example of the line i am loading is 

54743 ALG00006 Chemloul & Associés

when i load it using the code 

integer li_FileNum
string ls_text
boolean lb_stop

messagebox("will open the file ","c:\rankings\website_files\test.txt")
li_FileNum = FileOpen("c:\rankings\website_files\test.txt", LineMode!)

do

if FileReadEx(li_FileNum, ls_text)>0 then
 messagebox("text read",ls_text)

 

I get the following message box text

 

the characters display incorrectly

I am. not concerned about messagebox doing this but i need to store the input into mydatabase do i need a different datatype or how do i make sure i get and see the correct characters. Currently if i load the file uising dw_import for example i get the same result

 

thanks in advance 

 

using PB 19.0 ( can use newer version if that makes difference !)

 

Andrew

Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 17 February 2023 11:11 AM UTC
  2. PowerBuilder
  3. # 1

Hi.

There are recent conversations about similar issue. Take a look to the following one:

dw_1.ImportFile (appeon.com)

I guess your issue is related to a file using different encoding than EncodingANSI! (which is default for powerbuilder), and doesn't have BOM. You can test it opening it with an editor like notepad++.

If you know the encoding used to save the file then you can specify it in fileopen and see if the problem is solved.

Andreas.

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 17 February 2023 15:05 PM UTC
  2. PowerBuilder
  3. # 2

Hi Andrew;

  The problem is in your FileOpen command as follows ...

li_FileNum = FileOpen ( "c:\rankings\website_files\test.txt", LineMode! )   // Your code

li_FileNum = FileOpen ( "c:\rankings\website_files\test.txt", LineMode!, Write!, LockWrite!,  EncodingUTF16LE! )   // change to this

Note: I would also precede this with ..

IF FileExists ("c:\rankings\website_files\test.txt") = TRUE THEN

   FileDelete ("c:\rankings\website_files\test.txt")

END IF

HTH

Regards ... Chris

Comment
There are no comments made yet.
Andrew Davis Accepted Answer Pending Moderation
  1. Tuesday, 21 February 2023 07:49 AM UTC
  2. PowerBuilder
  3. # 3

Chris

Thanks for this but i am trying to populate a datawindows - well database in the end with the data from the file. 

I may be wrong but i don't think the fileopen encoding option works for read - is that correct ?

 

I have actually done a work round for now, I have loaded the data directly into my SQLAnywhere database using the admin tools of the database - file import from the ISQL window , but this is not ideal going forward and requires more work for the user. and required too many options for the user to mess things up.

Unless there is an embedded SQL command i can use ?

regards and thanks in advance

Andrew

Comment
  1. Miguel Leeuwe
  2. Tuesday, 21 February 2023 11:57 AM UTC
Here's a possible workaround as also mentioned in the ticked: https://community.appeon.com/index.php/qna/q-a/opening-unicode-files-without-boms
  1. Helpful
  1. Miguel Leeuwe
  2. Tuesday, 21 February 2023 11:57 AM UTC
ticket :s
  1. Helpful
  1. Chris Pollach @Appeon
  2. Tuesday, 21 February 2023 13:18 PM UTC
The only way the FileIopen() command would work with the proper encoding would be on a file create. If the file is already created, then PB uses the current encoding scheme of the existing file.
  1. Helpful 1
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 21 February 2023 14:29 PM UTC
  2. PowerBuilder
  3. # 4

Hi, Andrew -

You might take a look at the FileEncoding(filename) PowerScript function to see if that will help you determine how the contents of a file are encoded. I've not used it, but from its description, it might help.

Best regards, John

Comment
  1. Miguel Leeuwe
  2. Tuesday, 21 February 2023 14:53 PM UTC
I think there's a reported ticked on the FileEncoding function not always working as expected. https://www.appeon.com/standardsupport/search/view?id=3573
  1. Helpful 1
  1. John Fauss
  2. Tuesday, 21 February 2023 15:01 PM UTC
Thanks for the info, Andreas. I wondered if that might be the case. What if you opened the file in StreamMode and read the first four bytes into a blob, then examine those bytes to see if one of the BOM's is present and if not, consider the file to contain UTF-8 (without BOM)? I guess the real challenge is to be able to recognize between a file that is ANSI-encoded and one that is UTF-8 without BOM? Maybe the only practical way is to read the file once to scan for any bytes with a value above 0x7f (127) and if found, re-read the file as UTF-8?
  1. Helpful
  1. Andreas Mykonios
  2. Tuesday, 21 February 2023 15:17 PM UTC
If it's really a utf8 file without bom then you can read it in streammode to a blob and using string function convert the contents to the correct encoding. But if your file is ansi, then the string you will end with will be wrong. So this will work but only for files that are saved as utf8 without bom.

Andreas.
  1. Helpful
There are no comments made yet.
Andrew Davis Accepted Answer Pending Moderation
  1. Wednesday, 22 February 2023 09:48 AM UTC
  2. PowerBuilder
  3. # 5

Thank you all so much for your replies, I shall try the suggestions and get back to you all.

 

One thing - in my previous message I asked does anyone know whether i can use an SQL statement to load the text file to my database directly ? My work around was to use ISQL to load it up but that is messy

 

regards

 

andrew

 

 

 

 

Comment
  1. Miguel Leeuwe
  2. Wednesday, 22 February 2023 09:55 AM UTC
Maybe you could use ImportFile() into a datastore / datawindow and then update it. Or, there are some ODBC drivers for text files out there. You could make a pipeline object to import from one ODBC text connection to your final DB.

regards
  1. Helpful
  1. Andrew Davis
  2. Wednesday, 22 February 2023 10:31 AM UTC
Importfile() that is what i was doing and the import file is not liking the special characters.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.