MySQL and Excel 1

[wreikun]

Im wondering if MySQL is compatible with Excel files. For example, I would like to take a large Excel file and make it so I can query it using the MySQL database. Is this possible

Thanks,
--REI

 

[piti]

i'm not exactly sure what u want
do you want to use mysql to return data from excel files?
why using excel file to store data when you have a db server?

 

[wreikun]

Yes, I would like to use mysql to return data from excel files. For example, if I had a client who had huge excel file full of names, addresses, phone numbers, etc. and he wanted a database application, would I be able to take that excel file and easily transfer it to MySQL or access that file from MySQL?

REI

 

[piti]

the only way you could return the data from excel files is to import them into a mysql database (i don't think even ms sql server could do this without importing the data)

first you'll need to export your data into some comma (or whatever) separated text file and then use mysqlimport util or LOAD DATA INFILE statement

 

[hisham]

First you must save the file as example.csv “Excel has this feature”.
Load data from a CSV, tab delmited or delmited by any other character
can be on the filesystem local to the client:

LOAD DATA LOCAL INFILE "table1.txt" INTO TABLE table1;
LOAD DATA INFILE "/tmp/file_name" into table test IGNORE 1 LINES;
SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM ...;
LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ',';

If you use PHP: fgetcsv() Gets line from file pointer and parse for CSV fields:
Example: Read and print entire contents of a CSV file

$row = 1;
$fp = fopen ("test.csv","r"
while ($data = fgetcsv ($fp, 1000, ",&quot) {
$num = count ($data);
print &quot;<p> $num fields in line $row: <br>&quot;;
$row++;
for ($c=0; $c < $num; $c++) {
print $data[$c] . &quot;<br>&quot;;
}
}
fclose ($fp);