ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
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...
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:
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();
}
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195
Rep:
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.
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.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.