LinuxQuestions.org - Excel to Text

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Excel to Text (https://www.linuxquestions.org/questions/programming-9/excel-to-text-485997/)

I am working on a PHP application. I need to upload a Excel spreadsheet and convert it to a text file. Then parse it and insert the data into a MySQL database.

I know how to upload the file, parse it and insert the data into the database. I just do not know how to convert the Excel spread sheet to a text file (tab delimited) from the command line.

I would really like to extract data from certain cells, but it is necessary. I could have the user convert the file to a text file, but I would like to keep it as easy as possible from the users point of view.

I did google for about 6 hours and did not find anything. Any help would be appreciated. Thanks

if you want to convert from one format to another, take the windows file extension of the first, then add the number 2 then add the destination extension...

http://www.google.com/search?q=xls2csv

bingo. hopefully.

I have done this before, although in Windows with .NET. I just did a quick Google, and it seems Mono supports oledb, at least with the PostgreSQL provider. I would guess that MySQL also has a provider. If so, the connection string should look similar to:

Code:

//Standard OLEDb connection string for Excel files

                        conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + 

                                @"Data Source=" + filename  + ";" + 

                                @"Extended Properties=" + (char)34 + "Excel 8.0;HDR=No;IMEX=1;" + (char)34;

substituting the MySQL provider in place of the Jet provider. Once you have an OLEDB connection, you should be able to modify this to read the cell data:

Code:

OleDbDataAdapter table = null;



                        DataSet DataSet1 = new DataSet("Records");

                        

                        try

                        {

                                string query = "SELECT * FROM [" + tab + "$" + column + cell + ":" + column + cell + "]";

                                

                                table = new OleDbDataAdapter(query, conn);



                                table.TableMappings.Add("Table", "ExcelTest");



                                //delete anything that might be in dataset so not duplicating records



                                DataSet1.Tables.Clear();

                        

                                table.Fill(DataSet1);



                                return DataSet1.Tables[0].Rows[0].ItemArray.GetValue(0).ToString();

                        }

If you are on a linux platform and use OO, you can write a script in OO which does just that.

There are numerous examples of this in the OO forums. Unfortunately I cannot give you the code because it resides in the OO installation on my office machine, and that machine is off-line because they are remodelling my office.

Most important is that once you wrote this script in OO, you can call it from the command line, without actually opening OO on the GUI, without any user interaction.

You can open Excel file, I do it regularly in order to extract data from Excel files and feed the data to GNUplot.

As far as I remember, CSV is a standard export formats, but you can awk and sed it easily to whatever you need.

jlinkels

Hi

There are commercial PHP classes that can do this also, e.g.
http://www.excelparser.com/

Thanks acid_kewpie, that is what I was looking for. I should be able to create a script to run when I file is uploaded that will insert the data into a database.

I hope that the answer of acid_kewpie learned me to READ anyone else post BEFORE I add my two cents of WORTHLESS thoughts.

SIGH

You are never too old to learn or too old to learn to read.

My apologies for wasting storage space on this forum.

jlinkels