Does anyone know if this Excel to MySQL can actually be performed?

[suttercain]

Hi guys,

 

I currentley use Navicat for most of my Excel to MySQL conversions. But I would like to get this web based.

 

Ideally the user would submit a standard .xls via a form upload.

 

Now here is the complex part. The file has 6 spreadsheets in it. Each spreadsheet has different columns and some of those columns need to go into different tables. So it's NOT just xls to one mysql table. It's actuallt one xls file with numerous spreadsheets going into different MySQL tables based on the column names.

 

Does anyone know IF THIS IS POSSIBLE?

 

Thanks.

 

SC

[dbo]

Of course it is possible... but probably not very easy. This is really an ETL activity. There are large organizations that specialize in this type of data management activity and it's not usually cheap. If you're looking for a canned solution I'd be very surprised if you can find a free one. Otherwise you're going to have to write some custom code that specifies the mapping rules necessary for doing the transformation from excel to mysql.

[suttercain]

Yeah, we already got a quote. $30,000. That's why I was looking into writing it... but who knows.

[dbo]

If you're transforming the data to the same datasources each time and the datamodel and business rules aren't changing it much you should be able to do something similar to the following process.

 

1) Upload .xls file

2) Export each sheet to CSV

3) Open each sheet and loop through the records doing appropriate transformation rules and insert into appropriate table

 

Naturally there are some details missing on error handling and archiving records so you can potentially have an audit trail etc. But at a high level that's the process. The export to CSV piece might be something you call an external perl script via PHP to do. I believe there are some nice modules for this type of thing in perl.

 

Let me know if you have any additional questions and maybe the two of us can brainstorm a bit.

[Barand]

I'll do it for $20,000

 

 

If that's still a bit steep, this should give you some insight into processing excel docs

 

http://web.informbank.com/articles/technology/php-office-documents.htm

[suttercain]

Thanks Barand,

 

That definitely gives some insight. Does anyone know how to get it to create multimple spreadsheets within the single excel file?

 

Thanks again.

[Barand]

Should be in here somewhere (MS Excel Object Model Reference)

 

http://msdn2.microsoft.com/en-gb/library/bb257026.aspx

[cooldude832]

This is just a stab in the dark, but i've used excel a lot at my work and we have been looking for a similar system to integrate our write up sheets into a database and then to pdfs to send to clients via email right after they are completed.  However we haven't done the web side yet, but through playing with excel i've learned that the spreadsheets are numbered in an indexial and associative matter that can be extracted from the code.  This is helpful because then you can refence cell data by the sheet name and then cell name (sorta a multi deminsonal array).  so it be like $sheet[$col][$row] but of course you have to find that part in the binary which it probably is a single string or party of a delimetered area.  So if you know what cells you want to go where it can probably be done pretty easily, otherwise to dynamically pull data it be a pain.  However you might be able to do while functions and go row by row and recreate it in a html table and then using forms insert data via  a web page

[MadTechie]

Yes is possible, i know this because i have it running on one of my WebApps,

 

infact i am going to be working on it again to make some improvement, the code is quite old now an their are a few bugs..

$30,000 is OTT, as the webapp itself isn't sold even near that and its pure php,  lol (either that or i need a better sales guy)

[hitman6003]

From a non-windows server I don't think it's possible.  From windows, you should be able to use the excel odbc driver and COM objects to access the sheets and data.  If you aren't familiar with VB, now's a good time...php examples are nearly impossible to find, but you can find quite a few written in VB/C#, and once you are using COM, the commands are nearly the same...just issuing them is different.

 

ASP.NET example:

http://www.aspfree.com/c/a/ASP.NET-Code/Read-Excel-files-from-ASPNET/

 

You may also be able to create an Excel COM object, open the .xls file, then save it in XML format...then you could use something like this:

http://www-128.ibm.com/developerworks/opensource/library/os-phpexcel/

 

It's not easy, but it is possible, and frequently the process is fragile and will break without much rhyme or reason.

[MadTechie]

It is possible i am using it on Windows, Mac & UNIX, the only problem i have had is with the calculation fields but i think i fixed that awhile ago.. will need to check the code

 

COMs were not used, but i used it with version 97 to 2000 (maybe 2002)  need to check

[roopurt18]

If each worksheet follows a consistent layout and has the same data in every row, I wonder if you could find a solution just exporting the file to a CSV, or other delimited format, and then using MySQL's LOAD DATA INFILE syntax to read it.

 

http://dev.mysql.com/doc/refman/4.1/en/load-data.html

 

Just thought I'd throw that out there.

[dbo]

edit

[Barand]

The original question stated that different cols need to go to different tables, so it isn't going to be as simple as just dropping it into a csv file and loading it.

 

A thought occured to me as I wrote that.

 

Drop the entire spreadsheet to CSV and load all cols into a "master" mysql table

 

Have series of queries such as

 

INSERT INTO table1 SELECT col1, col4, col2, col9 FROM master

 

INSERT INTO table2 SELECT col3, col5, col6, col7, col8 FROM master

 

etc

[roopurt18]

The only catch would be that each worksheet needs to represent a single tabular layout.  If you have a worksheet that does little things like merge cells or display an extra table it might start to get tricky.

 

I'd say that's worth $30k, lol

[Barand]

That was a pretty fast retraction of your previous post

 

And my $20K still stands

[cooldude832]

the idea still doesn't make sense to store in a linear mysql table unless each spreadshet became tis own mysql table because the idea is inpractical for the idea of mysql.

[Barand]

Can you explain that again, without wandering off into the realms of mathematical abstraction?

 

I know we're all fumbling in the dark, somewhat, as we have no idea of what the excel files in question look like. so all we can do is suggest some possible approaches to the problem.

[roopurt18]

That was a pretty fast retraction of your previous post

My post?

[suttercain]

Hi Guys,

 

Thanks for all the replies. So I have been looking more into this. I found a PHP application which loads a straight CVS file (converted from XML) into MySQL. But it doesn't have the advanced features I need.

 

Let me ask you all this.... how about XML? I see that Excel allows us to save the file as an XML document. The XML can then be dumped into MySQL according to the book PHP Hacks". My only problem is that the book claims you have to have Excel 2003 to do this. I am running 2002, but am allowed to save the file as an XML.

 

Here is what I got from PHP Hacks and the error that is popping up:

 

FILE UPLOAD FORM

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form enctype="multipart/form-data" action="import.php" method="post">
Excel XML File:
<input type="hidden" name="MAX_FILE_SIZE" value="2000000" />
<input type="file" name="file" /><br />
<input type="submit" value="Upload" />
</form>
</body>
</html>

 

HERE IS THE IMPORT.PHP FILE (SUPPOSE TO DISPLAY THE EXCEL INFORMATION IN A TABLE VIA THE BROWSER

<html>>
</head>

<body>
<?php
$data = array();
if ($_FILES['file']['tmp_name']) {
$dom = DOMDocument::load($_FILES['file']['tmp_name']);
$rows = $dom ->getElementsByTagName('Cell');
$datarow = array();
	foreach ($cells as cell) {
		$datarow[] = $cell->nodeValue;
	}
}
?>
<table>
<?php foreach ($data as $row) { ?>
<tr>
<?php foreach($row as $item) { ?>
<td><?php echo($item); ?></td>
<?php } ?>
</tr>
<?php } ?>
</table>
</body>
</html>

 

HERE IS THE ERROR I GET FROM THE IMPORT.PHP

Parse error: syntax error, unexpected ')', expecting T_PAAMAYIM_NEKUDOTAYIM in H:\xampp\htdocs\www\ExcelUpload\import.php on line 12

 

HERE IS A SMALL SAMPLE OF THE XML FILE THAT WAS CONVERTED IN EXCEL 2002

<Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <NumberFormat ss:Format="[ENG][$-409]mmmm\-yy;@"/>
  </Style>
  <Style ss:ID="s22">
   <NumberFormat ss:Format="[$-409]mmmmm\-yy;@"/>
  </Style>
</Styles>
<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="367" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:AutoFitWidth="0" ss:Width="86.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="109.5"/>
   <Column ss:AutoFitWidth="0" ss:Width="81.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="78.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="71.25"/>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="String">year</Data></Cell>
    <Cell><Data ss:Type="String">fact</Data></Cell>
    <Cell><Data ss:Type="String">fact_id</Data></Cell>
    <Cell><Data ss:Type="String">day</Data></Cell>
    <Cell><Data ss:Type="String">month</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s22"/>
    <Cell><Data ss:Type="Number">1976</Data></Cell>
    <Cell><Data ss:Type="String">Superman vs. Spider-Man is released. It's the first time DC and Marvel characters come together.</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="String">January</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"/>
    <Cell ss:Index="4"><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="String">January</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"/>
    <Cell ss:Index="4"><Data ss:Type="Number">3</Data></Cell>
    <Cell><Data ss:Type="Number">3</Data></Cell>
    <Cell><Data ss:Type="String">January</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index="4"><Data ss:Type="Number">4</Data></Cell>
    <Cell><Data ss:Type="Number">4</Data></Cell>
    <Cell><Data ss:Type="String">January</Data></Cell>
   </Row>
</Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Print>
    <ValidPrinterInfo/>
    <VerticalResolution>0</VerticalResolution>
   </Print>
   <Selected/>
   <SplitHorizontal>465</SplitHorizontal>
   <TopRowBottomPane>0</TopRowBottomPane>
   <ActivePane>2</ActivePane>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>19</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
    <Pane>
     <Number>2</Number>
     <ActiveRow>15</ActiveRow>
     <ActiveCol>2</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
</Workbook>

 

I don't have the excel worksheets here at home, but I will post a link to the example and eventual database layout.

 

Where there is a will... there is a way, for $30,000.

[suttercain]

Wow... I messed up the code... ignore the import.php from the previous step...

 

here is the correct code for the import.php and it returns a blank white page for me... maybe I do need Excel 2003.

 

<html>
</head>

<body>
<?php
$data = array();
if ($_FILES['file']['tmp_name']) {
$dom = DOMDocument::load($_FILES['file']['tmp_name']);
$rows = $dom->getElementsByTagName('Row');
foreach($rows as $row) {
	$cells = $row->getElementsByTagName('Cell');
	$datarow = array();
	foreach ($cells as $cell) {
		$datarow [] = $cell->nodeValue;
	}
	$date [] = $datarow;
	}
}
?>
<table>
<?php foreach ($data as $row) { ?>
<tr>
<?php foreach($row as $item) { ?>
<td><?php echo($item); ?></td>
<?php } ?>
</tr>
<?php } ?>
</table>
</body>
</html>

[keeB]

Easiest way to do it generically (sorry if this has already been mentioned) is to make the Tab names == table name

[dbo]

None of these solutions are going to work without transformation logic. You've already stated that data needs to be mixed and matched from the sheets to get where it belongs. The CSV and XML imports are just dumping data based on a naming convention. So at the very least you have to write some logic to get it to CSV or XML in the right format... which if you can do this you can just write the insert statements.

 

All kidding aside if you'd like to talk seriously about it I or my company could offer consulting services to provide you a solution which I'm sure would come well below the 30k that you were estimated and Barand's 20k Before being able to adequately estimate the cost an analysis of current configurations and a look at the Excel files would be in order.

[MadTechie]

what type of excel files are you using?

i know thats a very open question what i mean is, are they very complex (lots of calculation, charts etc) or kinda simple (just X worksheet with rows of data)

[Barand]

Getting there but blanks cells are a pain!

<?php
$str = <<<XML
<Workbook>
<Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s21">
   <NumberFormat ss:Format="[ENG][$-409]mmmm\-yy;@"/>
  </Style>
  <Style ss:ID="s22">
   <NumberFormat ss:Format="[$-409]mmmmm\-yy;@"/>
  </Style>
</Styles>
<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="367" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:AutoFitWidth="0" ss:Width="86.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="109.5"/>
   <Column ss:AutoFitWidth="0" ss:Width="81.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="78.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="71.25"/>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="String">year</Data></Cell>
    <Cell><Data ss:Type="String">fact</Data></Cell>
    <Cell><Data ss:Type="String">fact_id</Data></Cell>
    <Cell><Data ss:Type="String">day</Data></Cell>
    <Cell><Data ss:Type="String">month</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s22"/>
    <Cell><Data ss:Type="Number">1976</Data></Cell>
    <Cell><Data ss:Type="String">Superman vs. Spider-Man is released. It's the first time DC and Marvel characters come together.</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="String">January</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"/>
    <Cell ss:Index="4"><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="String">January</Data></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s21"/>
    <Cell ss:Index="4"><Data ss:Type="Number">3</Data></Cell>
    <Cell><Data ss:Type="Number">3</Data></Cell>
    <Cell><Data ss:Type="String">January</Data></Cell>
   </Row>
   <Row>
    <Cell ss:Index="4"><Data ss:Type="Number">4</Data></Cell>
    <Cell><Data ss:Type="Number">4</Data></Cell>
    <Cell><Data ss:Type="String">January</Data></Cell>
   </Row>
</Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Print>
    <ValidPrinterInfo/>
    <VerticalResolution>0</VerticalResolution>
   </Print>
   <Selected/>
   <SplitHorizontal>465</SplitHorizontal>
   <TopRowBottomPane>0</TopRowBottomPane>
   <ActivePane>2</ActivePane>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>19</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
    <Pane>
     <Number>2</Number>
     <ActiveRow>15</ActiveRow>
     <ActiveCol>2</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
</Workbook>
XML;

$str = str_replace(array('ss:', 'x:'),'',$str);
$xml = simplexml_load_string($str);

foreach ($xml->Worksheet as $ws)
{
    echo "<h3>{$ws['Name']}</h3>";
    foreach ($ws->Table as $wst)
    {
        foreach ($wst->Row as $wstr)
        {
            foreach ($wstr as $cell) {
                if ($cell['Index'])
                {
                    for ($i=2; $i <= $cell['Index']; $i++)
                    {
                        echo ' - |';
                    }
                }
                
                foreach ($cell as $stuff)
                {
                    echo $stuff . '| ';
                }            
            }
            echo '<br/>';
        }
    }
}

?>