Creating Excel CSV – PHP

  php

Q(Question):

Hi

I am trying to create a MS Excel format CSV but I can’t figure out how
to get the line feed/carriage return/new record working properly.

I am nding each line/record with these characters: "\r\n" but the
Excel file just loads all the field in one row as if it doesn’t
recognise these characters.

These are the headers I am using to create the file:

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment;
filename=production_boards.csv");
header("Pragma: no-cache");
header("Expires: 0");

Any ideas why the file won’t open properly in Excel?

Thanks

Jeremy

A(Answer):

I’m not sure how CSV’s are supposed to be formatted, but you could
always try to each of the following till something works:
"\r\n"
"\r"
"\n"

If nothing works it’d be safe to say that at least that isn’t the problem

–Jeff

Jeremy Langworthy wrote:

Hi

I am trying to create a MS Excel format CSV but I can’t figure out how
to get the line feed/carriage return/new record working properly.

I am nding each line/record with these characters: "\r\n" but the
Excel file just loads all the field in one row as if it doesn’t
recognise these characters.

These are the headers I am using to create the file:

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment;
filename=production_boards.csv");
header("Pragma: no-cache");
header("Expires: 0");

Any ideas why the file won’t open properly in Excel?

Thanks

Jeremy

A(Answer):

oops, throw "\n\r" in there too
Jeff Smick wrote:

I’m not sure how CSV’s are supposed to be formatted, but you could
always try to each of the following till something works:
"\r\n"
"\r"
"\n"

If nothing works it’d be safe to say that at least that isn’t the problem

–Jeff

Jeremy Langworthy wrote:

Hi

I am trying to create a MS Excel format CSV but I can’t figure out how
to get the line feed/carriage return/new record working properly.

I am nding each line/record with these characters: "\r\n" but the
Excel file just loads all the field in one row as if it doesn’t
recognise these characters.

These are the headers I am using to create the file:

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment;
filename=production_boards.csv");
header("Pragma: no-cache");
header("Expires: 0");

Any ideas why the file won’t open properly in Excel?

Thanks

Jeremy

A(Answer):

Jeremy Langworthy wrote:

Hi

I am trying to create a MS Excel format CSV but I can’t figure out how
to get the line feed/carriage return/new record working properly.

I am nding each line/record with these characters: "\r\n" but the
Excel file just loads all the field in one row as if it doesn’t
recognise these characters.

These are the headers I am using to create the file:

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment;
filename=production_boards.csv");
header("Pragma: no-cache");
header("Expires: 0");

Any ideas why the file won’t open properly in Excel?

If the file loads in Excel, then it’s not your headers. How are you
getting the data for each line? Do you have the field delimiter after
each item (comma, sem-colon, or whatever you are using)?

If your are using fwrite with a semi-colon for a delimiter, it should
look something like this:

<?php
$fp = fopen("csv.txt", "a");
fwrite($fp,stripslashes("$name;"));
fwrite($fp,stripslashes("$address;"));
fwrite($fp,stripslashes("$city;"));
fwrite($fp,stripslashes("$state;\n"));
fclose($fp);
}
?>

Then Excel should have no problem handling it.

A(Answer):

JDJones <se******@sprynet.com> wrote in message news:<Ddd%b.406368$xy6.2317200@attbi_s02>…

Jeremy Langworthy wrote:

Hi

I am trying to create a MS Excel format CSV but I can’t figure out how
to get the line feed/carriage return/new record working properly.

I am nding each line/record with these characters: "\r\n" but the
Excel file just loads all the field in one row as if it doesn’t
recognise these characters.

These are the headers I am using to create the file:

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment;
filename=production_boards.csv");
header("Pragma: no-cache");
header("Expires: 0");

Any ideas why the file won’t open properly in Excel?

If the file loads in Excel, then it’s not your headers. How are you
getting the data for each line? Do you have the field delimiter after
each item (comma, sem-colon, or whatever you are using)?

If your are using fwrite with a semi-colon for a delimiter, it should
look something like this:

<?php
$fp = fopen("csv.txt", "a");
fwrite($fp,stripslashes("$name;"));
fwrite($fp,stripslashes("$address;"));
fwrite($fp,stripslashes("$city;"));
fwrite($fp,stripslashes("$state;\n"));
fclose($fp);
}
?>

Then Excel should have no problem handling it.

Thanks JD but I think the problem is a delimiter one. I have tried
every combination of \n and \r and nothing seems to work. Is there
another way I should be delimiting the records so that Excel can read
them?

Thanks Guys

A(Answer):

"Jeremy Langworthy" <gr****@8legs.co.nz> wrote in message
news:c7**************************@posting.google.c om…

JDJones <se******@sprynet.com> wrote in message

news:<Ddd%b.406368$xy6.2317200@attbi_s02>…

Jeremy Langworthy wrote:

Hi

I am trying to create a MS Excel format CSV but I can’t figure out how
to get the line feed/carriage return/new record working properly.

I am nding each line/record with these characters: "\r\n" but the
Excel file just loads all the field in one row as if it doesn’t
recognise these characters.

These are the headers I am using to create the file:

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: attachment;
filename=production_boards.csv");
header("Pragma: no-cache");
header("Expires: 0");

Any ideas why the file won’t open properly in Excel?

If the file loads in Excel, then it’s not your headers. How are you
getting the data for each line? Do you have the field delimiter after
each item (comma, sem-colon, or whatever you are using)?

If your are using fwrite with a semi-colon for a delimiter, it should
look something like this:

<?php
$fp = fopen("csv.txt", "a");
fwrite($fp,stripslashes("$name;"));
fwrite($fp,stripslashes("$address;"));
fwrite($fp,stripslashes("$city;"));
fwrite($fp,stripslashes("$state;\n"));
fclose($fp);
}
?>

Then Excel should have no problem handling it.

Thanks JD but I think the problem is a delimiter one. I have tried
every combination of \n and \r and nothing seems to work. Is there
another way I should be delimiting the records so that Excel can read
them?

Thanks Guys

Jeremy,

Try posting the code you are using to create the .csv file, so we can see if
there is a gotcha in how it is being written.

Also, What o/s are you creating the file under – Windoze, Linux, Unix, OsX
….?

Finally, exactly how is the file getting from the place where it is created
to the innards of Excel? Are you creating it in-situ where it will be opened
by Excel, or are you doing an FTP or similar to move it from producer
directory to consumer directory?

It sounds like a file conversion issue, but we need to know more about the
environment.

Cheers,
Doug

Remove the blots from my address to reply

A(Answer):

Jeremy Langworthy wrote:

I am trying to create a MS Excel format CSV but I can’t figure out how
to get the line feed/carriage return/new record working properly.

I am nding each line/record with these characters: "\r\n" but the
Excel file just loads all the field in one row as if it doesn’t
recognise these characters.
(…)
Any ideas why the file won’t open properly in Excel?

Excel always does that when You directly open a csv file which does not
have a schema.ini.

Open Excel first, then open the csv file via the open dialog — bingo.

Rudi

LEAVE A COMMENT