Forum Moderators: coopster
I use \' for ' and \" for " in excell spreadsheet. I was doing this for php echo outputs. For some reason excell insists on putting quotes around fields.
Example:
This is what it looks like in excell):
+------+----------+
¦ field1 ¦ dimension ¦
+------+----------+
¦ 0001 ¦ 18\" x 3\' ¦
+------+----------+
this is what excell csv looks like does:
field1, dimension
0001,"18\"" x "3\'"
this is what i want it to be:
field1, dimension
0001,18\" x 3\'
I like using excell, but how do I get correct output? Any ideas on better methods would be appreciated. I then use phpMyAdmin upload txt file funtion.
that format works, as Salsa metioned, fgetcsv allows you to set the delimiter which would be " in your case. Though you must also then set the optional length parameter. Make 100% sure the integer used in length is longer than your longest line or you will receive unexpected results.
I often just strip all double, and even single, quotes from the lines but since you actually want some in there your best bet is to have them delimited and escaped, which is what you already have.
Porbably not what you are looking for, though.
Excel needs to put quotes around text/memo fields. It is the only way it knows how to handle carriage returns, line feeds, and other non-CSV friendly text codes.
That is wehre I actually got the \' and \" since php will automatically add those in.
Excell actually handles " and ' fine. Which I just have php addslashes and removeslashes to deal with output.
"e is a decent workaround. I frequently use this for javascript forms and submits when submitting to databases.
The problem with excell still seems to be "comma delimited csv". There is no html code for this. So i cannot use commas. I use ; and then a replace in php code to deal with this.
No pretty solutions for dealin with excell i guess. Thanks for the help everyone.
fgetcsv() is pretty.
Excel content that looks like this:
+---+------------+------------+
¦ 1 ¦ 18\" x 3\' ¦ something1 ¦
+---+------------+------------+
¦ 2 ¦ 19\" x 3\' ¦ something2 ¦
+---+------------+------------+
¦ 3 ¦ 20\" x 3\' ¦ something3 ¦
+---+------------+------------+
Export to cvs produces file.cvs like this (with my excel, anyway):
1,"18\"" x 3\'",something1
2,"19\"" x 3\'",something2
3,"20\"" x 3\'",something3
using fgetcsv() like:
$sql = "INSERT INTO table VALUES";
$fp = fopen('file.cvs',r);
for ($row = 0; $row_array = fgetcsv($fp,1024); ++$row) {
$elements = count($row_array);
$sql .= "(";
for ($i = 0; $i < $elements; $i++) {
if ($elements % ($i-1) == 0) $sql .= "'$row_array[$i]')";
else $sql .= "'$row_array[$i]',";
}
}
$sql = substr($sql,0,-1);
echo $sql;
Produces this:
INSERT INTO table VALUES('1','18\" x 3\'','something1'),('2','19\" x 3\'','something2'),('3','20\" x 3\'','something3')That looks mighty pretty to me. A ready query.
Maybe it won't help when working with phpMyAdmin, I don't know much about that, but if you write a script to do it someday, give something like that a try. Also, because I already did the test on it, I figured I'd post it in case someone searches the subject in the future. If the cvs file has a huge number of lines, you'd may want to break the queries down into smaller bits--just nest the code in another loop, and make the inserts in groups of 100 or 1000? Also, there should be some checking beforehand to see what the longest line is. Specifying the length isn't optional until PHP5.
I have little doubt there is a better way, and I'd like to know it!
I wish all well.
Salsa, you are very right. I tried creating a new excell csv with the example and got the no good output.
Your solution looks nice. An alternative would be to do a find/replace on " with the HTML equivalent ”
Either works, your solution saves some space and does not require anyone to know any HTML. Incase others are updating the database.
