Import/export problem with CSV/PHP/MySQL

[Damien]

Hi to all,
I need to design an import/export system. Data comes from a filemaker
pro DB in a big CSV file. Some alterations are made on the data as it is
imported into my mysql table. Data is something like :id,text1html,
text1raw,... .

Problems :
1. Texts can contain html tags, including entities (" etc), so
using only the ';' as separator does not work.
2. Some line are so long that is appears php breaks them down, so I lose
data reading the file.

For 1, I've come across fgetcsv() in recent posts. Works fine for now.

For 2, I can't seem to find a solution. I've tried to detect "broken"
lines and stick them back together but no effect...

Another problem arises when I try to export the data back into CSV, as
it is suposed to be used in Excel. The CR/LF that are in the text are
logically interpreted as new rows in Excel. How can I get around this ?
Is there a special "new line" character that excel would not interpret
as new row ? Should I try another file format ?

Thanks in advance for any idea/hint/link !

BR,
Damien
---
"So you're just taking your caffeine level past the medical definition
of 'stimulant' into the 'poisons' category?"
Bastard Operator From Hell

[R. Rajesh Jeba Anbiah]

Re: Import/export problem with CSV/PHP/MySQL

Damien <truc@bidule.ne t> wrote in message news:<41acddf6$ 0$22473$626a14c e@news.free.fr> ...[color=blue]
> Hi to all,
> I need to design an import/export system. Data comes from a filemaker
> pro DB in a big CSV file. Some alterations are made on the data as it is
> imported into my mysql table. Data is something like :id,text1html,
> text1raw,... .
>
> Problems :
> 1. Texts can contain html tags, including entities (&quot; etc), so
> using only the ';' as separator does not work.[/color]

Usually, CSV should be comma separated.
[color=blue]
> 2. Some line are so long that is appears php breaks them down, so I lose
> data reading the file.[/color]

For CSV -> MySQL import use LOAD DATA INFILE
<http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html> comment #4964

For MySQL -> CSV, OUTFILE
[color=blue]
> Another problem arises when I try to export the data back into CSV, as
> it is suposed to be used in Excel. The CR/LF that are in the text are
> logically interpreted as new rows in Excel. How can I get around this ?
> Is there a special "new line" character that excel would not interpret
> as new row ?[/color]

You may want to look at this usernote to understand CSV format
<http://in.php.net/fgetcsv#14788>

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

[Damien]

Re: Import/export problem with CSV/PHP/MySQL

R. Rajesh Jeba Anbiah a écrit :
[color=blue]
> Usually, CSV should be comma separated.[/color]

Except when the data provider is French and no much competent than me ;o)
[color=blue]
>
>[color=green]
>>2. Some line are so long that is appears php breaks them down, so I lose
>>data reading the file.[/color]
>
>
> For CSV -> MySQL import use LOAD DATA INFILE
> <http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html> comment #4964
>
> For MySQL -> CSV, OUTFILE[/color]

Thanks for the tip, I didn't even think about this function...

I actually found out that I had set a too low limit for the line length
in fgetcsv()... D'oh...

(snip)
[color=blue]
> You may want to look at this usernote to understand CSV format
> <http://in.php.net/fgetcsv#14788>
>[/color]

I had read these comments in another post, and it didn't work : excel
displays a "square" character. Until I found that the "square"
disappears when you set the cell format to "wrap text"...

Thanks for the links, that's been very helpfull !

BR,
Damien
---
"Disk that's so old it's got marks from Noah's screwdriver on the side."
BOFH