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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By dragan
      Is it by design that a site/ready.php is not included when creating a new site profile? Is it possible to include it with a hook? Or are there any security thoughts? (I don't want to redistribute it in public, it's just so I have my own boilerplate)
    • By Tyssen
      I have a page that contains a single ProFields table field and I want to display the contents of the table on the front end and then for logged in users, they can edit certain columns in the table.
      What I have at the moment is
      $out = '<form action="'.$page->url.'" method="post" > <table class="table"> <tbody>'; $count = 1; foreach($page->fieldName as $row) : $out .= ' <tr> <td><input type="checkbox" name="fieldName_'.$count.'_columnName"></td> </tr>'; if($input->post->submit) : $page->of(false); $page->set('fieldName_'.$count.'_columnName', $sanitizer->text($input->post->{fieldName_'.$count.'_columnName})); $page->save(); endif; $count++; endforeach; $out .= ' </tbody> </table> <button class="button" type="submit">Save</button> </form>'; The two problems I have are:
      I get an error trying from $sanitizer->text($input->post->{fieldName_'.$count.'_columnName}), not sure how to make that dynamic.  If I change the above to just a static value, e.g. $page->set('fieldName_1_columnName', 'Testing') and save the form, it's not saving the values to the database. Where am I going wrong?
    • By karian
      I don't know why multiple instances (repeater_repeat_columns1, repeater_repeat_columns2, ...) of my repeater field are displayed inside Template field (see image).
      Is there a way to clean/reset it ?
       

    • By psy
      I'm combining two PW sites into one, Site A into Site B.
      At each step, I did it bit by bit as the 'all at once' approach failed.
       
      First, I exported all the fields from Site A and imported into Site B. Any field types not supported by import/export, eg FieldtypeOptions I manually recreated. All good.
      Next I exported all the templates from Site A and imported them into Site B and copied across their associated template files. All good.
      Finally I exported the pages I needed from Site A into Site B - again, bit by bit to ensure it all went smoothly.
      From the admin side, it all looked and worked perfectly.
      Front end was a totally different story. All existing pages in Site B worked as expected. NONE of the pages imported from Site A displayed. They all ended in a redirect loop with no errors in the PW logs or Tracy Debugger.
      After some trial-and-error, I finally got it working with:
      - create a new template in Site B admin with no associated template file and just a title field
      - import the fields from the imported Site A template into the newly created template (both on Site B)
      - copy the Site A php template file into a new file that matched the new PW Site B template name and save in Site B site/templates
      I can deal with the above workaround. Just curious to know if I did something wrong or if the template import/export feature is problematic?
       
      ### Solution:
      While the export/import was a slow process, turned out the front end redirecting issue was unrelated. For reasons unknown, all templates marked as HTTPS only were the ones redirecting, ie all templates from Site A. Finally solved it by changing the $config->https to true in site/config.php
      Now the pages display correctly as https whether the template forces the issue or not.
       
    • By rareyush
      i am receiving and error whenever I try to run my processwire on localhost,

       
       
      sql code
       
      -- -- Table structure for table `field_fieldset_meta_end` -- CREATE TABLE `field_fieldset_meta_end` ( `pages_id` int(10) UNSIGNED NOT NULL, `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `field_fieldset_meta_END` -- CREATE TABLE `field_fieldset_meta_END` ( `pages_id` int(10) UNSIGNED NOT NULL, `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; anyone ?
      whenever I make a new database and upload it there, database get imported without errors.
×
×
  • Create New...