Import excel data in cakephp data base

Need Help
1

how to import excel data into a database in cakephp?

2

Is this for user-side functionality, or a one-time load that you as a developer need to do to initialize things?

1 Like
3

Just write a function that will import a csv with fields you need. Not a cake example but similar and you should get the idea:

    public function incsv()
    {
        $file = 'C:\mydocs\incsv.csv';
        $content = file($file);
        $array = array();

        for ($i = 1; $i < count($content); $i++) {
            $line = explode(',', $content[$i]);
            for ($j = 0; $j < count($line); $j++) {
                $array[$i][$j + 1] = $line[$j];
            }
        }

        $k = count($array) + 1;
        for ($i = 1; $i < $k; $i++) {
            $tdate = new \DateTime($array[$i][2]);
            $ndate = $tdate->format('Y-m-d');
            $descraw = $array[$i][8];
            $descspace = preg_replace('/[^a-z\d ]/i', '', $descraw);
            $desc = preg_replace('/^\s+|\s+$|\s+(?=\s)/', '', $descspace);

            $amt = $array[$i][3];
            $namt = number_format($amt, 2, '.', '');
            if ($namt < 0) {
                $wd = $namt * -1;
                $dep = 0;
                } else {
                $dep = $namt;
            }
            $maxid = $this->Check->getMaxid();
            $checkid = $maxid + 1;

            $data = [
                'checkid' => $checkid,
                'transdate' => $ndate,
                'transdescribe' => $desc,
                'widthdraw' => $wd,
                'deposit' => $dep
            ];
            $this->Check->insertCsv($data);
        }
        $this->Check->checkRecalc();
        Url::redirect('check/index');
    }

I did not need to use all fields, so just got the needed ones.

To achieve something like:

FHvPLIS.jpg1215×340

Besides learning cake I would highly advise you to study and learn various PHP methods and functions. Cake is a PHP framework.

And the above is just an example you need to figure out your own routine that works in your case.

1 Like
4

Here’s another similar example I used for a one-off import. The data was tab-delimited.

I did simple error collection so I could debug and clean the data if needed. I watched for rows that had an unexpected number of columns, and for failed saves.

    public function import()
    {
        $file = new File(TMP . 'source.tab');
        $content = $file->read();
        $lines = explode("\r", $content);

        $keys = [
            'information','owner','pass','product','sn','version','category','source','choose','cd_archive'
        ];
        $tab = (chr(9));
        $error = [];
        $accum = [];
        foreach ($lines as $line) {
            $line = utf8_encode($line);
            $values = explode($tab, $line);
            if(count($values) != count($keys)) {
                $error[] = $values;
            } else {
                $data = array_combine($keys, $values);
                $entity = new Entry($data);
                if($this->Entries->save($entity)){
                    $accum[] = $entity;
                } else {
                    $error[] = $data;
                }
            }
        }
        $this->set(compact('error', 'accum'));
    }
2 Likes
5

thanks for your help, I found a solution with PHPexcel, I will also test your example