Fputcsv Produces Improper End Of Line (Small Rectangle) In Csv

[newbie3 4]

Hi helpful readers

 

From fputcsv, we keep getting improper end of line in a .csv file view from Notepad. We spent quite a fair bit of time but still to no avail, hope someone could assist, thanks in advance.

 

We execute the following lines:

 

header('Content-Type: text/csv');

header('Content-Disposition: attachment; filename=data.csv');

 

$fs = fopen('php://output', 'wt'); // In addition to 'wt', we have also tried ,'w' and 'wb', no different in output.

$query = "SELECT * FROM table" ;

$result = mysqli_query($dbc, $query) ;

 

while($row = mysqli_fetch_assoc($result)) {

fputcsv($fs, $row, ',');

}

 

Regards

 

Newbie3

[newbie3 4]

Hello

 

Just wonder if anyone has generated a good csv file successfully using php function of fputcsv in Windows environment.

 

By a good csv file we mean there are clear multiple separate lines view in notepad.

 

A bad csv file means there is no separate line view in notepad. What we see is only one line with a very long string of characters. It appears that the small rectangles in between the very long string of characters mark the end of lines.

 

Any comment, assistance is very much appreciated.

 

Regards

 

Newbie3

[HartleySan 826]

I imagine that your issue is a rendering issue in Notepad more than anything. Maybe trying using a more robust text editor than Notepad, and see if you still get the odd boxes for newlines.

 

More importantly, see if you're able to get the CSV data properly from the file. If you can, then I don't think you need to worry about things displaying oddly in Notepad, which is a cruddy text editor to begin with anyway.

[HartleySan 826]

Also, this might shed some light on the issue:

http://cboard.cprogramming.com/c-programming/102498-notepad-doesnt-recognize-new-line-character.html

[newbie3 4]

Hi HartleySan

 

You are great. What we gain most from you is the guidance and the directions you offer on solving the problem. Those are the most valuable. This is not the first time we gain somethings from you.

 

It did not occur to us that being able to read the data correctly from the csv file is more important that how the csv file is being displayed by Notepad. We thought that not being able to display correctly in Notepad means not being able to be read correctly by other applications. You are right, it is about rendering.

 

We tried as you suggested and Microsoft Access is able to read the "bad" csv file successfully. Being able to read correctly by Microsoft Access solves the most parts of our problem. We will try other applications to read the bad csv file.

 

Thank you again and very much appreciated.

 

Regards

 

Newbie3

[newbie3 4]

Hello

 

Although Microsoft Access is able to read the bad csv file successfully, we are not so lucky when we tried to use other off the shelf application to read the bad csv file. It appears as a long string of characters in other applications, and therefore we are not able to use the bad csv as a source to import data into the other applications.

 

Any suggestion or comment is much appreciated.

 

Regards

 

Newbie3

[HartleySan 826]

Did you by chance save the CSV in Notepad with the bad characters?

 

The other possibility is that you're searching for the wrong new line character or have the wrong new line character set for your new lines.

 

I don't know the exact breakdown, but some OSs and text editors use \r (a carriage return) for new lines, while others use \n (a new line/line feed), and still others use a combination of the two with the carriage return coming first (i.e., \r\n).

 

Please check all of those things and then report back.

[newbie3 4]

Hello

 

The following scripts work (good csv file)

 

$fpw = fopen('datab.csv', 'wt');

$query = "SELECT * FROM table" ;

$result = mysqli_query($dbc, $query) ;

while($row = mysqli_fetch_assoc($result)) {

fputcsv($fpw, $row);

}

 

 

The following scripts fail to work ( produces bad csv file)

 

header('Content-Type: text/csv');

header('Content-Disposition: attachment; filename=data.csv');

$fpw = fopen('php://output', 'wt');

$query = "SELECT * FROM table" ;

$result = mysqli_query($dbc, $query) ;

while($row = mysqli_fetch_assoc($result)) {

fputcsv($fpw, $row);

}

 

 

Therefore, the different is between $fpw = fopen('datab.csv', 'wt') which works ; and the 3 lines below (which do not work).

header('Content-Type: text/csv');

header('Content-Disposition: attachment; filename=data.csv');

$fpw = fopen('php://output', 'wt');

 

It appears that it has nothing to do with fputcsv.

 

Regards

 

Newbie3

[HartleySan 826]

Are you telling me that you found a solution? If so, great.

I've never used the php://output stream before, so I'm not sure what kind of odd behaviors that might generate, but if the first solution, which seems simpler, works, why complain?

[newbie3 4]

Hello

 

The first solution works but not good enough. We need to download the good csv file to a local PC but the csv file stays on the web server in the directory where the script is run.

 

We got the second solution working now. The amended script that work is as follows:

 

header('Content-Type: text/csv');

header('Content-Disposition: attachment; filename=data.csv');

$fpw = fopen('datag.csv', 'wt');

$query = "SELECT * FROM table" ;

$result = mysqli_query($dbc, $query) ;

while($row = mysqli_fetch_assoc($result)) {

fputcsv($fpw, $row);

}

readfile ('datag.csv') ;

 

Two lines of the script in the second solution that is working now are

• $fpw = fopen('datag.csv', 'wt'); (Note : changed from $fpw = fopen('php://output', 'wt')
  
• readfile ('datag.csv') ; ( a new line added)
  

 

Thanks for taking time to contribute your opinion.

 

Regards

 

Newbie3