im using SELECT INTO OUTFILE query with XLS file as
"SELECT * INTO OUTFILE " . "myfile.xls" . " FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' FROM transactions where ppno like '%$cid%'
Im using XLS file instead of CSV. Is there anything wrong with that. When i try to open that XLS file in Microsoft Excel it says 'The file is in different format, Are you sure you want to open'... Plz suggest me
As Piskvor and Devart have said, simply giving a file an extension of .xls doesn't actually make it an Excel file.... the file content generated is still just a tab-separated value file as your SQL statement makes clear
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
If you want to create a real Excel file from within your PHP script, where you can also (for example) format the data that you're exporting, then you need to use a library such as my own PHPExcel, or one of the others listed in this thread.
Yes, there is indeed something wrong with that. Namely, you are only changing the filename - that is not sufficient to automagically convert your data to the appropriate data format (in other words, "a rose by any other name would smell as sweet"). Note that Excel actually tells you this, in the error message.
SELECT INTO OUTFILE has no support for Excel documents. You need to a) export into CSV, and convert afterwards, or b) get a tool which has this export capability (e.g. phpMyAdmin or Navicat).
It is possible to set any file extension. Actually xls is a binary OLE file that Excel tries to read. SELECT INTO OUTFILE generates text file, try to store file with CSV or TXT extension. In this case Excel will open file correctly.
