468,532 Members | 1,703 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,532 developers. It's quick & easy.

Import/export problem with CSV/PHP/MySQL

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
Jul 17 '05 #1
2 12099
Damien <tr**@bidule.net> wrote in message news:<41***********************@news.free.fr>...
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.
Usually, CSV should be comma separated.
2. Some line are so long that is appears php breaks them down, so I lose
data reading the file.
For CSV -> MySQL import use LOAD DATA INFILE
<http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html> comment #4964

For MySQL -> CSV, OUTFILE
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 ?


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/
Jul 17 '05 #2
R. Rajesh Jeba Anbiah a écrit :
Usually, CSV should be comma separated.
Except when the data provider is French and no much competent than me ;o)

2. Some line are so long that is appears php breaks them down, so I lose
data reading the file.

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

For MySQL -> CSV, OUTFILE


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)
You may want to look at this usernote to understand CSV format
<http://in.php.net/fgetcsv#14788>


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
Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Bruce A. Julseth | last post: by
reply views Thread by Vidhya CS | last post: by
2 posts views Thread by Brian Huether | last post: by
4 posts views Thread by news | last post: by
5 posts views Thread by Kajol | last post: by
1 post views Thread by Martin Herrman | last post: by
7 posts views Thread by phillip.s.powell | last post: by
10 posts views Thread by Simon | last post: by
7 posts views Thread by Randy | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.