Jump to content
maba

Monthly Excel file import, diff between versions -> Table, Page, PageTable, Repeater Matrix?

Recommended Posts

Hello,
I need to import regularly - every 15 or 30 days - a big .xslx file into my PW installation.
This file now has 14 columns, 5.000 rows and grows every month.

I'll need to group, order and work with these data to:

  • analyse User monthly costs
  • analyse User costs per Asset
  • ...

User (real AD account) has to match with a PW user - I can't join to the domain - but as you can see I have some services users (start with sca_*) or no user at all. Those rows have to be assigned to a specific user, e.g. account100.

And:

  • I would like to be able to have a kind of diff function to compare User assets between this and last month (and so on)
  • other request is to have a notification when something change for a User between actual and latest import

First request: which is the best solution to store those data in your opinion? Page, Table, Repeater Matrix, ...?
Those are very repetitive data and I think a page reference is better than to import all the data every time but I have to understand how to manage those "dynamic" groups of software (AccType Det), hardware (Asset), ... For example Price will be imported and not stored with the description because it could be change in the future and I'll not have any control on it.

Thanks!

User,OE,productNmr,AccType1,AccType Det,Count,Price (€),Sum,ASNA,CC,AccType Info,Asset,AccGroup,,,,,,,,,,,,,
Spoiler

,IT,000410,SAP Service,Licenses,0,0,0,012345678,ABC1234,-,,-,,,,,,,,,,,,,
account1,IT,000016,Client - Computer - Hardware,O Notebook ´Standard´,1,0,0,012345678,ABC1234,-,AAA3913,-,,,,,,,,,,,,,
account1,IT,000016,Client - Computer - Hardware,Notebook ´Standard  (1100-1300)´,1,101,101,012345678,ABC1234,-,AAA3913,-,,,,,,,,,,,,,
account1,IT,000015,OS Windows Client,OS4 - Standard Group (Installation w Office),1,10.6,10.6,012345678,ABC1234,-,AAA3913,OS,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Adobe Flash Player ActiveX 27.0.0.183 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: viewer and player,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Adobe Shockwave Player (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: viewer and player,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),CDBurner XP 4.5.6.5844 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: tools,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Copy Path to Clipboard (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: tools,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),OS/M Standard 4.2 - 64bit (installation),1,0,0,012345678,ABC1234,-,AAA3913,OS,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Firefox Windows x.x (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: other,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Google Chrome (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: other,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Google Chrome x.x (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: other,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Java (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: CASE,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Java 8u121 - 32/64 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: other,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Microsoft Office Professional Plus (machine license),1,25.66,25.66,012345678,ABC1234,-,AAA3913,Microsoft-products,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Microsoft Office Professional Plus 2013 SP1 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Microsoft-products,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Notepad++ (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: tools,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Notepad++ x.x (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: tools,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),PDF-XChange Editor (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: converter,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),PDF-XChange Editor 5.5 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: converter,,,,,,,,,,,,,
account1,IT,000014,Software - Dummy - ABCC-EU\CI,OS/M for any (machine license),1,0,0,012345678,ABC1234,-,AAA3913,OS,,,,,,,,,,,,,
account1,IT,000023,Software - OS Windows - Client - ABCC-EU\CI,OS/M for ABCC ITA (machine license),1,0,0,012345678,ABC1234,-,AAA3913,OS,,,,,,,,,,,,,
account2,IT,000016,Client - Computer - Hardware,O Notebook ´Standard´,1,0,0,012345678,ABC1234,-,AAA2999,-,,,,,,,,,,,,,
account2,IT,000016,Client - Computer - Hardware,Notebook ´Standard  (1100-1300)´,1,101,101,012345678,ABC1234,-,AAA2999,-,,,,,,,,,,,,,
account2,IT,000015,OS Windows Client,OS4 - Standard Group (Installation w Office),1,10.6,10.6,012345678,ABC1234,,AAA2999,OS,,,,,,,,,,,,,
account2,IT,000010,Services & Software - CIC (Software I...),MATLAB Compiler Runtime (machine license),1,0,0,012345678,ABC1234,-,AAA2999,MathWorks-products,,,,,,,,,,,,,
account2,IT,000010,Services & Software - CIC (Software I...),MATLAB Compiler Runtime 2015b - 9.0 - 32bit (installation),1,0,0,012345678,ABC1234,-,AAA2999,MathWorks-products,,,,,,,,,,,,,
account100,IT,000023,Services & Software - CIU (Software),Notepad++ (freeware license),1,0,0,012345678,ABC1234,-,AAA1825,Software: tools,,,,,,,,,,,,,
account100,IT,000023,Services & Software - CIU (Software),Notepad++ 7.3.3 (installation),1,0,0,012345678,ABC1234,-,AAA1825,Software: tools,,,,,,,,,,,,,
account100,IT,000023,Services & Software - CIU (Software),PDF-XChange Editor (freeware license),1,0,0,012345678,ABC1234,-,AAA1825,Software: converter,,,,,,,,,,,,,
account100,IT,000023,Services & Software - CIU (Software),PDF-XChange Editor 5.5 (installation),1,0,0,012345678,ABC1234,-,AAA1825,Software: converter,,,,,,,,,,,,,
account100,IT,000014,Software - Dummy - ABCC-EU\CI,OS/M for any (machine license),1,0,0,012345678,ABC1234,-,AAA1825,OS,,,,,,,,,,,,,
account100,IT,000023,Software - OS Windows - Client - ABCC-EU\CI,OS/M for ABCC ITA (machine license),1,0,0,012345678,ABC1234,-,AAA1825,OS,,,,,,,,,,,,,
account100,IT,000016,Client - Computer - Hardware,Notebook ´Application E02200M48 (2000-2400)´,1,49.5,49.5,012345678,ABC1234,-,AAA2963,-,,,,,,,,,,,,,
account100,IT,000015,OS Windows Client,OS4 - Standard Group (Installation w Office),1,10.6,10.6,012345678,ABC1234,-,AAA2963,OS,,,,,,,,,,,,,
account100,IT,000014,Software - Dummy - ABCC-EU\CI,OS/M for any (machine license),1,0,0,012345678,ABC1234,-,AAA2963,OS,,,,,,,,,,,,,
account100,IT,000023,Software - OS Windows - Client - ABCC-EU\CI,OS/M for ABCC Europe (machine license),1,0,0,012345678,ABC1234,-,AAA2963,OS,,,,,,,,,,,,,
account100,IT,000016,Client - Computer - Hardware,O Notebook ´Standard´,1,0,0,012345678,ABC1234,-,AAA3508,-,,,,,,,,,,,,,
account100,IT,000016,Client - Computer - Hardware,Notebook ´Standard  (1100-1300)´,1,101,101,012345678,ABC1234,-,AAA3508,-,,,,,,,,,,,,,
sca_check-mk,IT,000010,Server - Hardware,Server - virtual ESX,1,42.96,42.96,012345678,ABC1234,-,BBB137,-,,,,,,,,,,,,,
sca_check-mk,IT,000014,Software - Dummy - ABCC-EU\CI,OS/M for any (machine license),1,0,0,012345678,ABC1234,-,BBB137,OS,,,,,,,,,,,,,
sca_other-ab,IT,000010,Software - OS Linux - ABCC-EU\CI,OS/X - RHEL Linux Server virtual (machine license),1,10.50,10.50,012345678,ABC1234,-,BBB137,OS,,,,,,,,,,,,,

 

Share this post


Link to post
Share on other sites

I'm not sure I get the full picture...

Apart from analyses, are you doing anything else with this dataset in PW? Are those Excel fields also present as PW fields already?

How do you plan to do this analyses in PW? Do users see this in the backend somewhere? Are you going to build something like a custom dashboard where every user sees their data? Or is it for management overviews?

3 hours ago, maba said:

I can't join to the domain

I don't know what this means.

Depending on what exactly you need to do, you might even consider not using PW at all, i.e. use plain mySQL for data-storage / -update and analytics. PW has an excellent DB API you could use.

I would definitely take a closer look at @bernhard's RockFinder / RockGrid modules, which dramatically optimizes queries in PW, and offers state-of-the art data listing.

  • Like 1

Share this post


Link to post
Share on other sites

Nope. No other needs beyond this costs analyses.
Some fields are already present in my PW installation as page - user (user pages), asset (clients and maybe servers page) - other as single like ASNA and CC (the same for all the rows).

Basically, I thought to analyse this dataset with ListerPro for quick checks (mainly on the latest import), create some charts and share them with my team and other with my boss. The need of a custom dashboard is possible but before I have to set the notification system for every import (and the "diff" features. I have no control of what HQ charges to the daughter companies.
So, at the first, no user interaction in the backend.

21 hours ago, dragan said:

I don't know what this means.

Not so related. It is just to say that I can't use LDAP/SAML for Active Directories authentication.

Yes, I know all the @bernhard's great modules but I don't know if I can create the "diff" features using RockGrid. I'll take a closer look, also for the import requests.

Share this post


Link to post
Share on other sites
14 hours ago, maba said:

Yes, I know the all the @bernhard's great modules but I don't know if I can create the "diff" features using RockGrid. I'll take a closer look

This sound like you don't know them well enough 😉 If using RockFinder I'd definitely go with RF2 and not RF1.

16 hours ago, dragan said:

Depending on what exactly you need to do, you might even consider not using PW at all, i.e. use plain mySQL for data-storage / -update and analytics. PW has an excellent DB API you could use.

WnVkoTL.png

8 lines of code when using RockTabulator. Maybe 10 more for applying the diff (if you have a library for that). I bet you'll save hours of work using RockTabulator, but as I don't fully understand your scenario that's just a guess...

20 hours ago, maba said:

First request: which is the best solution to store those data in your opinion? Page, Table, Repeater Matrix, ...?

If you take the RockGrid or RockTabulator route you'll definitely be better off using pages. I'm using pages for almost anything nowadays because it makes many things a lot easier (like finding data with RockFinder, creating joins and relations, creating fields programmatically etc.; One could create something very similar to Repeater Matrix using RockGrid/RockTabulator but with a lot more options regarding the listing/display part in the backend.

Share this post


Link to post
Share on other sites
4 hours ago, bernhard said:

This sound like you don't know them well enough 😉 If using RockFinder I'd definitely go with RF2 and not RF1.

"RockFinder2 is the successor to RockFinder v1", so.. I'll use RockFinder 😜
I follow your works after saw this awesome Custom Office Management CRM/Controlling Software.

4 hours ago, bernhard said:

8 lines of code when using RockTabulator. Maybe 10 more for applying the diff (if you have a library for that). I bet you'll save hours of work using RockTabulator, but as I don't fully understand your scenario that's just a guess...

Diff has to be done on previous/last imports for a User or Asset. I won't compare the xlsx/csv files.
E.g.: take all the pages about asset ABC1234 from the import of 02 Sep., compare them with ABC1234 pages of today's import and highlight which items changes (pages and their fields).
I don't think this is possible with RockTabulator. Is it possible?

I'll try to be more clear with an example (that I still have to set up 😁).

Thanks for your time and help guys!

Share this post


Link to post
Share on other sites

RockTabulator is just a way to present tabular data. Which data is completely up to you. You can list text, images, html code, whatsoever. So you can definitely also show diffs... as it's just html. The great thing about tabulator is that it supports multiline rows out of the box (aggrid has troubles with that). And this might especially be handy in your situation.

http://tabulator.info/docs/4.4/format

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Rodd
      Hi everyone!
      I have a website in a production environment and I want to duplicate it in a local environment. I exported the content of the website (with the 'Site Profile Exporter' module) but I cannot use it actually. I've got an issue with the database. I imported this one in MAMP then.

      I also exported the pages (with the 'ProcessPagesExportImport' module), but I cannot import it to my local website because the fields don't exist. So I created this fields, but I have this error :
      How can I use the elements that already exist and are presents in my database? How can I duplicate correctly the templates, fields and pages?
      Thanks by advance
      PS: Sorry if my english is bad
       
    • By hellerdruck
      Hi all
      I need help with something. Situation: We have let's say 2'000 Files (Excel) that should be displayed (list with links) on a page. We'd need to filter these files by given Keywords or a tree structure or both. Now, I'm looking for a solution whereas our customer can synchronise the files from his local computer with the folder on the webserver. They will update and upload files on a daily basis. Therefore, it would need to synchronise rather than load the files manually in pages or repeaters. Maybe indexing would be an idea, too.
      Are there any modules for Processwire that would help achieving this? Could anyone point me in the right direction?
      Thanks in advance.
    • By iNoize
      Hello, need some help for an RealEstate project. It have to use the OnOffice to import the objects. 
      https://apidoc.onoffice.de/
       
    • By ngrmm
      I have a page with a table. Each table row has a page-reference field and a checkbox.
      The Page sends emails to all users (page-refrence->email-field) and change the value of the checkbox in a row to 1.
      It works with this:
      <?php // event ID fron url query $eventID = $input->get('eventID','int'); // get event-page $event = $pages->get($eventID); // config $fromEmail = $event->event_mail_from; $fromName = $event->event_mail_from_name; $emailSubject = $event->event_subject; // email html body ob_start(); include('./_inc/emailbody.inc'); $emailBody = ob_get_clean(); // make event-page editable $event->of(false); // loop through table and send out emails foreach($event->event_clients_list as $event_table_row) { // get client page $clientPage = $event_table_row->client_name; // get client email $clientEmail = $clientPage->email; // if client isn't invited yet (checkbox not checked) if($event_table_row->client_invited == '') { // send email $m = new WireMail(); $m->to($clientEmail); $m->from($fromEmail, $fromName); $m->subject($emailSubject); $m->bodyHTML($emailBody); $m->send(); // mark client as invited $event_table_row->client_invited = 1; $event->save('event_clients_list'); } } ?> But i have to use a variable in my emailbody.inc which i'm able to get in the table-loop.
      So i do the including of the body inside my loop. But this doesn't work anymore. Page sends out the emails but is unable to change the value of the checkbox.
      I get no errors!
      I'm using ProTable
      <?php // event ID fron url query $eventID = $input->get('eventID','int'); // get event-page $event = $pages->get($eventID); // config $fromEmail = $event->event_mail_from; $fromName = $event->event_mail_from_name; $emailSubject = $event->event_subject; // loop through table and send out emails foreach($event->event_clients_list as $event_table_row) { // get client page $clientPage = $event_table_row->client_name; // get client email $clientEmail = $clientPage->email; // email html body ob_start(); include('./_inc/emailbody.inc'); $emailBody = ob_get_clean(); // make event-page editable $event->of(false); // if client isn't invited yet (checkbox not checked) if($event_table_row->client_invited == '') { // send email $m = new WireMail(); $m->to($clientEmail); $m->from($fromEmail, $fromName); $m->subject($emailSubject); $m->bodyHTML($emailBody); $m->send(); // mark client as invited $event_table_row->client_invited = 1; $event->save('event_clients_list'); } } ?>  
×
×
  • Create New...