CSV: 1 of 5 $vars has paragraphs and ""; import confused

sherry

Hello.

I'm creating a script that creates a CSV file for importing into a spreadsheet into cells 'A' thru 'E'.

For example, the script writes 5 $vars to the CSV.txt file. Each $var is separated by a delimiter that should not be found in the $var's value, e.g. '+'.

One var, '$var5' is a text field that is essentially 2-3 paragraphs long, thereby each paragraph is separated by a carriage return. Also, one of those paragraphs contains double quotes ("...").

When I load the file into the spreadsheet and set the delimiter for each $var, it's fine until it reaches '$var5' containing the paragraphs. The entire '$var5' is not put into the 'next' column (cell 'E'); only the 1st paragraph is. Once it hits the 2nd paragraph, it's treated like a new record, thereby putting paragraph #2 just below and therefore into cell 'A' instead.

I can't change the " " contained in the text of the var to say, single quotes, because it's part of a "%s" substitution, and causes an error otherwise, probably because the value of '%s' is a phrase vs. a single word.

Also, the entire 'record' containing the $vars is contained in a 'here doc'* because that was the only way I could get a clean paragraph between records. When I used e.g. '\n' &/or '\r', a ''" showed up at the end of the record.

~~~~ Example~~ ~~~~~
* $csvrecord = <<<EOL
$var1+$var2+$var3+$var4+$var5

EOL;


I haven't found in CSV references, info regarding this case of a var containing both paragraphs and double quotes, yet...


Thanks in advance.

P.S. PHP4

NogDog

In a CSV file, any field that contains any commas, double quotes, and/or line breaks must be double-quoted and have any double quotes doubled up. So this field...

I said, "This is a test."
The end.

...would become this as one CSV field...

"I said, ""This is a test.""
The end."

sherry

Thanks for your reply, NogDog.

I have to do some more testing on this. For some reason, Excel (on XP machine) is not interpreting the double-quotes as described.

I put your example into a .txt file along with 2 other fields, using '+' as the delimiter. The format below is similar to the output of the text file generated from a php script when the data was listed as $var1+$var2+$var3+$var4+$var5, where $var5 was similar to your original example. In the .txt file, the second paragraph of $var5 dropped down to the 2nd line in the same way shown below.

For the example below, when the 3rd field was changed in the .txt file using the additional quotes needed for the CSV format when there are line breaks, quoted text, etc...

abc+2+"I said, ""This is a test.""
The end."

in Excel, it became...

~~~~~~~~~~~~~~~~~~~~
A	B	C
abc	2	I said, "This is a test."
The end."
~~~~~~~~~~~~~~~~~~~~

As shown, Excel seems to properly interpret the double-quotes in the 1st sentence (notice the 1st one and 1 pair from the doubled-up quotes are gone), but leaves an unmatched double-quote in the 2nd sentence since it is not interpreted to match the interpretation of the 1st double-quote at the beginning of the 1st sentence of cell 'C'.

In essence, when it hits the carriage return at the end of the 1st sentence of cell 'C', it stops, then handles the 2nd sentence separately, creating an orphaned double-quote as well as adding that 2nd sentence to cell 'A'.

This is similar to the type of result I was getting from my original example for $var5, thereby prompting the starting of this thread.

If you have more thoughts on this, please let me know.

Again, thanks in advance.

leatherback

Why don't you just use [man]fputcsv[/man]

sherry

Thanks, leatherback.

My understanding is that's only for PHP5.

leatherback

OK, that makes sense 🙂

Have you however looked at the examples below it?

sherry

No, but I'll take a look to see what I can extract from it.

Thanks.

NogDog

OK, here's something I found that seems to work: use chr(10) for the newlines within a cell. You could use preg_replace():

// replace LF or CRLF with ASCII character #10:
$cr = chr(10);
$text = preg_replace('/\r?\n/', $cr, $text);

sherry

Thanks for your reply, NogDog.

I tried your suggestion and in place of the paragraph marks, the 'preg_replace()' put a ''"; one per carriage return (paragraph mark). In the CSV (.txt) file, it appeared as 1 paragraph (a good sign), but when it was being imported into Excel, each ''" was interpreted as a carriage return, thus formatting as mentioned in the 'Code' section in my earlier (2nd) post.

I like your suggestion, though, for formatting of the .txt file since it does compress the output instead of the blank lines due to the breaks between paragraphs.

Well, if you have other ideas, I'm very open to them, of course...🙂

Thanks again and thanks in advance.