How can an uploaded Excel file update an Oracle database?

Question

I have an Oracle 11g database, being communicated to with PHP files. I want users on my website to be able to upload .xls documents, whose cell values will pass to the variables in the database.

I figure I will have the user upload the .xls via a basic html form, which seems the easiest. How can I have the data from an uploaded .xls file to update a database?

Note: The top couple of rows may have to be ignored because they will be titles/headings. How can I have the program ignore those?

Example Table:

TITLES AND OTHER HEADERS - Probably 2-3 rows of cells
Header1 | Header2 | Header3 | Header4 | Header5 | Header6 | Header7 | Header8 
value1  | value2  | value3  | value4  |  value5 | value6  | calue7  | value8
...
valueN  | valueN  | valueN  | valueN  |  valueN |  valueN | valueN  | valueN

My current code:

Form:

   <?php require "reader.php";
 ?>


<form name="SLCCA" action="update_handler2.php" method="post">


   <u>GF:</u> <input type="file" name="xls_doc"><br>


   <input type="submit" value="Submit">

Form Handler:

   <?php

    $xls_doc=$_POST["xls_doc"];

    $objConnect = oci_connect("user", "pass", "(description=(address=(protocol=tcp)(host=hostaddress.com)(port=1533))(connect_data=(service_name=sid)))");

require_once('reader.php');
?>

<?php

    $reader=new Spreadsheet_Excel_Reader();
    $reader->setUTFEncoder('iconv');
    $reader->setOutputEncoding('UTF-8');
    $reader->read($xls_doc);

    $strSQL = "LOAD DATA
    INFILE 'xls_doc.dat'
    BADFILE 'xls_doc.bad'
    DISCARDFILE 'xls_doc.dsc'
    APPEND
    INTO TABLE INTOXDM.LCCR";

    $objParse = oci_parse($objConnect, $strSQL);
    $objExecute = oci_execute($objParse);

?>

This is an Oracle question, not a MySQL question! People either aren't reading closely or they are participating in tags they don't understand. — codenheim, Jun 30 '14 at 16:49
@mrjoltcola are you saying I have done something wrong? I am confused lol — Ryan4588, Jun 30 '14 at 16:53
@Ryan4588 - No, not you; the people who have edited and closed your question and linked it to a MySQL question have done something wrong. — codenheim, Jun 30 '14 at 16:54
Okay, I thought so lol just clarifying. I think hd1's answer will suffice, though. Still confused how they can confuse the Oracle with MySQL lol — Ryan4588, Jun 30 '14 at 16:59
@JasonMArcher - Sure it is unique to MySQL. 2 out of 3 of the answers are MySQL specific, unless you can show us how to import data into Oracle with PHPMyAdmin or "load data local infile ..." syntax — codenheim, Jun 30 '14 at 17:16

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

First, convert the XLS to csv:

$reader=new Spreadsheet_Excel_Reader();
$reader->setUTFEncoder('iconv');
$reader->setOutputEncoding('UTF-8');
$reader->read($filename);

Now use SQL*Loader to read it in:

LOAD DATA
INFILE 'my_file.dat'
BADFILE 'my_file.bad'
DISCARDFILE 'my_file.dsc'
APPEND
INTO TABLE my_table

If you've further problems, do leave a comment.

edited May 23 '17 at 11:57

Community

1
1

answered Jun 30 '14 at 16:40

hd1

33,938
5
80
91

After converting it to a .csv, how can I have some top portion deleted (or ignored) that way headers/titles aren't added into the database. If it were up to me, I would just have a generic table without a title, but it isn't. Thanks for the quick reply! – Ryan4588 Jun 30 '14 at 16:45
If you're on Unix, `sed -i -e "2d" $CSV_FILE` will handle this for you. If you're on Windows, [cygwin](https://www.cygwin.com) includes sed. – hd1 Jun 30 '14 at 16:54
Thanks so much @hd1! I'll look into what you said, it's very helpful. – Ryan4588 Jun 30 '14 at 16:58
I get the error: "Fatal error: Class 'Spreadsheet_Excel_Reader' not found on line 12" – Ryan4588 Jun 30 '14 at 17:04
I'm dumb, need the class. Ignore last comment. – Ryan4588 Jun 30 '14 at 17:08
OK, so I get this error message now: Deprecated: Assigning the return value of new by reference is deprecated in reader.php on line 261 The filename is not readable – Ryan4588 Jun 30 '14 at 17:32
Deprecated is not an error, it should be seen as advice that the method is schedules for removal – hd1 Jun 30 '14 at 17:48
I solved the issue of the Deprecated message. Now all I receive is "filename file.xlsx is not readable". I tried changing the excel doc to .xls and same error. Any way to solve this? – Ryan4588 Jun 30 '14 at 17:50
What is the filename you're trying to import? – hd1 Jun 30 '14 at 17:53
What does .doc have to do with the error? I haven't used .doc's at all, nor were they relevent to the question? I am confused lol – Ryan4588 Jun 30 '14 at 17:55
let's say it is book1.xlsx – Ryan4588 Jun 30 '14 at 17:55
Try saving it in pre-2007 (xls) format and importing using the script. – hd1 Jun 30 '14 at 17:56
Same error, OP updated with my source so you can see if I made an error somewhere. – Ryan4588 Jun 30 '14 at 17:58
Do you see any error in my source that would make it explode upon upload? – Ryan4588 Jun 30 '14 at 18:14
Is there anything in xls_doc.bad or xls_doc.dsc? – hd1 Jun 30 '14 at 18:52
No, they do not exist. However, I'm not sure why that would make the first lines blow up. The problem, in my eyes, is that when the file is submitted the program doesn't know how to handle it, thus labels it as "unreadable" – Ryan4588 Jul 01 '14 at 11:32
Converting between .xls and .xlsx is more than a simple renaming of the file -- they are completely different formats. .xlsx is compressed XML, while .xls is binary. There is a [converter to CSV for both](http://hayageek.com/convert-xls-to-csv-in-php/), but they are not the same. – hd1 Jul 01 '14 at 17:19

How can an uploaded Excel file update an Oracle database?

1 Answers1