• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1707
  • Last Modified:

CSV file uploaded in php and inserted to oracle database

Hi,

I am sorry if this question has been answered before, but I have looked and googled many pages and can't seem to find what I want to do.

I have been updating the database every week by manually writing the code and then inserting values into my table. The table is called freight and have colums like this
ORNO                                     NOT NULL                     CHAR(9)
DDAT                                      NOT NULL                     DATE
CHARGE                                                                       NUMBER

what i would like to do is create a php file that will allow me to browse and select a file to upload, validate (orno must start with SOA, and no blank lines) the data and then insert into table.

Can someone please show me how to do this?
0
Henk_du_Toit
Asked:
Henk_du_Toit
  • 21
  • 15
  • 4
  • +2
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
I am not sure about PHP but if you have file with correct data, you can easily load data into table. You can use sqlloader utility or external table to get this done.
0
 
Henk_du_ToitAuthor Commented:
I can get it done the way I am doing it at the moment using sql insert statement. But I am looking for an easier way to get this uploaded to database.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
yes, easier way is to use sqlloader or external tables. just read thru this :

http://psoug.org/reference/externaltab.html - external tables
http://psoug.org/reference/sqlloader.html - sqlloader
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Lukasz ChmielewskiCommented:
Can be done with PHP but it's not in PHP zone.
By "the file" do you mean a simple text file ? And are the fields separated with any character ? I mean for example:
1;2;3;
4;5;6;

(ORNO,DDAT,CHARGE) ?
0
 
Henk_du_ToitAuthor Commented:
hey roads,

by the file I meant csv file... would be handy to have fields seperated as ('ORNO','DDAT','CHARGE')

Thanks
0
 
Lukasz ChmielewskiCommented:
Unfortunately I do not know anything about connecting PHP with Oracle...
0
 
Henk_du_ToitAuthor Commented:
i have the connection script for connecting to oracle... I just need to find a way to upload csv file validate it and then inserting to table.

Would you be able to help with that?
0
 
Lukasz ChmielewskiCommented:
Sure, just tell what you need as an output
csv -> query lines  "insert into table (ORN,DDAT,CHARGE) VALUES fromfile1,fromfile2,fromfile3
?
0
 
Henk_du_ToitAuthor Commented:
yeah i need to import x number lines from csv file in the format for example:
insert into freight values('SOA123456','28/02/2011','500')

Does that help?
0
 
Lukasz ChmielewskiCommented:
Well, take a look at this, we can start from it:

<html>
<body>

<?php
    if(!empty($_POST)){
        if ($_FILES["file"]["error"] > 0){
          echo "Error: " . $_FILES["file"]["error"] . "<br />";
        }
        else{
          $f = file($_FILES["file"]["tmp_name"]);
          foreach($f as $key => $value){
            // cut the whitespaces from the beginning and end
            $value = trim($value);
            $array = explode(",",$value);
            
            // trim the values for the database
            array_walk($array,trim);
            $query = "insert into table (ORNO,DDAT,CHARGE) values ('$array[0]','$array[1]','$array[2]')";
            echo"$query<br />";
          }
        }    
    }
?>

<form action="index.php" method="post" enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file" /> 
<input type="submit" name="submit" value="Submit" />
</form>

</body>
</html>

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I want to again mention sql*Loader.  Loading data into Oracle is what it was created for.  Why write a bunch of PHP code when a simple control file will work.

If you can provide sample data and the 'rules' for loading I'll see what I can come up with.
0
 
Henk_du_ToitAuthor Commented:
Yeah that works great! I can see all the information from the spreadsheet displayed... How do I now go from here to actually inserting the rows into the database?
0
 
Henk_du_ToitAuthor Commented:
Here is some sample data...
orno	datum	amount
SOA011111	4/02/2011	129.95
SOA011112	7/02/2011	291.51
SOA011113	7/02/2011	84.02
SOA011114	7/02/2011	79.52
SOA011115	7/02/2011	63.03
SOA011116	7/02/2011	126.92
SOA011117	7/02/2011	699.18
SOA011118	7/02/2011	72.6
SOA011119	8/02/2011	48.96
SOA011120	8/02/2011	59.17
SOA011121	10/02/2011	35.66
SOA011122	10/02/2011	608.74
SOA011123	9/02/2011	160.55
SOA011124	9/02/2011	74.06
SOA011125	11/02/2011	104.18
SOA011126	11/02/2011	89.07
SOA011127	11/02/2011	27.39
SOA011128	11/02/2011	56.32
SOA011129	11/02/2011	38.34
SOA011130	11/02/2011	59.51
SOA011131	11/02/2011	117.25
SOA011132	11/02/2011	118.62
SOA011133	7/02/2011	57.51
SOA011134	8/02/2011	69.36
SOA011135	7/02/2011	86.07
SOA011136	8/02/2011	69.36
SOA011137	8/02/2011	51.8
SOA011138	9/02/2011	26.08
SOA011139	9/02/2011	88.12
SOA011140	9/02/2011	55.9
SOA011141	7/02/2011	357.86
SOA011142	7/02/2011	71.19
SOA011143	11/02/2011	85.37
SOA011144	7/02/2011	65.21
SOA011145	10/02/2011	26.08
SOA011146	10/02/2011	65.21
SOA011147	10/02/2011	80.57
SOA011148	10/02/2011	73.12
SOA011149	8/02/2011	228.27

Open in new window

0
 
Henk_du_ToitAuthor Commented:
Meant to state the rules... They are quite simple:
No duplicates allowed with orno being guid
orno must start with SOA
0
 
slightwv (䄆 Netminder) Commented:
I put the data inline just for ease of testing.  You can easily add the file path and keep the text separate from the control file.

The control file is in the code windows below.

The 'no duplicates' is handled by the primary key in the table.  notice I added a 'BAD' row (starts with BAD).

Given the following table:

drop table tab1 purge;
create table tab1(
ORNO     CHAR(9) primary key,
DDAT     DATE,
CHARGE   NUMBER
);

Using the command:
sqlldr user/password control=mycontrol.ctl

You get the following results in the log file:

Record 1: Discarded - failed all WHEN clauses.

Table TAB1:
  39 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  1 Row not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.



options (skip=1)
load data
 infile *
 APPEND INTO TABLE Tab1
 WHEN (1:3) = 'SOA'
 FIELDS TERMINATED BY X'09' 
(
ORNO,
DDAT DATE 'MM/DD/YYYY',
CHARGE
) 
begindata
orno	datum	amount
BAD011111	4/02/2011	129.95
SOA011111	4/02/2011	129.95
SOA011112	7/02/2011	291.51
SOA011113	7/02/2011	84.02
SOA011114	7/02/2011	79.52
SOA011115	7/02/2011	63.03
SOA011116	7/02/2011	126.92
SOA011117	7/02/2011	699.18
SOA011118	7/02/2011	72.6
SOA011119	8/02/2011	48.96
SOA011120	8/02/2011	59.17
SOA011121	10/02/2011	35.66
SOA011122	10/02/2011	608.74
SOA011123	9/02/2011	160.55
SOA011124	9/02/2011	74.06
SOA011125	11/02/2011	104.18
SOA011126	11/02/2011	89.07
SOA011127	11/02/2011	27.39
SOA011128	11/02/2011	56.32
SOA011129	11/02/2011	38.34
SOA011130	11/02/2011	59.51
SOA011131	11/02/2011	117.25
SOA011132	11/02/2011	118.62
SOA011133	7/02/2011	57.51
SOA011134	8/02/2011	69.36
SOA011135	7/02/2011	86.07
SOA011136	8/02/2011	69.36
SOA011137	8/02/2011	51.8
SOA011138	9/02/2011	26.08
SOA011139	9/02/2011	88.12
SOA011140	9/02/2011	55.9
SOA011141	7/02/2011	357.86
SOA011142	7/02/2011	71.19
SOA011143	11/02/2011	85.37
SOA011144	7/02/2011	65.21
SOA011145	10/02/2011	26.08
SOA011146	10/02/2011	65.21
SOA011147	10/02/2011	80.57
SOA011148	10/02/2011	73.12
SOA011149	8/02/2011	228.27

Open in new window

0
 
Henk_du_ToitAuthor Commented:
slightwv, thanks for your reply... given the above, how do I actually select the csv file to upload it? Also where can I get sqlldr from? Had a look to download it, but cannot find it...

Sorry if I sound dumb, but have no idea how it works...
0
 
slightwv (䄆 Netminder) Commented:
Upload the csv file?  I don't understand.

The csv is not on the machine where the oracle client is installed?

Sqlloader is part of the oracle client install.  It should be in the 'utilities' part of the options.
0
 
Henk_du_ToitAuthor Commented:
no it's not, this is why I wanted to use php code as per Roads comment. I can select the file easily and displays all the data onscreen. The only part missing now is for me to insert this into my table...
0
 
slightwv (䄆 Netminder) Commented:
Sorry. I misunderstood the question.

I can't help with the php code.
0
 
Henk_du_ToitAuthor Commented:
Is there anyone that can provide me with further help?
0
 
Lukasz ChmielewskiCommented:
Can you show your php code for connecting to oracle ?
We can integrate the upload with the insert. Does the code I provided work for you ? I mean - are the queries shown ?
0
 
Henk_du_ToitAuthor Commented:
yes the queries are shown exactly how I want them. the connection to oracle is just including the file like include 'connector.php'; but here is the connection string as well.
$db="
	(DESCRIPTION=
	(ADDRESS_LIST=
	(ADDRESS=
	(PROTOCOL=TCP)
	(HOST=localhost)
	(PORT=1521)))
	(CONNECT_DATA=
	(SERVICE_NAME=sapert)))";

$db_conn = ocilogon( $username, $passwd, $db);

Open in new window

0
 
Lukasz ChmielewskiCommented:
I guess this would be something like this:



<html>
<body>

<?php

   include('connector.php');

    if(!empty($_POST)){
        if ($_FILES["file"]["error"] > 0){
          echo "Error: " . $_FILES["file"]["error"] . "<br />";
        }
        else{
          $f = file($_FILES["file"]["tmp_name"]);
          foreach($f as $key => $value){
            // cut the whitespaces from the beginning and end
            $value = trim($value);
            $array = explode(",",$value);
            
            // trim the values for the database
            array_walk($array,trim);
            $query = "insert into table (ORNO,DDAT,CHARGE) values ('$array[0]','$array[1]','$array[2]')";
            $a[] = $query; 
          }
        }    
        
       
        // THE INSERT PART
        foreach ($a as $v) {
        $s = oci_parse($db_conn,$a);
        $r = oci_execute($s); // Committed
        }

    }
?>

<form action="index.php" method="post" enctype="multipart/form-data">
<label for="file">Filename:</label>
<input type="file" name="file" id="file" /> 
<input type="submit" name="submit" value="Submit" />
</form>

</body>
</html>

Open in new window

0
 
Henk_du_ToitAuthor Commented:
Thank you very much for your help thus far... I have put your code in my index.php file and I can select the file to load but when clicking submit, nothing seems to happen?
0
 
Lukasz ChmielewskiCommented:
Switch the "insert part" of the code to this:

        // THE INSERT PART
        foreach ($a as $v) {
        $s = oci_parse($db_conn,$a) or die("error parsing");
        $r = oci_execute($s) or die("error cimmiting"); // Committed
        // echo the inserted query
        echo"INSERTED: $v";
        }

Open in new window

0
 
Khalid Mehmood AwanCommented:
oracle connection method is attached with this comment.

you can open this text file.
keep on reading the line
every time you read the line , split it by tab, comma or what ever character you have
check the index 0 of array where you will keep splitted values of line
start reading data from the line next to orno
keep on splitting and saving to database untill file ends or some other point in files reaches.
<?php

	putenv("ORACLE_SID=STATNOC");
	$username = "noc";
	$passwd = "mobilink";
	$db="(DESCRIPTION=
			  (ADDRESS_LIST=
				(ADDRESS=(PROTOCOL=TCP)
				  (HOST=10.114.2.143)(PORT=1521)))
				   (CONNECT_DATA=(SERVICE_NAME=STATNOC)))";
			  
	$conn = OCILogon($username, $passwd, $db); 

?>

Open in new window

0
 
Henk_du_ToitAuthor Commented:
Hi Roads,

I get error parsing...
0
 
Lukasz ChmielewskiCommented:
Try switching this line
$s = oci_parse($db_conn,$a) or die("error parsing");
to
$s = oci_parse($db_conn,$v) or die("error parsing");
0
 
Henk_du_ToitAuthor Commented:
ok that worked now I get error committing...
0
 
Lukasz ChmielewskiCommented:
is the $db_conn there ?
Is the
include('connector.php');
included ?
0
 
Henk_du_ToitAuthor Commented:
yes. I even double checked that it connects by doing a query to get data from a different table and that works... So i took one of the displayed queries in the array and try submitting it manually and it said invalid month... So is there somehow that I could set the date format for column ddat as dd/mm/yyyy?
0
 
Lukasz ChmielewskiCommented:
Are the values separated by TAB or ","
if TAB, use explode like this:

            $array = explode("\t",$value);
0
 
Henk_du_ToitAuthor Commented:
values are seperated by ","
0
 
Lukasz ChmielewskiCommented:
try switching this section

            // trim the values for the database
            array_walk($array,trim);
            $ddat = date('d/m/Y', strtotime($array[1]));
            $query = "insert into table (ORNO,DDAT,CHARGE) values ('$array[0]','$ddat','$array[2]')";
            $a[] = $query;
0
 
Henk_du_ToitAuthor Commented:
Tried that but still no luck...

So i took out the date column out all together out of csv and out of query and altered the table to drop the date column to see if i can insert values without date and ....................success!

so it has something to do with the date... don't know how we are going to get around it...?
0
 
Lukasz ChmielewskiCommented:
Can you post here the output of that part ?

        // THE INSERT PART
        foreach ($a as $v) {
        //$s = oci_parse($db_conn,$v) or die("error parsing");
        //$r = oci_execute($s) or die("error commiting"); // Committed
        // echo the inserted query
        echo"INSERTED: $v<br />";
        }
0
 
Henk_du_ToitAuthor Commented:
// THE INSERT PART
        foreach ($a as $v) {
        $s = ociparse($db_conn,$v) or die("error parsing");
        $r = ociexecute($s) or die("error commiting"); // Committed
        // echo the inserted query
        echo"INSERTED: $v<br />";
        }

Is this what you want me to put?
0
 
Lukasz ChmielewskiCommented:
Just switch the "// insert part" you have to this posted above, execute the script and post the result
0
 
Henk_du_ToitAuthor Commented:
I GOT IT TO WORK!!!

I inserted another query that does this:

$cmdstr = "ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY'";
$parsed=ociparse($db_conn, $cmdstr);
ociexecute($parsed);

And now it works! Great, thank you so much for your help, you are indeed a legend!!
0
 
Henk_du_ToitAuthor Commented:
now this does not do any validation checks on the data...

Is there a way to make sure that all orno start with "SOA"?
0
 
Lukasz ChmielewskiCommented:
Hallejuah !
Glad you sorted it out with your Oracle knowledge and my PHP.
0
 
Lukasz ChmielewskiCommented:
Well, this would be the section where the queries are added to array and checking for SOA - if the SOA is not there, do not add a csv line to execution

            if(substr($array[0],0,3) == "SOA"){
                $ddat = date('d/m/Y', strtotime($array[1]));
                $query = "insert into table (ORNO,DDAT,CHARGE) values ('$array[0]','$ddat','$array[2]')";
            }
            else
                $a[] = $query;

instead of
            $query = "insert into table (ORNO,DDAT,CHARGE) values ('$array[0]','$ddat','$array[2]')";
            $a[] = $query;
0
 
Henk_du_ToitAuthor Commented:
awesome!! That's great man...

Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 21
  • 15
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now