WampServer

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

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.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

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

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.3.4 64bit) Aestan Tray Menu 3.2.5.4
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.23>
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 databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

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

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.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons



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