Dear Peter,

I also had a problem with creating csv files.
My solution was for 0 leading text fields to use ="value" in the cell.
CSV file example:

 =0101;101
="0101";101
0101;101
="     0101";101

So I simple put =" before the field on the output form and " char after the field.

Best regards,
Viktor

Hi All

For a long time whenever we wanted some data output which would invariably be used in excel, We would just generate a tab delimited file (sometimes csv but mostly tab delimited), and just launch excel pointing at that file.

For the most part this is fine, although we have noticed things like telephone fields and a few others have leading 0's stripped off them. The only way to not have this happen was for us to manually open excel on its own, and then open the file from within excel which caused the import wizard to come into play, where you can then say treat this column as text.

We had a thought recently to try our hand at instead of just writing a pure text file and opening it in excel, could we just directly create an xlsx.

Test data: 572 columns 5000 rows

Method 1
Excel interop for c# was the first thing we tried. The first implementation was creating a List<List<string>> which is essentially a 2d array to store each cells contents. Then whilst doing a foreach around it setting each cells value through the excel interop. This turned out to be horrifically slow to the point where I never actually saw this successfully finish on my large dataset (it did finish on a 4 column 4 row test) as I had to bomb out, from further research I think this boils down because its an open instance of excel and there is a lot of overhead. I saw somewhere it suggested to set the application ScreenUpdating flag to false can improve performance but my dataset was large enough I couldnt see any improvement.

Method 2
I then found a suggested improved approach which seems like a .net equivalent of Heidis COM example from https://magicu-l.groups.io/g/main/files/OfficeCom%20for%20Magicxpa/OfficeMagic2.zip which I've also seen referenced to elsewhere on the forums, which is to by using vectors move the entire data in one call to a range of cells. However in my test of 572*5000 data this method failed to execute. It was fine in a 572*50 test. Maybe it was breaking some form of memory/size limit? I will also try and give Heidis COM example a try at somepoint, but I presume it will peform like c# interop and its just a different interface.

Method 3
I found a third party software called ClosedXML, currently hosted on github. This has an advantage in that its completely self contained and doesnt even need excel installed to work. however it seems to add an extra 50% processing time to perform, on the time having read all data into a c# array. so for my 5000 records it took 1 minute 40 seconds to process the data into a c# array. (not just one table, this is 5000 profiles in one table with data in supporting tables to be read). then about 50 seconds to decant that data through ClosedXML. Which is sort of alright but its still a pretty heavy time hit.

Method 4
Hyper focusing on our core issue of we dont want leading 0's to be stripped off fields. Whilst experimenting with all of the above I ran accross enough of the interop API to try just taking a tab dlimited file, and opening it in excel whilst trying to maintain a particular formatting.

object fieldInfo = new int[,] { { 1, 2}, { 2, 2 }, { 3, 2 }, { 4, 2 }, { 5, 2 }, { 6, 2 }, { 7, 2 }, { 8, 2 }, { 9, 2 }, { 10, 2 }, { 11, 2}, { 12, 2 }, { 13, 2 }, { 14, 2 }, { 15, 2 }, { 16, 2 }, { 17, 2 }, { 18, 2 }, { 19, 2 }, { 20, 2 }, { 21, 2}, { 22, 2 }, { 23, 2 }, { 24, 2 }, { 25, 2 }, { 26, 2 }, { 27, 2 }, { 28, 2 }, { 29, 2 }, { 30, 2 } };

xlApp.Workbooks.OpenText("c:\\selections\\petera\\18032020.TXT",

        false, 1,

        Excel.XlTextParsingType.xlDelimited,

        Excel.XlTextQualifier.xlTextQualifierDoubleQuote,

        Type.Missing, true, Type.Missing, Type.Missing,

        Type.Missing, Type.Missing, Type.Missing,

        fieldInfo, //field info

        Type.Missing, Type.Missing,

        Type.Missing, Type.Missing, Type.Missing);

The above lines of code defining all the collumns as format 2 which means text. and then using the open text function to pull all the data into the worksheet preformatted as text. And then you can use saveas to save it as an xlsx file if you want. For a pre-existing tab dlimited file of my 572*5000 data, it took 5 or 6 seconds to occur. which is pretty good.

Final thoughts
So maybe method 4 is the way to jump for us, but im still a bit stumped at how poorly the c# excel interop performed. Has anyone else tried to automate an excel file other than just raw text output to a delimited file, or Heidis COM example which I will give a try? Or is it just that im trying to output such large datasets thats hampering me?

Thanks for any thoughts

Peter

Hy Todd,

I think there will be a better jump in point between 4-5. If you watching the chart there is a stay at 4$ in the past, but in this situation nothing sure, i could imagine lower levels.

Best Regards,
Gábor

A while ago, I tried SQLite 100% in read only mode for a single web service and experienced occasional lock errors.  Finally, I changed to MSSQL Express and resolved the issue.
Thanks
Thomas Titus.

I've just received the following reply from Magic helpdesk:

Hi Tomasz,

I have following update from our Product Management,

SQLite is a single user DB and the same considered by Magic.
We do not support accessing the same DB by multiple threads/engines.

So there is no way to use SQLite in XPA multi user environment even if we accept locking of whole DB in the moment of writing. It a pity. 

Tomasz

Thinking out load...

What if we all bought 100 shares of Magic Software (MGIC) today/tomorrow?  The price is currently 6.63/share with a 4.62% dividend.  Think of it is as offsetting your annual support cost.

Todd

Thanks Rob for the update.

Thomas Titus

Hi Hans,

I just solved it.

I changed the user of the Broker to myself and then Magic can write to the share. Apparantly the other useraccount doens't have access on the Magic server to execute/open the share.

Now I only have to find out why that user doesn't have the rights.

Thanks,

Rob

Op 17-3-2020 om 14:55 schreef Hans Brussaard:

Hi Rob,

Have you tried executing the net use command from within Magic using Invoke OS?

Hans

Hello

Some time ago I raised the topic of creation of accordion style menu which is today's standard for web applications. On advice of the group I've created it directly in XPA using the trick with butons in the table. It works ok however in one of the replies it was mentioned the possibility of using Web Browser: here
Unfortunately described solution use ActiveX variable which seems to be not supported any more by XPA (see the youtube movie in the link). Does anybody have idea how to do the same without ActiveX variable?

Tomasz

Hi Thomas,

Op 17-3-2020 om 04:13 schreef Thomas Titus via Groups.Io:

Are you setting up a remote Unipass Broker, away from the DMZ IIS server?

We have a webserver running in DMZ with Apache

And a Magicserver in AD running the Magic broker and the Magic engines

Do you have an ARR Server (Application Request Routing) to host your file share?

The fileserver is a regular server with a share

Are you setting up multiple IIS to do load balancing?

No. I think that the webserver isn't getting very much to do. I think that the only reason for multiple IIS is for fault tolerance.

Hi Luuk,

the problem is not the command (net use) itself. The command must be executed on startup of the server in background, without any user logged in.

Some more details:

• First delete the connection

• Create the connection, if a password is asked, enter it:

• Use WMIC to see the username that was used for this connection:

• Delete the connection again:

• Create connection by specifying PASSWORD and DOMAIN\USERNAME from previous steps:

Hi Rob,
I am also in the process of setting up DMZ IIS. Our infra structure team is struggling with a third party vendor to complete the setup.

Are you setting up a remote Unipass Broker, away from the DMZ IIS server?

Do you have an ARR Server (Application Request Routing) to host your file share?

Are you setting up multiple IIS to do load balancing?

I know, I asked a lot of questions, but I am very curios to know your efforts and progress.

Thanks

Thomas Titus

 

 

 

Are

 

I've tried both Physical and Deferred and also different locking strategy.

What are the transaction settings for the program?

Hi Wes

I've just tried and I have the message in the statusline "Failed to disconnect, datasource is open, datasource: Products". My test is very simple: I'm running simple browsing program for table Products from top menu on 2 session in Runtime. The problem starts if the program is open on both session and I'm modifying a record on one of them. Even after closing the program on the that session I'm getting "db is locked" error on the second one. On the other hand the first session is also not possible to use as when try to start browsing program again I'm getting "cannot start a transaction withing a transaction" error.

Tomasz

Have you tried a DbDiscnt  after updating the value?  Just a WAG.  Is this database open all the time for default values or only opened on occasion?

Wes

Hello

I know that SQLite is rather single-user DBMS but I red here that it is possible to use in in multi-user environment - this is just the question of updating by 1 user at any instant of time as whole DB is locked on writing. However when I'm trying with Magic (XPA 3.3) it looks like first update lock DB exclusively and not releasing it after this update is finalized. Second Magic session gets just the message - "Database locked". Is it really no possible to overcome this?

regards
Tomasz