PHP Format Dates with fgetcsv

PHP
1

I am trying to run an import in a program I’m writing, but the dates are not coming back properly.

In my Excel CSV spreadsheet, the dates are listed in this format under the column Invoice_Date:

07/02/2020

The code I’ve written so far completes the import, however the dates come back as 0000-00-00.

Here is my current import code:

<?php

include('dbconnect.php');

if(isset($_POST['btn_upload'])){
	$filename = $_FILES['file']['tmp_name'];
	$file = fopen($filename,"r");


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

	$stmt = $pdo->prepare("INSERT INTO invoices (Invoice_Number, Customer_Number, Customer_Name, Invoice_Date, Sales_Order_Number, Sales_Amount, Progress_Billing, Extra_Charge_Amount, Tax_Amount, Invoice_Amount, Writeoff_Amount, Paid_Amount, Balance_Due, Use_Tax, Tax_Stat, Salesperson_1, Comm_Rate_1, Salesperson_2, Comm_Rate_2, Salesperson_3, Comm_Rate_3, Deposit_Applied, Sales_Tax_Codes, Sales_Tax_Amounts, Customer_Address1, Customer_Address2, Customer_City, Customer_State, Customer_Zip, Customer_Country) VALUES (:Invoice_Number, :Customer_Number, :Customer_Name, :Invoice_Date, :Sales_Order_Number, :Sales_Amount, :Progress_Billing, :Extra_Charge_Amount, :Tax_Amount, :Invoice_Amount, :Writeoff_Amount, :Paid_Amount, :Balance_Due, :Use_Tax, :Tax_Stat, :Salesperson_1, :Comm_Rate_1, :Salesperson_2, :Comm_Rate_2, :Salesperson_3, :Comm_Rate_3, :Deposit_Applied, :Sales_Tax_Codes, :Sales_Tax_Amounts, :Customer_Address1, :Customer_Address2, :Customer_City, :Customer_State, :Customer_Zip, :Customer_Country)");

	while (! feof($file)) {
  		$row = fgetcsv($file);
  		$stmt->bindParam(':Invoice_Number', $row[0]);
		$stmt->bindParam(':Customer_Number', $row[1]);
		$stmt->bindParam(':Customer_Name', $row[2]);
		$stmt->bindParam(':Invoice_Date', $row[3]);
  		$stmt->bindParam(':Sales_Order_Number', $row[4]);
  		$stmt->bindParam(':Sales_Amount', $row[5]);
  		$stmt->bindParam(':Progress_Billing', $row[6]);
  		$stmt->bindParam(':Extra_Charge_Amount', $row[7]);
  		$stmt->bindParam(':Tax_Amount', $row[8]);
  		$stmt->bindParam(':Invoice_Amount', $row[9]);
  		$stmt->bindParam(':Writeoff_Amount', $row[10]);
  		$stmt->bindParam(':Paid_Amount', $row[11]);
  		$stmt->bindParam(':Balance_Due', $row[12]);
			$stmt->bindParam(':Use_Tax', $row[13]);
			$stmt->bindParam(':Tax_Stat', $row[14]);
			$stmt->bindParam(':Salesperson_1', $row[15]);
			$stmt->bindParam(':Comm_Rate_1', $row[16]);
			$stmt->bindParam(':Salesperson_2', $row[17]);
			$stmt->bindParam(':Comm_Rate_2', $row[18]);
			$stmt->bindParam(':Salesperson_3', $row[19]);
			$stmt->bindParam(':Comm_Rate_3', $row[20]);
			$stmt->bindParam(':Deposit_Applied', $row[21]);
			$stmt->bindParam(':Sales_Tax_Codes', $row[22]);
			$stmt->bindParam(':Sales_Tax_Amounts', $row[23]);
			$stmt->bindParam(':Customer_Address1', $row[24]);
			$stmt->bindParam(':Customer_Address2', $row[25]);
			$stmt->bindParam(':Customer_City', $row[26]);
			$stmt->bindParam(':Customer_State', $row[27]);
			$stmt->bindParam(':Customer_Zip', $row[28]);
			$stmt->bindParam(':Customer_Country', $row[29]);
  		$stmt->execute();
}

	fclose($file);
	header("Location: ../members_list.php");
}

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

?>

I read on StackOverflow that I can make a stored procedure, but I’m not sure how to make one to convert the date properly. What should I do?

2

MySQL will expect entries for a DATE field to be in YYYY-MM-DD format.

3

You can either explode/implode the date value in the php code to get it into the correct format or you can use STR_TO_DATE() in the sql query to produce a date value from the supplied format.

4

How would I set that up?

STR_TO_DATE(str,format);

would it be:

STR_TO_DATE(:Invoice_Date, %Y, %m, %d);

or would implode/explode be easier?

5

You can use the following to parse your date into a PHP DateTime object, then format it as MySQL expects:

\DateTime::createFromFormat('d/m/Y', $row[3])->format('Y-m-d')

Replace the reference to $row[3] in your bindParam() call with the above, and it should work.

The \DateTime::createFromFormat() part accepts a format string and an input string, and creates a DateTime object representing that date. It will optionally do time as well, but you don’t need that here.

The ->format('Y-m-d') part outputs the created DateTime object as a string with the provided format.

6

I get the following error when I replace the code:

Fatal error: 

Uncaught Error: Call to a member function format() on bool in 
C:\xampp\htdocs\cascotax\api\import.php:20 Stack trace: #0 {main} thrown in 
C:\xampp\htdocs\cascotax\api\import.php on line 20
7

Show us the current version of your code?

9

I am having issues when I’m importing a CSV file into my script. All the dates in my spreadsheet are formatted like this:

01/01/2020

When they should be

2020-01-01

My CSV script looks like this:

<?php

include('dbconnect.php');

if(isset($_POST['btn_upload'])){
	$filename = $_FILES['file']['tmp_name'];
	$file = fopen($filename,"r");


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

	$stmt = $pdo->prepare("INSERT INTO invoices (id, Invoice_Number, Customer_Number, Customer_Name, Invoice_Date, Sales_Order_Number, Sales_Amount, Progress_Billing, Extra_Charge_Amount, Tax_Amount, Invoice_Amount, Writeoff_Amount, Paid_Amount, Balance_Due, Use_Tax, Tax_Stat, Salesperson_1, Comm_Rate_1, Salesperson_2, Comm_Rate_2, Salesperson_3, Comm_Rate_3, Deposit_Applied, Sales_Tax_Codes, Sales_Tax_Amounts, Customer_Address1, Customer_Address2, Customer_City, Customer_State, Customer_Zip, Customer_Country) 
    VALUES (:id, :Invoice_Number, :Customer_Number, :Customer_Name, :Invoice_Date, :Sales_Order_Number, :Sales_Amount, :Progress_Billing, :Extra_Charge_Amount, :Tax_Amount, :Invoice_Amount, :Writeoff_Amount, :Paid_Amount, :Balance_Due, :Use_Tax, :Tax_Stat, :Salesperson_1, :Comm_Rate_1, :Salesperson_2, :Comm_Rate_2, :Salesperson_3, :Comm_Rate_3, :Deposit_Applied, :Sales_Tax_Codes, :Sales_Tax_Amounts, :Customer_Address1, :Customer_Address2, :Customer_City, :Customer_State, :Customer_Zip, :Customer_Country)");

	while (! feof($file)) {
  		$row = fgetcsv($file);
			$stmt->bindParam(':id', $row[0]);
			$stmt->bindParam(':Invoice_Number', $row[1]);
		$stmt->bindParam(':Customer_Number', $row[2]);
		$stmt->bindParam(':Customer_Name', $row[3]);
		$stmt->bindParam(':Invoice_Date', $row[4]);
  		$stmt->bindParam(':Sales_Order_Number', $row[5]);
  		$stmt->bindParam(':Sales_Amount', $row[6]);
  		$stmt->bindParam(':Progress_Billing', $row[7]);
  		$stmt->bindParam(':Extra_Charge_Amount', $row[8]);
  		$stmt->bindParam(':Tax_Amount', $row[9]);
  		$stmt->bindParam(':Invoice_Amount', $row[10]);
  		$stmt->bindParam(':Writeoff_Amount', $row[11]);
  		$stmt->bindParam(':Paid_Amount', $row[12]);
  		$stmt->bindParam(':Balance_Due', $row[13]);
			$stmt->bindParam(':Use_Tax', $row[14]);
			$stmt->bindParam(':Tax_Stat', $row[15]);
			$stmt->bindParam(':Salesperson_1', $row[16]);
			$stmt->bindParam(':Comm_Rate_1', $row[17]);
			$stmt->bindParam(':Salesperson_2', $row[18]);
			$stmt->bindParam(':Comm_Rate_2', $row[19]);
			$stmt->bindParam(':Salesperson_3', $row[20]);
			$stmt->bindParam(':Comm_Rate_3', $row[21]);
			$stmt->bindParam(':Deposit_Applied', $row[22]);
			$stmt->bindParam(':Sales_Tax_Codes', $row[23]);
			$stmt->bindParam(':Sales_Tax_Amounts', $row[24]);
			$stmt->bindParam(':Customer_Address1', $row[25]);
			$stmt->bindParam(':Customer_Address2', $row[26]);
			$stmt->bindParam(':Customer_City', $row[27]);
			$stmt->bindParam(':Customer_State', $row[28]);
			$stmt->bindParam(':Customer_Zip', $row[29]);
			$stmt->bindParam(':Customer_Country', $row[30]);
  		$stmt->execute();

}

	fclose($file);
	header("Location: ../invoices_list.php");
}

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

?>

How do I edit this script to make the dates import in the proper format?

10

What format is that? Your definiton is ambiguous.

11

It is formatted as MM-DD-YYYY when it should be YYYY-MM-DD

12

You have already asked this - PHP Format Dates with fgetcsv

The last reply was waiting for your current code to help with an error you were getting.

13

okay, american date format. The DateTime parser should understand it then.
date_format(date_create($input),'Y-m-d')

EDIT: Woops. parameters around the wrong way.

14

Does placement matter or can I place it anywhere?

15

date_format returns a string. So… you can put it anywhere a string would normally go…

16

Could that replace…

$stmt->bindParam(':Invoice_Date', $row[4]);
17

$row[4] is (presumably) a string. So it could replace that, yes.

18

Notice : Undefined variable: input in C:\xampp\htdocs\cascotax\api\import.php on line 21

$stmt->bindParam(':Invoice_Date', date_format(date_create($input),'Y-m-d'));
19

so… replace $input with the thing you’re trying to convert…

(I mean… come on, put a modicum of effort in here, man.)

1 Like
20

I’m trying lol.

I also get this error when it imports:

Fatal error : Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘id’ cannot be null in C:\xampp\htdocs\cascotax\api\import.php:48 Stack trace: #0 C:\xampp\htdocs\cascotax\api\import.php(48): PDOStatement->execute() #1 {main} thrown in C:\xampp\htdocs\cascotax\api\import.php on line 48

Yet the column id is marked NOT NULL? What does this mean?

image
image838×44 6.81 KB

21

well, it looks like one of your CSV rows has a null value in its first column?
Does the CSV happen to have a blank line at the bottom?