Import data from CSV

Need Help
1

Hi all

Looking for the best way to import data regularly into a CakePHP 4 app

In the past I’ve done some basic scripts that take a CSV and massage it into a SQL insert, however I will soon have a need to regularly import data from a seperate system.

I’ve seen a few helpful posts like this one Import excel data in cakephp data base - #3 by jimgwhit but I was wondering if there’s a suggested plug in for this as I’m never a fan of re-inventing the wheel.

Would something like PhpSpreadsheet fit the requirement - any one have any experience or suggestions?

2

use phpsreadsheet
phpoffice/phpspreadsheet - Packagist

3

My experience is do it manually for given case, due different encodings like Windows/Linux (I’m from Poland we have our special characters) and CSV lacks encoding definition, different line endings CR/LF both or single, present or absent of ; at end of the line and end of the file…
I have done imports from ms excel csv and from bank accounts history and every file looks different.

1 Like
4

I work a lot with XML / XSLT in my day job so for the initial import I serialised the CSV and used XSLT stylesheet and lookups for associated data to create SQL inserts for the initial data.

Some sort of plug in to allow even just admin users to more easily import would be nice for me, but a low priority

5

Hello,
I think you have to use phpSpreadsheet into your CakePHP 4 project. This library handles Excel and CSV files well, offering methods to read and write data. scripts that transform CSV or Excel data into structured arrays or CakePHP ORM entities suitable for database insertion. Cron job or use CakePHP shells to schedule regular imports from the separate system.

6

Thanks, it would be infrequent - once a month or so which means fully automating is overkill, but something easier than a manual SQL import is preferred

Regards,
Brian

7

write a command to import the data,

bin/cake bake command ImportData

In the command you can load entity do validation. You can make it as complex or as simple as you want.