RE: [PHP] excel 2 csv 2 mysql
yes i hope it comes handy , i'm stoked i found it, the import works perfect
i simply upload the xls file str8 into mysql :D or u can play around with
the csv data using getcsv()
-Original Message-
From: jon roig [mailto:[EMAIL PROTECTED]
Sent: Friday, April 04, 2003 4:13 AM
To: [EMAIL PROTECTED]
Subject: RE: [PHP] excel 2 csv 2 mysql
Pretty cool, man... I actually didn't know about xls2csv.
Learn something new every day -- this'll certainly come in handy.
'Course, it's not exactly standard with most distributions. For anyone
looking for it, you can download it here:
http://www.45.free.net/~vitus/ice/catdoc/
Or, depending on your distro, you might be able to find an RPM:
http://www.rpmfind.net/linux/rpm2html/search.php?query=catdoc
... sounds like this might make for a good web service of some kind... I'll
have to see if I can whip something up.
-- jon
-Original Message-
From: daniel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2003 1:26 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; daniel
Subject: RE: [PHP] excel 2 csv 2 mysql
ok i've built a class to acomplish this now
class Export_Excel {
var $command
function Export_Excel() {
$this->command = "/www_tools/apache/catdoc/bin/xls2csv";
}
function xls2csv($path,$xls_file,$short_name) {
$cmd = escapeshellcmd("$this->command -q 0 $path.$xls_file");
$cmd .= " > $path.$short_name.csv";
@exec($cmd,$stdout,$errocode);
unlink("$path.$xls_file");
if ($errorcode > 0) return $errocode;
}
}
and here is my db class function
function
insert_file($filename,$table,$col_terminator,$line_terminator,$cols=false,$o
pt
ions=false,$remove_file=false) {
if ($options) $option = "OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED
BY
'\n'";
$this->query("LOAD DATA INFILE '$filename' INTO TABLE $table FIELDS
TERMINATED BY '$col_terminator' $option $cols");
if ($remove_files) unlink($filename);
}
its just a quicky, twiddling thumbs today i just had to find out how to do
it
:D
>= Original Message From daniel <[EMAIL PROTECTED]> =
>ok it works now
>
>/www_tools/apache/catdoc/bin/xls2csv -q 0 test.xls > test2.csv
>
>
>column1`,column2,column3
>faffafs,fsafsa,fsafs
>fssfa,fasfs,fasfs
>fasaf,fasfs,asffaf
>fafs,sfafsa,fasfas
>sfafs,fssfa,fassfa
>sfasfa,asfafs,fasfas
>asffas,sfaaf,sfafsa
>fssaf,asffsa,asffas
>sfasfa,sfasaf,fasasf
>
>then i can load that into mysql using load data into :D, aparantly there
are
>some bugs , gotta find them
>>= Original Message From daniel <[EMAIL PROTECTED]> =
>>ok guys are you ready ,
>>
>>/www_tools/apache/catdoc/bin/xls2csv test.xls > test2.csv
>>
>>gave me this
>>
>>
>>"column1`","column2","column3"
>>"faffafs","fsafsa","fsafs"
>>"fssfa","fasfs","fasfs"
>>"fasaf","fasfs","asffaf"
>>"fafs","sfafsa","fasfas"
>>"sfafs","fssfa","fassfa"
>>"sfasfa","asfafs","fasfas"
>>"asffas","sfaaf","sfafsa"
>>"fssaf","asffsa","asffas"
>>"sfasfa","sfasaf","fasasf"
>>
>>unfortunately its putting quotes in, have to strip them
>>
>>>= Original Message From <[EMAIL PROTECTED]> =
>>>Yeah... you're going to have to use COM or something similar on a windows
>>>server to get effective access to the server.
>>>
>>>Another option is dynamically adding at as an odbc datasource and
querying
>>>it. Again, this will only work on a windows server...
>>>
>>>It's all much easier if you can convince users to upload csv files.
>>>
>>> - jon
>>>
>>>-
>>>jon roig
>>>senior manager, online production
>>>epilepsy foundation
>>>http://jonroig.com
>>>
>>>
>>>-Original Message-
>>>From: daniel [mailto:[EMAIL PROTECTED]
>>>Sent: Monday, March 31, 2003 7:48 PM
>>>To: [EMAIL PROTECTED]
>>>Subject: [PHP] excel 2 csv 2 mysql
>>>
>>>
>>>hi guys i am trying to work out how to dynamically be able to upload an
>>>excel
>>>file , export it to csv to be able to import into mysql , is there any
>>>examples out there ? fopen gave me binary code :|
>>>
>>>
>>>
>>>--
>>>PHP General Mailing List (http://www.php.net/)
>>>To unsubscribe, visit: http://www.php.net/unsub.php
>>>
>>>
>>>
>>>
>>>--
>>>PHP General Mailing List (http://www.php.net/)
>>>To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>>
>>--
>>PHP General Mailing List (http://www.php.net/)
>>To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>--
>PHP General Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
Pretty cool, man... I actually didn't know about xls2csv.
Learn something new every day -- this'll certainly come in handy.
'Course, it's not exactly standard with most distributions. For anyone
looking for it, you can download it here:
http://www.45.free.net/~vitus/ice/catdoc/
Or, depending on your distro, you might be able to find an RPM:
http://www.rpmfind.net/linux/rpm2html/search.php?query=catdoc
... sounds like this might make for a good web service of some kind... I'll
have to see if I can whip something up.
-- jon
-Original Message-
From: daniel [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 01, 2003 1:26 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; daniel
Subject: RE: [PHP] excel 2 csv 2 mysql
ok i've built a class to acomplish this now
class Export_Excel {
var $command
function Export_Excel() {
$this->command = "/www_tools/apache/catdoc/bin/xls2csv";
}
function xls2csv($path,$xls_file,$short_name) {
$cmd = escapeshellcmd("$this->command -q 0 $path.$xls_file");
$cmd .= " > $path.$short_name.csv";
@exec($cmd,$stdout,$errocode);
unlink("$path.$xls_file");
if ($errorcode > 0) return $errocode;
}
}
and here is my db class function
function
insert_file($filename,$table,$col_terminator,$line_terminator,$cols=false,$o
pt
ions=false,$remove_file=false) {
if ($options) $option = "OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED
BY
'\n'";
$this->query("LOAD DATA INFILE '$filename' INTO TABLE $table FIELDS
TERMINATED BY '$col_terminator' $option $cols");
if ($remove_files) unlink($filename);
}
its just a quicky, twiddling thumbs today i just had to find out how to do
it
:D
>= Original Message From daniel <[EMAIL PROTECTED]> =
>ok it works now
>
>/www_tools/apache/catdoc/bin/xls2csv -q 0 test.xls > test2.csv
>
>
>column1`,column2,column3
>faffafs,fsafsa,fsafs
>fssfa,fasfs,fasfs
>fasaf,fasfs,asffaf
>fafs,sfafsa,fasfas
>sfafs,fssfa,fassfa
>sfasfa,asfafs,fasfas
>asffas,sfaaf,sfafsa
>fssaf,asffsa,asffas
>sfasfa,sfasaf,fasasf
>
>then i can load that into mysql using load data into :D, aparantly there
are
>some bugs , gotta find them
>>= Original Message From daniel <[EMAIL PROTECTED]> =
>>ok guys are you ready ,
>>
>>/www_tools/apache/catdoc/bin/xls2csv test.xls > test2.csv
>>
>>gave me this
>>
>>
>>"column1`","column2","column3"
>>"faffafs","fsafsa","fsafs"
>>"fssfa","fasfs","fasfs"
>>"fasaf","fasfs","asffaf"
>>"fafs","sfafsa","fasfas"
>>"sfafs","fssfa","fassfa"
>>"sfasfa","asfafs","fasfas"
>>"asffas","sfaaf","sfafsa"
>>"fssaf","asffsa","asffas"
>>"sfasfa","sfasaf","fasasf"
>>
>>unfortunately its putting quotes in, have to strip them
>>
>>>= Original Message From <[EMAIL PROTECTED]> =
>>>Yeah... you're going to have to use COM or something similar on a windows
>>>server to get effective access to the server.
>>>
>>>Another option is dynamically adding at as an odbc datasource and
querying
>>>it. Again, this will only work on a windows server...
>>>
>>>It's all much easier if you can convince users to upload csv files.
>>>
>>> - jon
>>>
>>>-
>>>jon roig
>>>senior manager, online production
>>>epilepsy foundation
>>>http://jonroig.com
>>>
>>>
>>>-Original Message-
>>>From: daniel [mailto:[EMAIL PROTECTED]
>>>Sent: Monday, March 31, 2003 7:48 PM
>>>To: [EMAIL PROTECTED]
>>>Subject: [PHP] excel 2 csv 2 mysql
>>>
>>>
>>>hi guys i am trying to work out how to dynamically be able to upload an
>>>excel
>>>file , export it to csv to be able to import into mysql , is there any
>>>examples out there ? fopen gave me binary code :|
>>>
>>>
>>>
>>>--
>>>PHP General Mailing List (http://www.php.net/)
>>>To unsubscribe, visit: http://www.php.net/unsub.php
>>>
>>>
>>>
>>>
>>>--
>>>PHP General Mailing List (http://www.php.net/)
>>>To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>>
>>--
>>PHP General Mailing List (http://www.php.net/)
>>To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>--
>PHP General Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] excel 2 csv 2 mysql
The quotes are valid, use fgetcsv() function, it will strip them daniel wrote: ok guys are you ready , /www_tools/apache/catdoc/bin/xls2csv test.xls > test2.csv gave me this "column1`","column2","column3" "faffafs","fsafsa","fsafs" "fssfa","fasfs","fasfs" "fasaf","fasfs","asffaf" "fafs","sfafsa","fasfas" "sfafs","fssfa","fassfa" "sfasfa","asfafs","fasfas" "asffas","sfaaf","sfafsa" "fssaf","asffsa","asffas" "sfasfa","sfasaf","fasasf" unfortunately its putting quotes in, have to strip them = Original Message From <[EMAIL PROTECTED]> = Yeah... you're going to have to use COM or something similar on a windows server to get effective access to the server. Another option is dynamically adding at as an odbc datasource and querying it. Again, this will only work on a windows server... It's all much easier if you can convince users to upload csv files. - jon - jon roig senior manager, online production epilepsy foundation http://jonroig.com -Original Message- From: daniel [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 7:48 PM To: [EMAIL PROTECTED] Subject: [PHP] excel 2 csv 2 mysql hi guys i am trying to work out how to dynamically be able to upload an excel file , export it to csv to be able to import into mysql , is there any examples out there ? fopen gave me binary code :| -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re:[PHP] excel 2 csv 2 mysql
Justin, For reading, you could try this: http://www.zakkis.ca/products/abc_excelparser/index.php Cost is 150 CAD - quite steep. Tried the online demo - seems to work. Will only work with cells containing strings though - doesn't resolve formulae. If you purchase it, perhaps you could let me know how you get on? For writing, there seems to be a module in Pear: http://pear.php.net/package-info.php?pacid=156 >> hi guys i am trying to work out how to dynamically be able to upload an excel >> file , export it to csv to be able to import into mysql , is there any >> examples out there ? fopen gave me binary code :| -- Geoff Caplan Advantae Ltd http://www.advantae.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
ok i've built a class to acomplish this now
class Export_Excel {
var $command
function Export_Excel() {
$this->command = "/www_tools/apache/catdoc/bin/xls2csv";
}
function xls2csv($path,$xls_file,$short_name) {
$cmd = escapeshellcmd("$this->command -q 0 $path.$xls_file");
$cmd .= " > $path.$short_name.csv";
@exec($cmd,$stdout,$errocode);
unlink("$path.$xls_file");
if ($errorcode > 0) return $errocode;
}
}
and here is my db class function
function
insert_file($filename,$table,$col_terminator,$line_terminator,$cols=false,$opt
ions=false,$remove_file=false) {
if ($options) $option = "OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED
BY
'\n'";
$this->query("LOAD DATA INFILE '$filename' INTO TABLE $table FIELDS
TERMINATED BY '$col_terminator' $option $cols");
if ($remove_files) unlink($filename);
}
its just a quicky, twiddling thumbs today i just had to find out how to do it
:D
>= Original Message From daniel <[EMAIL PROTECTED]> =
>ok it works now
>
>/www_tools/apache/catdoc/bin/xls2csv -q 0 test.xls > test2.csv
>
>
>column1`,column2,column3
>faffafs,fsafsa,fsafs
>fssfa,fasfs,fasfs
>fasaf,fasfs,asffaf
>fafs,sfafsa,fasfas
>sfafs,fssfa,fassfa
>sfasfa,asfafs,fasfas
>asffas,sfaaf,sfafsa
>fssaf,asffsa,asffas
>sfasfa,sfasaf,fasasf
>
>then i can load that into mysql using load data into :D, aparantly there are
>some bugs , gotta find them
>>= Original Message From daniel <[EMAIL PROTECTED]> =
>>ok guys are you ready ,
>>
>>/www_tools/apache/catdoc/bin/xls2csv test.xls > test2.csv
>>
>>gave me this
>>
>>
>>"column1`","column2","column3"
>>"faffafs","fsafsa","fsafs"
>>"fssfa","fasfs","fasfs"
>>"fasaf","fasfs","asffaf"
>>"fafs","sfafsa","fasfas"
>>"sfafs","fssfa","fassfa"
>>"sfasfa","asfafs","fasfas"
>>"asffas","sfaaf","sfafsa"
>>"fssaf","asffsa","asffas"
>>"sfasfa","sfasaf","fasasf"
>>
>>unfortunately its putting quotes in, have to strip them
>>
>>>= Original Message From <[EMAIL PROTECTED]> =
>>>Yeah... you're going to have to use COM or something similar on a windows
>>>server to get effective access to the server.
>>>
>>>Another option is dynamically adding at as an odbc datasource and querying
>>>it. Again, this will only work on a windows server...
>>>
>>>It's all much easier if you can convince users to upload csv files.
>>>
>>> - jon
>>>
>>>-
>>>jon roig
>>>senior manager, online production
>>>epilepsy foundation
>>>http://jonroig.com
>>>
>>>
>>>-Original Message-
>>>From: daniel [mailto:[EMAIL PROTECTED]
>>>Sent: Monday, March 31, 2003 7:48 PM
>>>To: [EMAIL PROTECTED]
>>>Subject: [PHP] excel 2 csv 2 mysql
>>>
>>>
>>>hi guys i am trying to work out how to dynamically be able to upload an
>>>excel
>>>file , export it to csv to be able to import into mysql , is there any
>>>examples out there ? fopen gave me binary code :|
>>>
>>>
>>>
>>>--
>>>PHP General Mailing List (http://www.php.net/)
>>>To unsubscribe, visit: http://www.php.net/unsub.php
>>>
>>>
>>>
>>>
>>>--
>>>PHP General Mailing List (http://www.php.net/)
>>>To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>>
>>--
>>PHP General Mailing List (http://www.php.net/)
>>To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>--
>PHP General Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
ok it works now /www_tools/apache/catdoc/bin/xls2csv -q 0 test.xls > test2.csv column1`,column2,column3 faffafs,fsafsa,fsafs fssfa,fasfs,fasfs fasaf,fasfs,asffaf fafs,sfafsa,fasfas sfafs,fssfa,fassfa sfasfa,asfafs,fasfas asffas,sfaaf,sfafsa fssaf,asffsa,asffas sfasfa,sfasaf,fasasf then i can load that into mysql using load data into :D, aparantly there are some bugs , gotta find them >= Original Message From daniel <[EMAIL PROTECTED]> = >ok guys are you ready , > >/www_tools/apache/catdoc/bin/xls2csv test.xls > test2.csv > >gave me this > > >"column1`","column2","column3" >"faffafs","fsafsa","fsafs" >"fssfa","fasfs","fasfs" >"fasaf","fasfs","asffaf" >"fafs","sfafsa","fasfas" >"sfafs","fssfa","fassfa" >"sfasfa","asfafs","fasfas" >"asffas","sfaaf","sfafsa" >"fssaf","asffsa","asffas" >"sfasfa","sfasaf","fasasf" > >unfortunately its putting quotes in, have to strip them > >>= Original Message From <[EMAIL PROTECTED]> = >>Yeah... you're going to have to use COM or something similar on a windows >>server to get effective access to the server. >> >>Another option is dynamically adding at as an odbc datasource and querying >>it. Again, this will only work on a windows server... >> >>It's all much easier if you can convince users to upload csv files. >> >> - jon >> >>- >>jon roig >>senior manager, online production >>epilepsy foundation >>http://jonroig.com >> >> >>-Original Message- >>From: daniel [mailto:[EMAIL PROTECTED] >>Sent: Monday, March 31, 2003 7:48 PM >>To: [EMAIL PROTECTED] >>Subject: [PHP] excel 2 csv 2 mysql >> >> >>hi guys i am trying to work out how to dynamically be able to upload an >>excel >>file , export it to csv to be able to import into mysql , is there any >>examples out there ? fopen gave me binary code :| >> >> >> >>-- >>PHP General Mailing List (http://www.php.net/) >>To unsubscribe, visit: http://www.php.net/unsub.php >> >> >> >> >>-- >>PHP General Mailing List (http://www.php.net/) >>To unsubscribe, visit: http://www.php.net/unsub.php > > > >-- >PHP General Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
ok guys are you ready , /www_tools/apache/catdoc/bin/xls2csv test.xls > test2.csv gave me this "column1`","column2","column3" "faffafs","fsafsa","fsafs" "fssfa","fasfs","fasfs" "fasaf","fasfs","asffaf" "fafs","sfafsa","fasfas" "sfafs","fssfa","fassfa" "sfasfa","asfafs","fasfas" "asffas","sfaaf","sfafsa" "fssaf","asffsa","asffas" "sfasfa","sfasaf","fasasf" unfortunately its putting quotes in, have to strip them >= Original Message From <[EMAIL PROTECTED]> = >Yeah... you're going to have to use COM or something similar on a windows >server to get effective access to the server. > >Another option is dynamically adding at as an odbc datasource and querying >it. Again, this will only work on a windows server... > >It's all much easier if you can convince users to upload csv files. > > - jon > >- >jon roig >senior manager, online production >epilepsy foundation >http://jonroig.com > > >-Original Message- >From: daniel [mailto:[EMAIL PROTECTED] >Sent: Monday, March 31, 2003 7:48 PM >To: [EMAIL PROTECTED] >Subject: [PHP] excel 2 csv 2 mysql > > >hi guys i am trying to work out how to dynamically be able to upload an >excel >file , export it to csv to be able to import into mysql , is there any >examples out there ? fopen gave me binary code :| > > > >-- >PHP General Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php > > > > >-- >PHP General Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
http://www.ice.ru/~vitus/catdoc/ anyone seen this ? its pretty much wot i needed, havent used it yet though >= Original Message From <[EMAIL PROTECTED]> = >Yeah... you're going to have to use COM or something similar on a windows >server to get effective access to the server. > >Another option is dynamically adding at as an odbc datasource and querying >it. Again, this will only work on a windows server... > >It's all much easier if you can convince users to upload csv files. > > - jon > >- >jon roig >senior manager, online production >epilepsy foundation >http://jonroig.com > > >-Original Message- >From: daniel [mailto:[EMAIL PROTECTED] >Sent: Monday, March 31, 2003 7:48 PM >To: [EMAIL PROTECTED] >Subject: [PHP] excel 2 csv 2 mysql > > >hi guys i am trying to work out how to dynamically be able to upload an >excel >file , export it to csv to be able to import into mysql , is there any >examples out there ? fopen gave me binary code :| > > > >-- >PHP General Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php > > > > >-- >PHP General Mailing List (http://www.php.net/) >To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] excel 2 csv 2 mysql
on 01/04/03 11:48 AM, daniel ([EMAIL PROTECTED]) wrote: > hi guys i am trying to work out how to dynamically be able to upload an excel > file , export it to csv to be able to import into mysql , is there any > examples out there ? fopen gave me binary code :| also see fgetscsv() Justin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] excel 2 csv 2 mysql
Yeah... you're going to have to use COM or something similar on a windows server to get effective access to the server. Another option is dynamically adding at as an odbc datasource and querying it. Again, this will only work on a windows server... It's all much easier if you can convince users to upload csv files. - jon - jon roig senior manager, online production epilepsy foundation http://jonroig.com -Original Message- From: daniel [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 7:48 PM To: [EMAIL PROTECTED] Subject: [PHP] excel 2 csv 2 mysql hi guys i am trying to work out how to dynamically be able to upload an excel file , export it to csv to be able to import into mysql , is there any examples out there ? fopen gave me binary code :| -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
