PHP PDO Import CSV file into MySQL Database

You only have to scroll back a very short way down this forum to find another thread that talks about opening CSV files, and has some sample code on it.

If you are going to insist on using MS excel to open/save the file, use a .txt extension so that the import wizard will run when you try to open it. This will let you define columns to be what you want, not what MS assumes you want. https://support.office.com/en-us/article/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

An alternative is to use a better program, such as Apache’s OpenOffice, which always runs the import wizard when opening a .csv file.

Not to start a flame war, but I think MS has some great desktop apps and can create nice paper media documents. BUT, using desktop apps to create web documents is fraught with gotchas and best avoided.

If a MS desktop app interface resembles the look and feel of a web page, it’s coincidence. As posted previously, best to work with programs suitable for doing web work. (+1 from me for Notepad++ but there are many others to choose from)

This is what Notepad++ shows:

Would fopen work? I found this sample code from W3Schools:

<?php
$myfile = fopen("webdictionary.txt", "r") or die("Unable to open file!");
echo fread($myfile,filesize("webdictionary.txt"));
fclose($myfile);
?>

So, the user would have to use something other than Excel? I thought the majority of people used Excel to open CSV files?

Maybe so. It depends on how many problems you want to try to solve at the same time. At the moment, Excel has something to do with the problem. It makes sense to me to let that aside for now and put together a PoC (Proof Of Concept). Then maybe a MVP (Minimum Viable Product) before getting to that problem.

In other words, get your script to work in a way known to work before adding complexity. It’s great to have an idea of what direction you want to go and what the goals are, but don’t let side problems get in the way of progress.

I see. Well, with @rpkamp’s help, I’m trying to figure out code to import the file and add the data to the database.

Would the sample code I posted in my previous response work as a starting point?

Well, fopen is a good starting point. However I would look into fgetcsv instead of fread :slightly_smiling_face:

What’s the difference? Better and more secure, I guess?

So, what about this?

<?php

$file = fopen("demo.csv","r");

print_r(fgetcsv($file));

fclose($file);

?>

I created another folder in my htdocs directory(because I use XAMPP) so I can see what’s happening and it shows the following array:

Array ( [0] => Name [1] => Residential Address [2] => Mailing Address [3] => Precinct [4] => Age [5] => Ethnicity [6] => Gender [7] => Party [8] => Race [9] => Phone )

I would probably start with parsing CSV, but only because I like working with data more than the file system. But yes, that’s a good place to start. It can take some guesswork to not paint yourself into a corner, but learning how to work with files is a worthwhile skill no matter how far things go.

EDIT

I don’t know what you did, but anyway
https://www.php.net/manual/en/function.fread.php

fread ( resource $handle , int $length ) : string

https://www.php.net/manual/en/function.fgetcsv.php

fgetcsv ( resource $handle [, int $length = 0 [, string $delimiter = "," [, string $enclosure = '"' [, string $escape = "\\" ]]]] ) : array

One returns a string, the other an array. True, you could parse the string into an array. But why when you can get the array directly?

You make a good point there. Okay, so I use fgetcsv, but why isn’t my data returning in the array? It only shows the header row?

I guess we would need to see the code involved with that “other folder”. I usually start with everything in one file at first and move portions out later. You don’t need to also do this, it works for me for my “pre-alpha” beginning code.

My guess would be the array isn’t being looped through for some reason, maybe choking on an unrecognized line terminator.

I use XAMPP to host my application locally. Because I use XAMPP, there is a folder called htdocs that basically acts like an Apache server.

So, inside the htdocs folder, I have another folder called csv where I am making the csv import to make sure I understand the code as I work through it.

Inside the csv folder, I have the demo.csv file and the index.php file which looks like this:

<!DOCTYPE html>
<html>
<body>

<?php

$file = fopen("demo.csv","r");

print_r(fgetcsv($file));

fclose($file);

?>

</body>
</html>

The output of the index.php file is this:

Array ( [0] => Name [1] => Residential Address [2] => Mailing Address [3] => Precinct [4] => Age [5] => Ethnicity [6] => Gender [7] => Party [8] => Race [9] => Phone )

And I have a row of data in the csv file that isn’t shown in the array. Do I need a while or foreach loop in here?

Yes :slightly_smiling_face:

You know that if you follow the link that @Mittineague posted in post #29, that goes to the PHP documentation for the fgetcsv() function which contains some sample code that shows you exactly what you need to do to read through the entire file?

Are the array indexes the same as the table columns?
Also add more user data, say ten users.

What about this?

<?php

$file = fopen("demo.csv","r");

while(! feof($file))
  {
  print_r(fgetcsv($file));
  }

fclose($file);

?>

I get this array as an output:

Array ( [0] => Name [1] => Residential Address [2] => Mailing Address [3] => Precinct [4] => Age [5] => Ethnicity [6] => Gender [7] => Party [8] => Race [9] => Phone ) Array ( [0] => MYRTLE, JEREMY ALLEN [1] => 1234 ALEXANDER BLVD. RICHMOND, VA 39568 [2] => 1234 ALEXANDER BLVD. RICHMOND, VA 39568 [3] => 02-03 [4] => 22 [5] => NL [6] => M [7] => REP [8] => W [9] => 123-456-7890 )

It’s a good start. I’d change it a little bit:

<?php

$file = fopen("demo.csv","r");

// Read and throw away the header row
fgetcsv($file);

while (! feof($file)) {
  print_r(fgetcsv($file));
}

fclose($file);

So now the next step is to create a query for each row :slightly_smiling_face:

Would this work?

$stmt = $pdo->prepare("INSERT INTO members (name, residential_address, mailing_address, precinct, age, ethnicity, gender, party, race, phone) VALUES (:name, :residential_address, :mailing_address, :precinct, :age, :ethnicity, :gender, :party, :race, :phone)");
$stmt->execute();

You need to pass values to execute()

… or use bindParam(). And don’t forget you can prepare() before the loop (and bindParam() if you go that way) and execute() inside the loop.