Data Import Tips to a PHP System
Data is a critical component of any software system. Whether it is creating simple dropdown menus to the complex migration of thousands of records from legacy systems. At Endertech, we understand that an effective import process is key to integrating data into a new system.
Tips for Importing Data Effectively
Using CSVs for Data Import
Here are some essential tips to ensure a smooth data import process:
Make Data Clean and simple
One of the best practices in data importation is to keep the data clean and straightforward. We prefer using CSV (Comma-Separated Values) files because they are both easy to use and human-readable.
Use Clear and Consistent Naming Conventions
When importing data, it is crucial to use clear and consistent naming conventions. This practice helps with readability of the data. For instance, naming columns and identifiers helps the user know exactly what is being imported.
Example of Data Structure
Color Table
Name |
Red |
Blue |
Green |
Model Table
Name |
Honda |
Volvo |
Tesla |
Vehicle Table
name | model | color |
CR-V | 1 | 1 |
CR-V | 1 | 2 |
CR-V | 1 | 3 |
Pilot | 1 | 1 |
Pilot | 1 | 2 |
Pilot | 1 | 3 |
In this example we are creating a list of vehicles. To properly import the “Vehicle” table,we first need to import the “Color” and “Model” tables. CSV files makes this process straightforward and ensures that each piece of data is easily mapped to their respective fields.
Simple Implementation of the Import Process in PHP using headers
public function setColorFromCSV($csvFile)
{
$headers = [];
$isHeader = true;
if (($handle = fopen($csvFile, "r")) !== FALSE) {
while (($row = fgetcsv($handle)) !== FALSE) {
if ($isHeader) {
$headers = array_flip($row);
$isHeader = false;
continue;
}
if (isset($row[$headers['name']])) {
$color = new Color();
$color->setName($row[$headers['name']]);
$this->em->persist($color);
}
}
}
}
In this example, we flip the headers to match the column names in the CSV file. This is another reason why clear naming conventions are useful. By naming everything accurately, we ensure that the data import process is smooth and error-free.
Importing relational data with “getReference”
public function setVehicleFromCSV($csvFile)
{
$headers = [];
$isHeader = true;
if (($handle = fopen($csvFile, "r")) !== FALSE) {
while (($row = fgetcsv($handle)) !== FALSE) {
if ($isHeader) {
$headers = array_flip($row);
$isHeader = false;
continue;
}
if (isset($row[$headers['Name']])) {
/* @var ColorRepository $colorRepository */
$colorRepository = $this->em->getRepository(Color::class);
$color = $colorRepository->findOneBy(['id' => $row[$header['color']]]);
/* @var ModelRepository $modelRepository */
$modelRepository = $this->em->getRepository(Model::class);
$model = $modelRepository->findOneBy(['id' => $row[$header['model']]]);
$vehicle = new Vehicle();
$vehicle->setName($row[$headers['name']]);
$vehicle->setModel($model);
$vehicle->setColor($color);
$this->em->persist($vehicle);
}
}
}
}
public function setVehicleFromCSV($csvFile)
{
$headers = [];
$isHeader = true;
if (($handle = fopen($csvFile, "r")) !== FALSE) {
while (($row = fgetcsv($handle)) !== FALSE) {
if ($isHeader) {
$headers = array_flip($row);
$isHeader = false;
continue;
}
if (isset($row[$headers['Name']])) {
$color = $this->em->getReference(Color::class, $row[$header['color']]);
$model = $this->em->getReference(Model::class, $row[$header['model']]);
$vehicle = new Vehicle();
$vehicle->setName($row[$headers['name']]);
$vehicle->setModel($model);
$vehicle->setColor($color);
$this->em->persist($vehicle);
}
}
}
}
When importing thousands of records, it is important to take into account efficiency. In the above example, we use “getReference” to establish an association with the entity's identifier, which is more efficient than querying the database multiple times. This method ensures that the import process remains efficient even with large datasets.
For reference: https://www.doctrine-project.org/projects/doctrine-orm/en/3.2/reference/advanced-configuration.html
Error Handling
There are many ways to go about this depending on your situation. A system can have the whole system shut down if it fails or you can continue the process just log which rows are failing.
These are two examples of some error handling:
// Example 1: 204 Response
if ($row[$header['name']] == ''
|| $row[$header['model']] == ''
|| $row[$header['color']] == ''
) {
$message = "Missing fields";
return new Response($message, 204);
}
// Example 2: Continue but display row
if ($row[$header['name']] == ''
|| $row[$header['model']] == ''
|| $row[$header['color']] == ''
) {
$errors[] = 'Missing fields in row ' . implode(',', $row);
continue;
}
Conclusion
Data importation is an essential aspect of integrating new systems and enhancing functionality. By keeping data clean, simple, and well-structured, and by leveraging the power of CSV files and Symfony’s robust tools, we can ensure a smooth data import process. At Endertech, we are committed to making data integration seamless and effective.