[For beginners] Laravel: Trying to import a CSV file!

2020.10.01

• Web system development

0

17,434

Hello! This is Fukui from the System Development Department!
Today, I'd like to introduce how I imported a CSV file using the Goodby CSV library in Laravel!
*This is my first blog post, so I'm writing it with great excitement...!

By the way, this time I worked on a Laravel project created in the Homestead environment.
The environment is as follows:
PHP 7.4.5
, Laravel 7.14.1
, MySQL 5.7

table of contents

・Preparation
・Installing the Goodby CSV Library
・Creating a CSV import method
・Testing operation
・Summary
・Bonus

Advance preparation

This time, I will create a simple table that only registers the book's "title" and "price."

        Schema::create('books', function (Blueprint $table) { $table->increments('id'); $table->string('title'); $table->integer('price'); $table->timestamps(); });

Here is the view I prepared.

(Yes, it's simple! Sorry!)

With the routing, CSV import method, and model in place, the next step is to install the Goodby CSV library

Installing the Goodby CSV Library

Run the following command to install the Goodby CSV library:

composer require goodbye/csv

Wait until it completes

Once the installation is complete, follow the instructions in the README on Goodby CSV's GitHub and add the following use declaration to the relevant controller:
Goodby CSV GitHub

use Goodby\CSV\Import\Standard\LexerConfig; use Goodby\CSV\Import\Standard\Lexer; use Goodby\CSV\Import\Standard\Interpreter;

Creating a CSV Import Method

The code for the CSV import part is written as follows.
Before saving to the DB, Goodby CSV configuration settings and charset conversion are performed.

    public function importCsv(Request $request) { // Save CSV file $tmpName = mt_rand().".".$request->file('csv')->guessExtension(); // TMP file name $request->file('csv')->move(public_path()."/csv/tmp",$tmpName); $tmpPath = public_path()."/csv/tmp/".$tmpName; // Goodbye CSV config settings $config = new LexerConfig(); $interpreter = new Interpreter(); $lexer = new Lexer($config); // Convert Charset to UTF-8, ignore CSV header line $config->setToCharset("UTF-8"); $config->setFromCharset("sjis-win"); $config->setIgnoreHeaderLine(true); $dataList = []; // Assign values ​​to the $dataList array as a new Observer $interpreter->addObserver(function (array $row) use (&$dataList){ // Get data for each column $dataList[] = $row; }); // Parse CSV data $lexer->parse($tmpPath, $interpreter); // Delete TMP file unlink($tmpPath); // Registration process $count = 0; foreach($dataList as $row){ Book::insert(['title' => $row[0], 'price' => $row[1]]); $count++; } return redirect()->action('ItemsController@book')->with('flash_message', $count . 'Books registered!'); }

Operation check

This time, I will save the following three lines of data as a CSV file. (I love music.)

Select the CSV file and click Upload...

it appears to have been processed successfully and redirected.
Check the DB.

We were able to confirm that the data written in CSV was saved in the database!

summary

I was glad that I was able to successfully save the information written to CSV into the database.
I was a little nervous before starting, but I found it very convenient, as the process from installing the library to registering data took a relatively short time.
In reality, validation checks and exception handling are required, but I would like to use it in situations where I need to register large amounts of data.

bonus

■ Download the CSV template file

By adding the following method, I was able to download the CSV template file

    public function downloadCsv(): object { // Prepare output data $csvHeader = ["Title", "Price"]; // Set output data separated by commas $downloadData = implode(',', $csvHeader); // Excel compatible $downloadData = mb_convert_encoding($downloadData, "SJIS", "UTF-8"); // Create a temporary csv file if (! file_exists(storage_path('csv'))) { $bool = mkdir(storage_path('csv')); // Throw an exception if directory creation fails if (! $bool) { throw new \Exception("Unable to create directory."); } } $name = 'book.csv'; $pathToFile = storage_path('csv/' . $name); // Create a CSV file if (! file_put_contents($pathToFile, $downloadData)) { throw new \Exception("Failed to write file."); } // Download response return response()->download($pathToFile, $name)->deleteFileAfterSend(true); }

■ Edit csv (VScode plugin)

I added this during this project. It makes CSV files much easier to view in VScode

Edit csv Marketplace

lastly

I am a member of the system development service site "SEKARAKU Lab."
Beyond offers a one-stop service for everything from server design and construction to operation, so if you have any problems with server-side development, please feel free to contact us.
SEKARAKU Lab: [https://sekarakulab.beyondjapan.com/](https://sekarakulab.beyondjapan.com/)

That's all for today!
Thank you for reading until the end!

If you found this article useful, please click [Like]!

0

Loading...

0

17,434