Difference between #108 and #109 of
Yii v2 snippet guide III

Revision #109 has been created by rackycz on Jul 17, 2023, 11:35:43 AM with the memo:

Migration CSV
« previous (#108) next (#110) »

Changes

Title unchanged

Yii v2 snippet guide III

Category unchanged

Tutorials

Yii version unchanged

2.0

Tags unchanged

tutorial,yii2,beginer

Content changed

[...]
For Java there are [Java JDBC](http://www.hxtt.com/access.html), [Jackcess](https://jackcess.sourceforge.io/) and [Ucanaccess](http://ucanaccess.sourceforge.net/site.html).

**And how about Docker ?**
As far as I know you cannot run Windows images under Linux so you will not be able to use the ODBC-advantage of Windows in this case.
You can use Linux images under Windows, but I think there is no way how to access the ODBC drivers from virtual Linux. You would have to try it, I haven't tested it yet.
 
 
**Migration batch insert csv**
 
---
 
 
If you want to import CSV into your DB in YIi2 migrations, you can create this "migration base class" and use it as a parent of your actuall migration.
 
 
```php
 
<?php
 
 
namespace app\components;
 
 
use yii\db\Migration;
 
 
class BaseMigration extends Migration
 
{
 
    /**
 
     * @param $filename Example: DIR_ROOT . DIRECTORY_SEPARATOR . "file.csv"
 
     * @param $table The target table name
 
     * @param $csvToSqlColMapping [csvColName => sqlColName] (if $containsHeaderRow = true) or [csvColIndex => sqlColName] (if $containsHeaderRow = false)
 
     * @param bool $containsHeaderRow If the header with CSV col names is present
 
     * @param int $batchSize How many rows will be inserted in each batch
 
     * @throws Exception
 
     */
 
    public function batchInsertCsv($filename, $table, $csvToSqlColMapping, $containsHeaderRow = false, $batchSize = 10000, $separator = ';')
 
    {
 
        if (!file_exists($filename)) {
 
            throw new \Exception("File " . $filename . " not found");
 
        }
 
 
        // If you see number 1 in first inserted row and column, most likely BOM causes this.
 
        // Some Textfiles begin with 239 187 191 (EF BB BF in hex)
 
        // bite order mark https://en.wikipedia.org/wiki/Byte_order_mark
 
        // Let's trim it on the first row.
 
        $bom = pack('H*', 'EFBBBF');
 
 
        $handle = fopen($filename, "r");
 
        $lineNumber = 1;
 
        $header = [];
 
        $rows = [];
 
        $sqlColNames = array_values($csvToSqlColMapping);
 
        $batch = 0;
 
 
        if ($containsHeaderRow) {
 
            if (($raw_string = fgets($handle)) !== false) {
 
                $header = str_getcsv(trim($raw_string, $bom), $separator);
 
            }
 
        }
 
 
        // Iterate over every line of the file
 
        while (($raw_string = fgets($handle)) !== false) {
 
            $dataArray = str_getcsv(trim($raw_string, $bom), $separator);
 
 
            if ($containsHeaderRow) {
 
                $dataArray = array_combine($header, $dataArray);
 
            }
 
 
            $tmp = [];
 
            foreach ($csvToSqlColMapping as $csvCol => $sqlCol) {
 
                $tmp[] = trim($dataArray[$csvCol]);
 
            }
 
            $rows[] = $tmp;
 
 
            $lineNumber++;
 
            $batch++;
 
 
            if ($batch >= $batchSize) {
 
                $this->batchInsert($table, $sqlColNames, $rows);
 
                $rows = [];
 
                $batch = 0;
 
            }
 
        }
 
        fclose($handle);
 
 
        $this->batchInsert($table, $sqlColNames, $rows);
 
    }
 
}
 
```
 
8 0
4 followers
Viewed: 191 040 times
Version: 2.0
Category: Tutorials
Tags: beginer, tutorial, yii2
Written by: rackycz
Last updated by: rackycz
Created on: Jan 21, 2021
Last updated: a year ago
Update Article
Revert to #108 | Revert to #109

Revisions

View all history