PHP PDO Import CSV file into MySQL Database

jmyrtle · January 22, 2020, 7:41pm

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:

<?php

include('nav/head.php');

?>

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

<head>

  <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>
	<br>
	<form action="api/import.php" method="post">
		<div class="form-group">
			<input type="file" id="file" name="file" accept=".csv, .xlsx" autocomplete="off" />
		</div>				
			<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">							
	</form>


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

      </div>
      <!-- End of Main Content -->

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

</html>

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

<?php

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?

rpkamp · January 22, 2020, 9:25pm

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

jmyrtle · January 22, 2020, 9:28pm

@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.

rpkamp · January 22, 2020, 9:36pm

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:

Make it work
Make it pretty
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

Open the CSV file
Loop through all lines in the CSV file

Now you come up with more steps

jmyrtle · January 22, 2020, 9:37pm

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

rpkamp · January 22, 2020, 9:38pm

Sorry, I meant CSV

All those stupid TLAs

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

jmyrtle · January 22, 2020, 9:42pm

@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?

rpkamp · January 22, 2020, 9:45pm

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?

jmyrtle · January 22, 2020, 9:46pm

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

And yes, that is Excel.

rpkamp · January 22, 2020, 9:51pm

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.

jmyrtle · January 22, 2020, 9:54pm

Well, if I were importing users, I would:

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

rpkamp · January 23, 2020, 6:54am

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

Basically the outline is:

Open the CSV file
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
Done

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

droopsnoot · January 23, 2020, 9:44am

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.

jmyrtle · January 23, 2020, 12:53pm

@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

droopsnoot · January 23, 2020, 6:08pm

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

jmyrtle · January 23, 2020, 6:13pm

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

droopsnoot · January 23, 2020, 6:16pm

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.

jmyrtle · January 23, 2020, 6:19pm

@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.

droopsnoot · January 23, 2020, 6:22pm

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”?

rpkamp · January 23, 2020, 7:07pm

Cool. So Google it