Upload Excel files and update mssql db

[helplakshmi]

Hi All,

I wish you a Happy new year!!!..

My requirement is to upload an excel file using PHP and to update the data in MSSQL. For which i have designed a screen so that the user can browse 2 input files and update the database. html code for the same

<table  cellspacing="3" cellpadding="3" style="color:#0000b9; background-color:#d7deec; ">
 <tbody>
    <tr>
          <td>
             <b> <label for="babmpath">BA-BM status file:</label> </b>
          </td>
           <td><input type="file" name="babmpath" id="babmpath"  />
         </td>
    </tr>
    <tr>
          <td>
             <b> <label for="eskal">Escalation file:</label> </b></td>
           <td><input type="file" name="eskal" id="eskal"  /></td>
     </tr>
     <tr>
           <td >
              <b><input type="submit" name="update"
                          value="Update Database"
                          onclick="return confirm('Are you sure that you want  to upload to the database?\n\ \nPlease close the excel file that you want to upload to the database.');" /> </b>
          </td>
       </tr>
      </tbody>
  </table>

The logic that i used to upload the files was:-- Once the user browse for these files and press update button. I am saving the path of the files in another 2 hidden input fields. I am passing the path to the stored procedure in MSSQL.

The code in stored procedure:-

ALTER PROCEDURE [dbo].[update_db] 
    -- Add the parameters for the stored procedure here
    @ba_bm_status    VARCHAR(100), 
    @eskal VARCHAR(100),
    @error VARCHAR(MAX) OUTPUT
AS
BEGIN
 
    SET NOCOUNT ON;
 
BEGIN TRANSACTION update_database
SET XACT_ABORT ON
 
 
--Importing data using distributed queries
 
SET @error = ''
IF(@ba_bm_status IS NOT NULL)
BEGIN
EXEC('SELECT * INTO temp FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=' + @ba_bm_status + ''',' +
 '''SELECT * FROM [qry_BA_Controlling (Report)$]'')');

Here @ba_bm_status is the path of the excel that i am sending from PHP. qry_BA_Controlling (Report) worksheet name in the excel.

Till now it is working fine. Uploading the data from excel and updating it on the server. (Testing is done on the server itself :-p). Now once the code has been deployed i mean made it online. Whenever the user tries to update the database. It is giving me an error message.

The reason behind it was:- As i am just passing the path instead of uploading the files. The path from where are the files are browsed is on the client side and i am searching for the files in the server side(I mean in MSSQL when i am uploading the files).
For example path of the file:- C:\Documents and Settings\l.varada\Desktop\Files\old\eroom_status.x ls

and on execution in MSSQL stored procedure(refer to the code above). It was not able to find the file in this path on the server.

Then i thought for an alternative solution as:- Upload the excel files on to the server and from the server to update the database.

With the little knoweldge i have and after browsing net i have found only method to uplaod file(with input type = file). I am using xampp server and mssql installed with odbc drivers. I have no idea where these files are getting saved on the server.

I would be glad If you can help me further in solving this further or suggest me an alternative solution :-)

Anyways thanks for your patience in reading this...

Regards,
Lakshmi.

[dgreenhouse]

You have to use a <FORM> element and the correct 'ENCTYPE' attribute set as: ENCTYPE="multipart/form-data".

i.e.

<!-- Stolen from the TUT referenced below...->
<form enctype="multipart/form-data" action="uploader.php" method="POST">
  <input type="hidden" name="MAX_FILE_SIZE" value="100000" />
  Choose a file to upload: <input name="uploadedfile" type="file" /><br />
  <input type="submit" value="Upload File" />
</form>
 
 

See: http://www.tizag.com/phpT/fileupload.php for a simple tutorial and come back when you've done that.