1. Daniel Espinoza
  2. PowerBuilder
  3. Tuesday, 10 August 2021 00:05 AM UTC

Hi Team,

I have some code in Power Builder 2019 R3, I am using a DataStore to import a tab delimited .TXT file, the file contains 51,647 records with 12 columns separated by tab, when I do the ImportFile it returns an error value -13889. The file size is 11 megabytes. I converted the file to CSV separated by commas and same error occurs.

When we separate the file into two files of approximately 25,000 records each, we use the same code and both files are imported without error.

 

Do you know why I have this problem?

Is there a record limit for using the ImportFile?

Is there a file size limit for using the ImportFile?

Why doesn't it return an error code from the ones mentioned in the Power Builder help for the ImportFile instruction?

 

Thanks & Regards,

Daniel Espinoza

Accepted Answer
mike S Accepted Answer Pending Moderation
  1. Tuesday, 10 August 2021 17:13 PM UTC
  2. PowerBuilder
  3. # Permalink

51,647  > 32768.  sound like a 16bit int.

did you do this:

INT li_importedrows

li_importedrows = ds.importfile()

 

if so:

LONG ll_importedrows

ll_importedrows= ds.importfile()

 

 

 

 

 

Comment
  1. mike S
  2. Tuesday, 10 August 2021 19:33 PM UTC
51K does not fit into a 16 bit int. so you will get wrap around and the value typically is negative.

check your code for the use of integers.

  1. Helpful
  1. Daniel Espinoza
  2. Tuesday, 10 August 2021 21:38 PM UTC
Hi Mike,



Thanks for your answer, the problem is that. I mistakenly had an Integer variable for the result of the ImportFile, I changed the variable to type Long and the ImportFile statement in the DataStore worked correctly.



Regards
  1. Helpful
  1. mike S
  2. Wednesday, 11 August 2021 01:14 AM UTC
good to hear. i got burned by that same exact thing enough so its easy for me to see it now.
  1. Helpful
There are no comments made yet.
Mark Goldsmith Accepted Answer Pending Moderation
  1. Wednesday, 11 August 2021 01:37 AM UTC
  2. PowerBuilder
  3. # 1

Hi everyone,

Now that the issue is resolved and it appears the rows were actually importing fine I just wanted to come back to John's reply on the encoding and share some thoughts:

Based upon the Help file as John suggests it could lead one to believe there was a possible encoding issue due to the ASCII reference, which implies a 7-bit code. Not knowing that the records were actually being imported successfully could also make the issue misleading. I would suggest, though, that the Help for the ImportFile() method is not entirely correct. First, the reference to ASCII is misleading as the importing of characters in the 128 to 255 code range (really just interested in accented/ printable characters here) from an "ANSI encoded" file work just fine. I put quotes around those two words because there's lots of debate as to whether ANSI is a standard, an encoding, both, extended ASCII etc. and so I won't blab on any further on that topic.

However, to change it to ANSI would not be entirely accurate either as ImportFile() will work fine with a file encoded with UTF-8 (albeit as long as there are no accented characters because, while no errors occur, accented characters will show up as giberish, but it still imports). Also, importing a file that is encoded as UTF-8 with BOM will work just fine as well and import accented characters properly. It's interesting that this method doesn't choke on a file that is UTF-8 (without BOM) whereas other methods do fail like FileRead() and FileReadEx() when the file is opened with EncodingUTF8!. At a minimum I wish all methods worked the same way as the ImportFile() method - meaning have more flexibility with encoding types as opposed to just failing - versus having to do something like I suggested here: https://community.appeon.com/index.php/qna/q-a/searching-text-in-blob-column-contains-word-document

The primary point of all the above being that the guidance in the Help on the ImportFile() method stating that the file must be ASCII is misleading...unless they mean something other than what I'm familiar with when using the term ASCII...and so the Help should probably be re-worded. That said, the wording in the Help file on the ImportFile() method referencing ASCII is almost word for word as far back as PB 5, and maybe earlier I can't recall, and so maybe it just hasn't been updated as other encodings have come along.

Would also love to see Appeon change how PowerBuilder deals with text files as far as EncodingANSI! being the default and how it responds to a UTF-8 with or without BOM encoding in a file.

Finally, this 18 year old article on character sets, while referencing some technologies that are out-dated or no longer around, still provides a great perspective and backgrounder on character sets. The original author is now chairman of the board of Stack Overflow. https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/

Thanks for your indulgence, just my thoughts...regards,

Mark

Comment
There are no comments made yet.
Daniel Espinoza Accepted Answer Pending Moderation
  1. Tuesday, 10 August 2021 21:40 PM UTC
  2. PowerBuilder
  3. # 2

Hello team

Thank you all for the support and your comments.

Regards

Daniel Espinoza.

Comment
There are no comments made yet.
Mark Goldsmith Accepted Answer Pending Moderation
  1. Tuesday, 10 August 2021 19:22 PM UTC
  2. PowerBuilder
  3. # 3

Hi Daniel,

The return value, if positive, is the number of rows that were imported if it succeeds. If negative, it represents an error. However if you store the return value in a 16 bit Integer variable instead of a Long and the number of rows to import are greater than 32,767 then you could still return a negative number.

I think Mike's point in his reply is that the math seems to makes sense if you were storing the return value in an Integer instead of a Long. I didn't double-check my math so I may be mistaken but you have 51,647 records to import. If you subtract 32,767 (the largest positive number that can be stored in a signed integer) from the number of records to import that leaves 18,880 more records. If you add 18,880 to an Integer variable currently storing the value 32,767 (as that's how much higher you want your number to be, but it can't due to the storage limit of an Integer) you will get -13,889 which matches your supposed error number.  So, seems like it's not an error number per se but the number of rows that were imported when the return value is stored in an Integer variable.

Is the issue only that the value returned doesn't appear to match the number of rows imported or also that no rows were actually imported (when you try to import the entire file)?

Regards,

Mark

Comment
  1. Daniel Espinoza
  2. Tuesday, 10 August 2021 21:36 PM UTC
Hi Mark,



Thanks for your answer, the problem is that. I mistakenly had an Integer variable for the result of the ImportFile, I changed the variable to type Long and the ImportFile statement in the DataStore worked correctly. Thanks to Mike who told us about the limitations of the variables.



Regards
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 10 August 2021 17:52 PM UTC
  2. PowerBuilder
  3. # 4

Hi Daniel;

   Have you tried adding the following to your PB IDE's pb.ini file ...

[DataStore Behavior]

UseHwnd=no

Regards ... Chris

Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 10 August 2021 06:04 AM UTC
  2. PowerBuilder
  3. # 5

since you've already converted the file also to CSV, what happens if you try to open it with Excel? Maybe you get a more specific error message.

regards

Comment
  1. Daniel Espinoza
  2. Tuesday, 10 August 2021 14:50 PM UTC
Hi, Miguel

The conversion from the .TXT file to CSV is done with EXCEL and then I use that CSV file for the import. When I split that file in two is when the import works. No problem opening the file with EXCEL.



Regards
  1. Helpful
  1. Miguel Leeuwe
  2. Tuesday, 10 August 2021 14:57 PM UTC
Hi Daniel,

"How" do you split the files in two? Are you sure to exclude the headers (if there are any) when saving the excel file?

Can you send us the files?

regards.
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 10 August 2021 01:58 AM UTC
  2. PowerBuilder
  3. # 6

Hi, Daniel -

How is the file encoded? The help for ImportFile method (DataWindow) states the file should be in ASCII format. Regardless, it sounds like PB is not able to successfully recognize the original file's encoding.

I suspect when you edited a copy of the file to split it into two parts, you unknowingly changed the encoding into something that PB recognized. That's why it's able to Import both parts successfully.

If you edit the original file with Notepad, for example, then immediately Save As (without clicking Save), to the immediate left of the Save button is drop-down that lists the file's encoding that Notepad detected when the file was opened/read.

If you have no control over the encoding of the source file, then an alternative approach to importing the data would be to open (FileOpen is where you can explicitly specify the file's encoding) and read the entire file into a single string variable using FileReadEx, then try to import the data from the string using ImportString. If you want to try this, I suggest you first read the entire help topic "ImportString method (DataWindows)".

HTH, John

Comment
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.