Laravel and SQLite dot commands

@JohnnyBigodes The error you're encountering is due to the fact that dot commands, such as ".import," are specific to the SQLite command-line interface (CLI) and cannot be executed directly through Laravel's database connection.

However, you can import a CSV file into a SQLite table using Laravel by utilizing its query builder or raw SQL statements. Here's an example of how you can achieve this:

use Illuminate\Support\Facades\DB;

$file = 'path/to/file.csv';
$tableName = 'your_table_name';

$file = fopen($file, 'r');
$columns = fgetcsv($file);

DB::beginTransaction();

try {
    DB::table($tableName)->truncate();

    while (($data = fgetcsv($file)) !== false) {
        $rowData = array_combine($columns, $data);
        DB::table($tableName)->insert($rowData);
    }

    DB::commit();
} catch (\Exception $e) {
    DB::rollback();
    throw $e;
} finally {
    fclose($file);
}

In this example, we're using Laravel's query builder to truncate the table and then insert the data row by row. The array_combine function is used to associate the column names with the corresponding values for each row.

Make sure to replace 'path/to/file.csv' with the actual path to your CSV file and 'your_table_name' with the desired name of your SQLite table.

By following this approach, you can import the data from the CSV file into a SQLite table using Laravel without relying on dot commands.

@jsanwo64 I was hoping that SQLite had something like the "LOAD DATA LOCAL INFILE" of MySQL, but at the end it seems to also import line by line.

I had already tried a solution like the one you provided, but this takes a long time to import, because I want to import CSVs with 7 millions rows and more.

I think I will stay with MySQL for now, because it just works and it is quick.

Thank you for your help.

@JohnnyBigodes Glad i could help. Try the code below hopefully it can help

use Illuminate\Support\Facades\DB;

$file = 'path/to/file.csv';
$tableName = 'your_table_name';
$chunkSize = 1000; // Number of rows to insert at once

$pdo = DB::connection()->getPdo();

// Enable foreign key checks
$pdo->exec('PRAGMA foreign_keys = ON');

// Disable automatic indexing during import for faster performance
$pdo->exec('PRAGMA synchronous = OFF');
$pdo->exec('PRAGMA journal_mode = MEMORY');

// Get the total number of rows in the CSV file
$totalRows = (int) shell_exec("wc -l < '$file'");

$startTime = microtime(true);

// Split the import into chunks for better performance
$chunkCount = ceil($totalRows / $chunkSize);

for ($chunk = 1; $chunk <= $chunkCount; $chunk++) {
    $offset = ($chunk - 1) * $chunkSize;
    
    $importCommand = ".mode csv\n"
                   . ".import '$file' '$tableName' $offset $chunkSize";

    $pdo->exec($importCommand);
    
    $importedRows = ($chunk * $chunkSize <= $totalRows) ? $chunkSize : ($totalRows - (($chunk - 1) * $chunkSize));
    $progress = (($chunk * $chunkSize) / $totalRows) * 100;

    echo "Imported $importedRows rows (Chunk $chunk/$chunkCount) - Progress: $progress%\n";
}

$endTime = microtime(true);
$executionTime = $endTime - $startTime;

echo "Import completed in $executionTime seconds.\n";

This code takes advantage of SQLite's features and optimizations to improve the performance of the import process for large CSV files. It should provide a faster and more efficient way to import the data into your SQLite table.

@JohnnyBigodes SQLite3 does not accept .dot commands, they are part of the CLI program what has to be installed separate on your server. Read more: https://dba.stackexchange.com/a/305205

If that is the case, and is installed, then following will work: https://stackoverflow.com/a/36001304/277632

PS: FYI, you do not need to have sqlite3 installed on Windows server. You can provide the sqlite3.exe with your package, then you can for example do:

        $db = "path/to/database/sqlite.db";
        $sql = "path/to/import.sql";
        exec("C:\path\to\laravel\project\sqlite3.exe ".$db."<".$sql);

As i run Laravel with NativePHP i started a discussion on the GitHub repository. There i wrote some alternatives what might be useful: https://github.com/NativePHP/laravel/discussions/180#discussioncomment-6756246

@osc2nuke I took a look at your Github Repository and something is strange.

I are talking about importing a large CSV file, but the file you are importing is a SQL file.

What are you really doing there? Is it a CSV or a SQL file?

I am asking because they are really different.

EDIT:

I am using Linux so you have first to install "sqlite3":

apt install sqlite3

Now you have to make an "import.sql" file, wich has the dot commands you need to import a csv file:

.mode csv
.import <path-to-you-csv-file> <tablename> 

Now you can run this against the sqlite cli:

sqlite3 <path-to-sqlite-database-file> < import.sql