Apparent EOL Issue with PHP/MySQL
Posted by: cgcarpenter (---.dsl.krvl.hctcnetworks.net)
Date: November 13, 2019 09:31PM

I have installed WAMP on a Windows 2008 R2 server. Running Apache 2.4.29, PHP 7.2.18, MySQL 5.7.26. Copied PHP scripts from a Linux server. Have successfully gotten a couple to work, including connecting to MySQL and MS SQL databases.

The problem I am having is with importing a CSV file. Overcame the security issue. I can create the file using this command:

$FileOut = $row->sEmployeeID.",".$row->sLastName.",". $row->sFirstName.",". $row->sTitle.",".$newHireDate."\r\n";

When I try to import the file into the MySQL table using:

$query="load data infile 'C:/wamp64/tmp/PicnicEmp.csv' into table PicnicEmp fields terminated by ',' lines terminated by '\r\n'";

it fails.

Here's where it gets weird. By printing the query I can see that the query is:

load data infile 'C:/wamp64/tmp/PicnicEmp.csv' into table PicnicEmp fields terminated by ',' lines terminated by ' '

Notice the lack of the \r\n.

In phpMyAdmin, if I run the query as shown above, it fails. Get a #1261 - Row 1 doesn't contain data for all columns which isn't true.

If I edit the statement and reinsert the \r\n, it works perfectly. It appears to me to be a Windows issue but I can't find any solutions anywhere. Any one have any suggestions?

Carl Carpenter
Director, Information Services
Hill Country MHDD Centers

Options: ReplyQuote
Re: Apparent EOL Issue with PHP/MySQL
Posted by: Otomatic (Moderator)
Date: November 13, 2019 10:02PM

Hi,

The line terminations are different between Linux and Windows and this is intrinsic to both systems.

A CSV file generated from Linux will systematically have all the line ends by \n and the same file generated under Windows will systematically have the line ends by \r\n

You must use a text editor to replace all line ends \n (Also named Unix) with line ends \r\n (Also named DOS or Windows).
Text editors like UltraEdit or Notepad++ do this very well.

------------------------------------------------------------------------------------------------------------
Wampserver 3.2.0 32 bit - Apache 2.4.41 - PHP 7.4.0/7.3.12/..../5.6.40 - MySQL 5.7.28 - MariaDB 10.4.11
Wampserver 3.2.0 64 bit - Apache 2.4.41 - PHP 7.4.0/7.3.12/..../5.6.40 - MySQL 5.7.28/8.0.18 - MariaDB 10.4.11
PhPMyadmin 4.9.2 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: Apparent EOL Issue with PHP/MySQL
Posted by: cgcarpenter (---.dsl.krvl.hctcnetworks.net)
Date: November 15, 2019 12:31AM

The file is being created on a Windows server and being read on a Windows server. The \r\n tag is included when creating the file.

I just experimented with the code and reversed the single and double quotes so that the \r\n in both statements had double quotes. Still didn't work but the display of the query showed the \r\n.

load data infile "C:/wamp64/tmp/PicnicEmp.csv" into table PicnicEmp fields terminated by "," lines terminated by "\r\n"

Copied that line and pasted it into the SQL function in phpMyAdmin and it executed.

Carl Carpenter
Director, Information Services
Hill Country MHDD Centers

Options: ReplyQuote
Re: Apparent EOL Issue with PHP/MySQL
Posted by: RiggsFolly (Moderator)
Date: November 15, 2019 10:49AM

So have you worked out how to do this now? Can we consider this closed, afterall it is not a WAMPServer issue in the first place.

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.1.9 32bit & 64bit)
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.16>

Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your MySQL databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Options: ReplyQuote
Re: Apparent EOL Issue with PHP/MySQL
Posted by: cgcarpenter (---.dsl.krvl.hctcnetworks.net)
Date: November 15, 2019 07:24PM

No, it is not resolved. I was hoping someone else had experienced this problem using WAMP.

Carl Carpenter
Director, Information Services
Hill Country MHDD Centers

Options: ReplyQuote
Re: Apparent EOL Issue with PHP/MySQL
Posted by: Otomatic (Moderator)
Date: November 15, 2019 07:58PM

Hi,

Try to comment (put ; at the beginning of the line) :

secure_file_priv="C:/wamp(64)/tmp"

in my.ini file.

Then Restart Wampserver.

------------------------------------------------------------------------------------------------------------
Wampserver 3.2.0 32 bit - Apache 2.4.41 - PHP 7.4.0/7.3.12/..../5.6.40 - MySQL 5.7.28 - MariaDB 10.4.11
Wampserver 3.2.0 64 bit - Apache 2.4.41 - PHP 7.4.0/7.3.12/..../5.6.40 - MySQL 5.7.28/8.0.18 - MariaDB 10.4.11
PhPMyadmin 4.9.2 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »



Edited 1 time(s). Last edit at 11/15/2019 11:10PM by Otomatic.

Options: ReplyQuote


Sorry, only registered users may post in this forum.