1

I'm fetching values from a database and inserting them info a csv file to be downloaded and imported into outlook.

I'm using this code

// output headers so that the file is downloaded rather than displayed
$today = date("m.d.y");
header('Content-Type: text/csv;');
header('Content-Disposition: attachment; filename='.$today.'-allowners.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, array('firstname', 'lastname', 'email'));

// fetch the data
$rows = mysql_query('SELECT first_name, last_name, email FROM owners');

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

And I'm getting weird results, first the column titles don't have delimiter, secondly not all fields from the database get delimited with ' " '. The output goes like this :

firstname,lastname,email
" aaaaa"," bbbbb",0000
" aaaaa"," bbbbb"," 0000"
"aaaaa ",bbbbb,000@00000.com
 aaaaa,bbbbb,000.00.0@00000.com
" aaaaa"," bbbbb"," 000.00000@00000.com"
" aaaaa"," bbbbb"," 000000@000000.com"

And when trying to import into outlook it's giving me an error stating that the file is invalid or in use by another aplication or process.

---------------Solution---------------

The problem was the linebreak ending of the file created with php running on linux to be opened in a windows system, this doesn't allways cause trouble in some software, but since it's MS Outlook they seem pretty strict. I tested opening the file with excel and Saved as CSV overwriting the existing file and it worked without a problem.

Solution can be found in the following url of an answer to other question.

http://stackoverflow.com/a/12723639/2633609
flynn
  • 37
  • 7
  • It doesn't look like the output you have there is invalid. The quotes are missing from fields that don't need quotes, but all the delimiters (') are there. Is it possible that the file isn't closed at the end? – Rob Baillie Dec 05 '13 at 13:09
  • The documenation provides an example stating that the enclosures (the quotes) will only be applied where they are needed. http://php.net/manual/en/function.fputcsv.php – Rob Baillie Dec 05 '13 at 13:10
  • The column titles __do__ have a delimiter, they have a `,` as the delimiter, which is the default for `fputcsv()` – Mark Baker Dec 05 '13 at 13:15
  • The provided example output loads correctly in Excel 2003 – Rob Baillie Dec 05 '13 at 13:17
  • Have you tried opening the outputted file in a text editor, pasting it into a new file and seeing if that opens? I know it's a long-shot, but can you provide the actual output? – Rob Baillie Dec 05 '13 at 13:20
  • Yes I've tried pasting it into another file, other office versions, other windows versions but still no luck the error states the following - A file error has occurred in the translator Comma Separated Values ​​(Windows) (while initializing a translator to build a field map) values​​. Outlook could not get the data file "C: \ .... \ mailling.csv". Check you have the correct file, if you have permission to open it, and if he is not open in another program. – flynn Dec 05 '13 at 15:46
  • Found the solution, it's because linebreaks are made different from linux to windows, so a conversion as to be made, I'll re-edit the question with the solution. – flynn Dec 05 '13 at 16:07

2 Answers2

0

The enclosures are not added because they're not needed. All values that are enclosed share the same trait: they have leading space.

Keep in mind that CSV is a very poorly defined format. Just about anything can be CSV. Delimiters, enclosures and escape characters are not well defined.

As for the error you're getting. Maybe Excel wants semi-colon separated files? Tab separated also seems to work sometimes. This depends on your language/regional settings.

Halcyon
  • 57,230
  • 10
  • 89
  • 128
0

In the example output you've provided you don't have any formatting issues:

  • The fields that do not need enclosures (double-quotes) do not have double-quotes
  • The fields that DO need enclosures, have them
  • All fields in all rows have the required delimiter (comma)

The example given is a valid CSV output and in line with the documentation here: php.net/manual/en/function.fputcsv.php

The strongest suspicion is that the file isn't properly closed with an fclose at the end of your script.

Rob Baillie
  • 3,436
  • 2
  • 20
  • 34