LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 09-22-2006, 10:05 AM   #1
tangle
Senior Member
 
Registered: Apr 2002
Location: Arbovale, WV
Distribution: Slackware
Posts: 1,761

Rep: Reputation: 78
Excel to Text


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
 
Old 09-22-2006, 10:19 AM   #2
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,417

Rep: Reputation: 1985Reputation: 1985Reputation: 1985Reputation: 1985Reputation: 1985Reputation: 1985Reputation: 1985Reputation: 1985Reputation: 1985Reputation: 1985Reputation: 1985
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.
 
Old 09-22-2006, 11:12 AM   #3
tekkieman
Member
 
Registered: Dec 2004
Location: Northern CA
Distribution: Mepis 6.5
Posts: 123

Rep: Reputation: 15
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();
			}
 
Old 09-23-2006, 09:13 AM   #4
jlinkels
LQ Guru
 
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195

Rep: Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043
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
 
Old 09-23-2006, 10:10 AM   #5
Guttorm
Senior Member
 
Registered: Dec 2003
Location: Trondheim, Norway
Distribution: Debian and Ubuntu
Posts: 1,450

Rep: Reputation: 446Reputation: 446Reputation: 446Reputation: 446Reputation: 446
Hi

There are commercial PHP classes that can do this also, e.g.
http://www.excelparser.com/
 
Old 09-23-2006, 12:59 PM   #6
tangle
Senior Member
 
Registered: Apr 2002
Location: Arbovale, WV
Distribution: Slackware
Posts: 1,761

Original Poster
Rep: Reputation: 78
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.
 
Old 09-23-2006, 09:31 PM   #7
jlinkels
LQ Guru
 
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195

Rep: Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel to Html msvinaykumar Linux - Newbie 8 08-10-2006 09:09 AM
working with excel sthomas123 General 4 02-04-2006 11:31 AM
convert text file to binary excel file ust Linux - General 2 11-23-2004 02:33 AM
Macros in OO vs Excel sikandar Linux - Software 0 09-01-2003 12:47 AM
Clone of MS Excel? omgkthxbye Linux - Software 3 10-24-2002 03:00 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 09:22 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration