Solved

CSV file uploaded in php and inserted to oracle database

Posted on 2011-03-15
43
1,011 Views
Last Modified: 2013-11-10
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
Comment
Question by:Henk_du_Toit
  • 21
  • 15
  • 4
  • +2
43 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35136591
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
 

Author Comment

by:Henk_du_Toit
ID: 35136607
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35136625
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35136675
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
 

Author Comment

by:Henk_du_Toit
ID: 35136682
hey roads,

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

Thanks
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35137060
Unfortunately I do not know anything about connecting PHP with Oracle...
0
 

Author Comment

by:Henk_du_Toit
ID: 35137078
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35137107
Sure, just tell what you need as an output
csv -> query lines  "insert into table (ORN,DDAT,CHARGE) VALUES fromfile1,fromfile2,fromfile3
?
0
 

Author Comment

by:Henk_du_Toit
ID: 35137126
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35137177
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35137227
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
 

Author Comment

by:Henk_du_Toit
ID: 35137229
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
 

Author Comment

by:Henk_du_Toit
ID: 35137253
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
 

Author Comment

by:Henk_du_Toit
ID: 35137278
Meant to state the rules... They are quite simple:
No duplicates allowed with orno being guid
orno must start with SOA
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35137412
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
 

Author Comment

by:Henk_du_Toit
ID: 35141891
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35141924
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
 

Author Comment

by:Henk_du_Toit
ID: 35141977
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35142309
Sorry. I misunderstood the question.

I can't help with the php code.
0
 

Author Comment

by:Henk_du_Toit
ID: 35144915
Is there anyone that can provide me with further help?
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35144964
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Henk_du_Toit
ID: 35145060
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35145122
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
 

Author Comment

by:Henk_du_Toit
ID: 35145173
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35146062
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
 
LVL 5

Expert Comment

by:Khalid Mehmood Awan
ID: 35146063
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
 

Author Comment

by:Henk_du_Toit
ID: 35146132
Hi Roads,

I get error parsing...
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35146452
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
 

Author Comment

by:Henk_du_Toit
ID: 35151409
ok that worked now I get error committing...
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35151474
is the $db_conn there ?
Is the
include('connector.php');
included ?
0
 

Author Comment

by:Henk_du_Toit
ID: 35151539
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35151670
Are the values separated by TAB or ","
if TAB, use explode like this:

            $array = explode("\t",$value);
0
 

Author Comment

by:Henk_du_Toit
ID: 35151675
values are seperated by ","
0
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 500 total points
ID: 35151872
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
 

Author Comment

by:Henk_du_Toit
ID: 35151947
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35151978
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
 

Author Comment

by:Henk_du_Toit
ID: 35151996
// 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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35152018
Just switch the "// insert part" you have to this posted above, execute the script and post the result
0
 

Author Closing Comment

by:Henk_du_Toit
ID: 35152048
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
 

Author Comment

by:Henk_du_Toit
ID: 35152066
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
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35152079
Hallejuah !
Glad you sorted it out with your Oracle knowledge and my PHP.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35154084
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
 

Author Comment

by:Henk_du_Toit
ID: 35161035
awesome!! That's great man...

Thank you very much.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now