PHP PDO Import CSV file into MySQL Database

I am trying to figure out how to add functionality to import a CSV file (and XLSX file if possible) into a MySQL database using PHP PDO. I’ve looked up different methods on how to do it and none of them work for me even after I customize the code for my database and preferences.

I have an import form webpage with this code:




<!DOCTYPE html>
<html lang="en">


  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">

  <title>CCRP | Home</title>
  <?php include('nav/header.php');  ?>
		<h1 class="h3 mb-2 text-gray-800">Import Members List</h1>
	<form action="api/import.php" method="post">
		<div class="form-group">
			<input type="file" id="file" name="file" accept=".csv, .xlsx" autocomplete="off" />
			<input type="submit" name="btn_upload" class="btn btn-success" value="Upload">
			<input type="submit" name="btn_back" class="btn btn-danger" value="Return to Members List">							

        <!-- /.container-fluid -->

      <!-- End of Main Content -->

	<?php include('nav/footer.php'); ?>


And a PHP file as an API script with this code:


include ('dbconnect.php');

$con = mysqli_connect($host, $user, $pwd);
mysqli_select_db($con, $db);

if (isset($_POST["btn_upload"])) {

if(isset($_POST['btn_back'])) {
	header("Location: ../members_list.php");


I have no idea where to start building functionality other than to include my database connector (which I have already done).

Here is what the import form looks like:


What do I do to build this functionality?

Don’t dive in head first. First come up with a possible approach (high level) of how this could be solved and then implement that step by step.

If you want we can go through it together. But you have to kick it off with an idea for an approach :slightly_smiling_face:

@rpkamp I’m not exactly sure of an approach… What do you suggest? And going through this together would be excellent since I’m fairly new to this.

Okay, so get the ball rolling the first thing I’d decide is to start with CSV only. Excel may be a nice addition later, but the three stages of coding are:

  1. Make it work
  2. Make it pretty
  3. Make it fast

Excel would be part of step 2. Once we have a working solution we can check if we can generalise for more inputs.

As for an approach, the first two steps would be

  1. Open the CSV file
  2. Loop through all lines in the CSV file

Now you come up with more steps :slightly_smiling_face:

1 Like

@rpkamp What CSS file? I am not using one.

Sorry, I meant CSV

All those stupid TLAs :joy:

Edit: I’ve updated my previous post to say CSV instead of CSS

@rpkamp Ohh! Ok.

Well, I already created a demo.csv file. It only has 1 record for now, but I can add more if needed.

Here is a simple screenshot.


Also, my precinct field defaults to a date and it shouldn’t be doing that at all. I’ll type in 02-03 and it changes to 3-Feb. I change the field type to text, but it keeps reverting back to 3-Feb each time the document is opened?

That’s a problem of the program you’re using there (excel?) trying to be smart. Usually when you open a CSV you get some options to indicate what type each column should be. Maybe that helps?

Anyway, have you figured out the next steps of the approach yet?

@rpkamp No, not really. I guess it would be to figure out a way to import it, right?

And yes, that is Excel.

Well yes. Importing it is the goal. But what steps need to take to get to get goal?

You seem to be doing this:

Certainly there are more steps in between. What are they? (rhetorical question when it comes to the owl)

If it helps, imagine you need to import users from a piece of paper by entering it into a form. What are the steps there? The steps for an automated import are similar.

Well, if I were importing users, I would:

  1. Find out what information I’m looking for, such as the user’s full name, username, password, email, etc.
  2. Build the form with the proper user input that is asking for the information I need (most likely a registration form)
  3. Generate (or write) the PHP code to communicate to the server
  4. Using the code written, add the data to the MySQL table after submitting the form.

Well for automated input it’s the same, but you can skip steps 2 and 3.

Basically the outline is:

  1. Open the CSV file
  2. Loop through all lines in the CSV file. For each line:
    1. Read all columns and store in an array
    2. Put all values in a query
    3. Execute the query
  3. Done

So let’s start with step 1, open a file, do you know how to do that?

Set the column format to “text” instead of letting Excel automatically choose one for you. It’s fairly good at choosing, but irritating for stuff like this. And for the printer serial numbers we used to import that it would usefully change into scientific notation.

@rpkamp In PHP, no. On a computer, yes.

@droopsnoot I did that. But after I save changes and re-open the file, it reverts back to 3-Feb . I see this popup and I click Yes


Ah, of course, you’re saving it as CSV. That will forget all the formatting whenever you save it.

@droopsnoot So what format do I use if CSV alters the data?

Is it altering the data, or just displaying it in a way that you don’t want it to?

If you want to edit a csv file, just use notepad or notepad++, especially if it’s just for testing.

@droopsnoot When I open the Excel CSV, it shows as 3-Feb. So, Excel thinks the value 02-03 is a date rather than text… (which makes sense since it can come out to be 02-03-2020.

However, it needs to be a text value (as you previously mentioned) to prevent the data from being altered.

Yes, but is Excel just displaying it like that, or when you look at the csv file in something else (something that doesn’t randomly change data formats, like notepad) is it actually saving the data as “03-Feb”?

Cool. So Google it :sunglasses: