[For beginners] Laravel: I tried importing a CSV file!
Hello! This is Fukui from the System Development Department!
This time, I tried importing a CSV file using the Goodby CSV library in Laravel, so I would like to introduce it!
*This is my first blog post, so I'm really sweating as I write this...!
By the way, this time I worked on a Laravel project created under 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
・Operation confirmation
・Summary
・Bonus
Advance preparation
This time, I'm going to prepare a simple table that only registers the "title" and "price" of the book.
table name | books |
---|---|
column | id, title, 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!)
After preparing the routing, CSV import method, and model, the next step is to install the Goodby CSV library.
Installing Goodby CSV library
Run the command below to install the Goodby CSV library.
composer require goodby/csv
Wait until it completes.
Once the installation is complete, follow 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 was written as follows.
Before the DB saving process, Goodby CSV config 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; //Goodby 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 a value 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 . 'You have registered a book! '); }
Operation confirmation
This time, I would like to save the following three lines of data as CSV. (I like music.)
Select the CSV file, click upload, and
the process appears to have been successfully completed and redirected.
Check the DB.
I was able to confirm that the data written in CSV was saved in the DB!
summary
I'm glad that I was able to successfully save the information written to CSV in the DB.
I was a little hesitant before starting work, but I found it very convenient as everything from installing the library to registering can be done in a relatively short time.
In reality, validation checks and exception handling are necessary, but I would like to use it depending on the situation, such as when I want to register a large amount of data.
bonus
■ Download 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); / / Compatible with Excel $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 when directory creation fails if (! $bool) { throw new \Exception("Failed to create directory."); } } $name = 'book.csv' ; $pathToFile = storage_path('csv/' . $name); // Create 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 to take advantage of this work. It makes CSV very easy to view on VScode.
lastly
I have opened the system development service site "SEKARAKU Lab" to which I belong.
Beyond is a one-stop service for everything from server design and construction to operation, so if you have any trouble with server-side development, please feel free to contact us.
SEKARAKU Lab: [https://sekarakulab.beyondjapan.com/](https://sekarakulab.beyondjapan.com/)
That's all for this time!
Thank you for reading to the end!