1. Richard Carrier
  2. PowerBuilder
  3. Sunday, 26 May 2019

We have a data window that retrieves about 250K rows.

 

When I try and export to XLSX , it runs for a while then blows up. It DOES Not have much formatting other than 3 total columns.

 

When I use TXT or the Save as formatted txt the file is almost 600K but saves.

I then load into excel and then save as .XLSX and the new file is now 85K

This has got to be an issue that others have encountered or just a bug.

 

I saw a another article with this exact issue and a stupid answer suggesting when the person saved, excel was truncating to 65K rows. That's NOT it. this is Excel 12 and I could see the rows in the spreadsheet I imported using the import wizard

 

I'm using the ANSCI default encoding.

 

Any help appreciated.

 

Thanks

Richard

Roland Smith Accepted Answer Pending Moderation
0
Votes
Undo

Try writing it to tab delimited text file but give it an Excel file extension. When you open it in Excel it will give a error message but then loads it Then you can save it from Excel in the proper format.

This works for xls format, I haven't tried with the new format. If it works, maybe you can use OLE from PowerBuilder to convert from text to Excel.

Comment
There are no comments made yet.
  1. Tuesday, 28 May 2019
  2. PowerBuilder
  3. # 1
Andreas Mykonios Accepted Answer Pending Moderation
0
Votes
Undo

Yes I use saveas the way you mention.

For framework installed on my pc, here is a screenshot:

Andreas.

Comment
There are no comments made yet.
  1. Tuesday, 28 May 2019
  2. PowerBuilder
  3. # 2
Richard Carrier Accepted Answer Pending Moderation
0
Votes
Undo

can you tell me the .net framework version that is on the machine you tested with.

Also, I'm assuming, you are using the saveas function and choosing xlsx with headers from the list ?

Comment
There are no comments made yet.
  1. Monday, 27 May 2019
  2. PowerBuilder
  3. # 3
Richard Carrier Accepted Answer Pending Moderation
0
Votes
Undo

I dont see how memory would cause a file to be larger, maybe run longer or blow up.

The machine has 32 gb of memory

Comment
There are no comments made yet.
  1. Monday, 27 May 2019
  2. PowerBuilder
  3. # 4
Andreas Mykonios Accepted Answer Pending Moderation
0
Votes
Undo

Have you checked the memory while you are saving the file? If your application is 32 bit or don't have enough memory, then this could be the reason for your issue.

Andreas.

Comment
There are no comments made yet.
  1. Monday, 27 May 2019
  2. PowerBuilder
  3. # 5
Richard Carrier Accepted Answer Pending Moderation
0
Votes
Undo

Andreas

 

I'm using PB 1880

I have about 100 columns but I must be doing something wrong or we have an issue.

My xlsx file actually blows up before it writes

the txt file is 170mb and take 20 minutes

 

What verison of .net framework, are you using Ansi encoding, we also have multiple dddw's

 

Any more clues. Sounds like it should be working per your test

 

 

Comment
There are no comments made yet.
  1. Monday, 27 May 2019
  2. PowerBuilder
  3. # 6
Andreas Mykonios Accepted Answer Pending Moderation
0
Votes
Undo

Another thing I forgot to mention is: when I open a xlsx created from a powerbuilder application and save it, it seems to become larger... Sel2 - copy.xlsx is a copy of sel2.xlsx, opened in excel and saved without any modifications.

Andreas.

Comment
There are no comments made yet.
  1. Monday, 27 May 2019
  2. PowerBuilder
  3. # 7
Andreas Mykonios Accepted Answer Pending Moderation
0
Votes
Undo

I have seen xlsx files produced from powerbuilder being smaller than any produced .txt (those .xlsx have no formatting).

The above screenshot is an example. The TotalReport_201905271637a.xlsx is smaller than TotalReport_201905271637u.txt (Unicode version) and TotalReport_201905271637a.txt (ANSI/DBCS version).

Most issues that I faced when saving to xlsx format were related to PB.NET and not pb classic...

In your original post I don't believe you mentioned that you successfully save the datawindow to xlsx format.

Anyway. I run a test with PB 2017 R3 (1858). I create a datawindow, add a dataobject with 6 columns. Upon retrieve this datawindow returns almost 207.000 records.

Save the contents in the datawindow to xlsx and txt. Screenshot of the result:

So for me the xlsx is smaller. It took almost 16 sec to save to xlsx. Txt takes less than a second. And I don't have any issue...

Andreas.

Comment
There are no comments made yet.
  1. Monday, 27 May 2019
  2. PowerBuilder
  3. # 8
Richard Carrier Accepted Answer Pending Moderation
0
Votes
Undo

Andreas,

 

Let me try and clarify.

I'm using the latest build of Appeon Powerbuilder. 

There is no error. happens runtime or development and it's consistent

 

The XLSX exported file is significantly larger than the exported txt file when coming out of powerbuilder so I dont understand your comment that xlsx is compressed and its normal to be smaller than txt. 

My problem is powerbuilder is bloating the file export. When we load that 500K xlsx into excel, then save as another name its 1/10 the size.

Thanks

Comment
There are no comments made yet.
  1. Monday, 27 May 2019
  2. PowerBuilder
  3. # 9
Andreas Mykonios Accepted Answer Pending Moderation
0
Votes
Undo

About the first problem (when trying to save as xlsx), it would be nice to provide us with more information:

  • Version of PowerBuilder where the problem occurs.
  • This happens in runtime?
  • Does it show any error?
  • Have you tried to save directly from the IDE? What happens?

As you mentioned your datawindow has 3 columns. What is the maximum length for each column? What data do they contain? They could contain any strange control characters?

Andreas.

Comment
There are no comments made yet.
  1. Monday, 27 May 2019
  2. PowerBuilder
  3. # 10
Andreas Mykonios Accepted Answer Pending Moderation
0
Votes
Undo

One thing that you should know is that xlsx files are actually compressed files. So it's normal to see that an xlsx containing the same data with a txt has actually a smaller size. Another option that can make your txt smaller or bigger, is the encoding you are using... ANSI/DBCS should create smaller files that UTF8.

Andreas.

Comment
There are no comments made yet.
  1. Monday, 27 May 2019
  2. PowerBuilder
  3. # 11
  • Page :
  • 1


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