I currently have a spreadsheet (ExCel) with about 3,000 rows.
Each row contains: Property Code | Property Address | PropertyName | Property Manager’s Name.
I’m trying to find a way to display this information clearly on a website in the best way possible so that a resident of one of the properties can find out who their property manager is.
I’m open to any ideas as to how I can achieve this in the least painful way. I can think of Importx but I’m not sure I want a resource for every row.
This is in MODX 3.1.2 PHP 8.3
To be clear I have not done this but if I were to approach it this I would use MIGx. There is a thread related to importing csv file after building your setup in the manager using Modx 3. There are issues with Modx 3 import but @halftrainedharry has outlined how to get around this. But that being said 3000 lines is going to be “interesting” to try and manage in the manager. How do I import data from Excel to getImageList?
I would create a custom database table.
So if you want to use MIGX, use MIGXdb instead of a MIGX TV. (With a MIGX TV, all the data is stored as JSON, which is not efficient to search.) There exists an action-button importcsvmigxdb, which is to import data into a db-table from a CSV file. (The action-button importcsvmigx is for a MIGX TV.)
You could also write a custom script for the import. If you create a CSV from the Excel-file, you can simply use the PHP function fgetcsv to read the data.
Thank you chaps. I’ll look into both these methods. I can see that I can reduce the number of rows significantly due to the way it is set up and the output required so I’ll assess again when I’ve got a manageable spreadsheet.
I agree that a custom database table would be a good way to go. I have a custom table I built from a CSV file with 1.5 million rows. I use regular PDO to access it. It’s staggering how fast it works and I’m on a shared server.
On my local PC I build SQL from the CSV using a python script, python because I know it and it has more command line support than PHP, but it could be PHP. Then I upload the SQL to my server and install it using MySql command line in a terminal. I do have to chunk the SQL INSERT statements into multiple files to avoid timeouts in MySql. phpMyAdmin seems to have problems creating tables from really big imports.
When it comes to display it depends on whether you are going to offer a search feature or just show all the data and allow the resident to use their browser’s search. If the latter then tables might work well, they might seem old fashioned but they work for variable length data. You can put long fields like the address in a column with a fixed width and it will wrap.
Thank you Mike. Some useful insights there. I think I’m going to be using a postcode search for this as it seems to be the only reliable way of tying a property manager’s name to a housing scheme. I’ve asked the client for an export of property manager’s names and the postcodes they manage. It will make life so much easier if they can, and it will also cut down the rows of data.