Plsql to Convert Xlsx to Html and Upload From Html
I was directed here from some other forum in order to find how to import the data from an excel spread sheet to a course. There is no link included that will show you how to do this. In fact, the only links were to the front page of the Oracle Engineering science network. This is the same as a kid wanting to know well-nigh asteroids and y'all drop her off in front of the metropolis library. Cheers, simply I already know about the OTN, and I know that finding anything in information technology is simply a matter of luck. The to the lowest degree you lot could accept done was provided a link to the specific page that held the data.
Tom:
I take an access table that I am trying to accept to oracle 8.1.7. I export the tabular array in access to ODBC database using a data source I create for my oracle database and the DataDirect 4.1 Oracle Driver ( I as well tried microsoft ODBC for oracle).
The export happens and then When I become to oracle, I run across the tabular array when I practise "Select table_name from user_tables".
However when I do "desc table" or "select from tabular array" information technology tells me that table does not exist.
I cannot even delete the table.
Do yous know what is happening?
June 06, 2002 - four:31 pm UTC
is the tabular array proper name in mixed case? if so, brand sure to use quoted identifiers
desc "tableName"
select * from "tableName"
Tom:
Aye I found that table proper name is in lowercase. I re-did this from access and named table uppercase.
this is weird. I guess when I do "desc table" information technology is always looking for capital letter table.
ane. Tin i change tabular array proper name from lowercase to capital letter.
2. to delete information technology shall i do "drop table "emp" ";
iii. Can I utilize Oracle Migration Assistant to move data from all kind of databases.
4. I installed oracle ODBC driver for 8.1.7 database. However when I endeavour to create a data source I tin can not meet it listed in the list of drivers available in the control console. Do you have to copy files to a sure directory in windows?
THank yous
June 06, 2002 - 9:36 pm UTC
Well, in ANSI standard sql -- identifiers are instance insensitive.
In sqlserver "standard" they are example sensitive.
We are not looking for "upper-case letter" in every bit much as they stored information technology in a particular example.
one) one method:
create view tableName as select * from "tableName"
otherwise, create the tabular array in Capital in their stuff....
2) to delete, utilise 'delete from "emp"'. to Driblet -- very dissimilar from delete, use 'driblet tabular array "emp"'
iii) at that place is the migration assistant with the "upgrade process" and there is the migration workbench with a tool to migrate from sqlserver/informix/db2/etc to Oracle.
The migration toolkit/workbench -- you go that for a database version/release. We accept many toolkits for yous to use. Y'all demand to get the toolkit that applies.
4) no idea, never used odbc in my life -- still. sad.
For Question iv.
Ensure the <Oracle Abode Path>\bin is set in environment path.
Alternatively.....more a quick fix than a proper solution
Copy the SQORA32.DLL (not sure if aforementioned in eight.ane.7) from <Oracle Dwelling Path>\bin to the Windows\system32 directory
Tom:
ane. If y'all are converting an INFORMIX or SYBASE database, woudl the workbench re-create all objects (i.e. tables, indexes, principal keys, foreign keys, sequences, stored procedures.?
2. WOuld information technology convert the informix stored procedure to a PL/SQL stored procedure.
June 08, 2002 - 5:09 pm UTC
1) It would exercise "its all-time". tables, indexes, main key, foreign keys, sequences - - pretty much dead on. procedures, well.... lawmaking conversion is always a tricky matter. Say it follows the eighty/20 dominion -- it does the rote stuff, leaving the interesting 20% for you.
2) it would "effort".
Tom:
When using the workbench to migrate informix database it takes all tables and objects and create them in the oracle database nether schema "informix" and not the oracle dba schema.
Since awarding uses the developer acount to log in I have to refer to all informix tables using schema.tabular array.
Can you just export all objects out of informix schema and import it into devadmin schema?
Merely and so delete the informix schema or you lot have to do information technology though unix for this?
Is at that place a better fashion of doing this?
Thanks,
September 07, 2002 - vi:52 pm UTC
Well, there is probably a setting somewhere in at that place (oasis't used the workbench personally) to set the schema -- i would have to imagine in that location would be but suppose there isn't.
A simple:
exp userid=informix/password owner=informix
imp userid=devadmin/password fromuser=informix touser=devadmin
will go it for you.
Tom:
I accept a .dbf file that I opened up in Access and then saved it into oracle table using relieve to ODBC database.
the oracle table is getting created fine. However the table has nigh 1000 records and I am only getting 900. It gave me a message"Microsoft Admission was unable to append all data tothe table. The contents of fields in 100 records were deleted. If data was deleted, the data you pasted or imported doesn't match the field data types or the field size belongings in the destination table."
I practice not have an oracle table to begin with. DO you lot think it is the ODBC driver issue?
Whatever hints.
January 08, 2003 - 4:39 pm UTC
I have no clue -- I've never used ODBC in my life
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:711825134415 <code>
is an alternative, you'll exist able to meet what is in the dbf file (assuming it is a dbase file)
or configure the generic connectivity that comes with the database and "select * from it" in sqlplus and see what is in at that place (and insert into oracle_table select * from dbf_file" if you like.
Or, open a trouble report with neb.gates@microsoft.com -- he might at present how to get more information from access ;)
I want to use your second choice "Using OLE automation".
Can you requite me a code instance of how I tin read the OLE data in my class to insert it again in my db?
Apr 09, 2003 - 9:41 am UTC
Nope -- never used OLE, don't have OLE... Don't even take an operating organisation capable of running OLE...
endeavor otn.oracle.com nether give-and-take forums -> programmer
Tom:
1. If you covert the .xls file to .htm what tool exercise you use to parse the html table?
2. i have a user spreadsheet that looks like a form. You have address info at the elevation and items requested at the bottom in tabular format. When I saved .xls to .csv file and I looked at the .csv at that place were a lot of commas in between information. Would this format exist difficult to convert to oracle?
June 06, 2003 - 11:32 am UTC
ane) DIY (do information technology yourself). I do not recommend this approach, information technology is "difficult"
two) don't know, use your cognition of Oracle and your application to see what it would accept to load it up and process it.
Hi,
We had the same problem. The approach we took to solve this problem, we had written a macro, which volition generate insert and update statments from the excel sheet and so cutting and paste those insert or update statements. Now we have migrated to Oracle 9i, so we are using external table and reading the text file into database.
<Quoting tom>
"open a trouble report with beak.gates@microsoft.com -- he might at present how to
get more information from access ;)
<End quote>
Well, as per ur proffer, I asked Gates about the problem, and he says it is useless to insert the data into Oracle, why not use SQL Server ? Anyways Oracle products are highly non-user friendly.....
Happy ?!
June 06, 2003 - 8:07 pm UTC
did he give you that broken, not functioning keyboard that drops vowels, extremely of import characters in this particular language equally well?
probably a new "open up standard" they are inventing for all of the states to adapt to over over in redmond I gauge.
but really -- do you honestly look me to debug admission? a product that for some reason just refused to actually install on the operating system I choose to use? sorry -- no can do.
(curious, how did you ask bill? every bit far equally I know, they don't actually do annihilation similar over there.... what is the URL? I do have some issues with word on linux, maybe he tin can help?)
Ha ha that was a good answer Tom. ;)
Honey Tom,
Can't nosotros use heterogeneous connectivity to read data from a Microsoft Excel File? Though it needs little bit of configuration, it works fine with Excel, Admission files.
June 08, 2003 - ix:41 am UTC
if you accept odbc access to excel, sure.
This might be repeating what you did not want to do in the kickoff identify, merely if you tin can salve the Excel files every bit CSV, then you tin read them every bit external tables using the technique described in:
</lawmaking> http://asktom.oracle.com/pls/asktom/f?p=100:xi:::::P11_QUESTION_ID:6611962171229 <code>
As far as COM automation, I found a lot from google on this when writing a sync between Oracle and Outlook tasks. You could fifty-fifty write something using windows scripting host to open up all excel files in a directory, then save them as a CSV file on a Network share on your db car. On the db side, a db job that runs every ten minutes and looks for files it hasn't already imported and reads them in as an external table would work.
No affair what, I would shy away from ODBC. It's a "least common denominator" connection. You can't leverage Oracle features such every bit packages or stored procedures, return ID's of rows you merely inserted, etc. like you can with OLE DB or Objects for OLE (OO4O). On the Microsoft Side, working with a recordset returned as an object from an OLE DB connection is much easier and much more than powerful than what you tin can do with ODBC. Just my 2 cents.
Tom:
i. On an HTML class can i have the user open the excel file, salvage it to .csv file and and then have him re-create the ASCII text and paste it into text field on the class and so submit the grade to a pl/sql procedure which saves the text to a long cavalcade in a temp table. So I can search for information by counting the number of commas in the record/column created and retrieve it and insert information technology into the oracle table I desire. IS this doeable in oracle?
ii. or upload the .csv file from the browser into the unix file directory and so apply UTL_FILE to read lines and parse the data and insert into oracle table?
3. Why do you say parsing the HTML file is difficult? Would an excel to XML conversion and then parsing to oracle be much easier?
Thank you,
June 10, 2003 - 8:04 am UTC
1) if they cutting and paste information technology into a text field, it'll be going to a PLSQL variable of type varchar2.
there will exist no temp table, information technology'll simply be a string of UPTO 32k in size.
If, you have them save as CSV, you lot tin take the FILE UPLOAD it to a clob. parsing the clob is very easy.
2) you lot tin upload to a CLOB
3) difficult as compared have a csv file. save it as html, parse it. tell usa if the lawmaking to do that was more than hard.
TOm: 1. On Option 1, the max text I can pass using pl/sql variable is 32 K. what about if there is more text. does oracle cut it and save the first 32 1000 or information technology will not laissez passer at all? 2. Why do y'all utilise CLOB column type and non LONG. Is it considering CLOB is more like VARCHAR2 and LONG is hard to parse/manipulate? 3. I tried to upload a file using an HTML form but the text was not saved in the table but rather the path of the file? Practise you know why? PROCEDURE TEST_SCRIPT as begin htp.p('<HTML>'); htp.p('<Head>'); htp.p('<SCRIPT Language="JavaScript1.i">'); htp.p(' <!--'); htp.p(' function doSubmit()'); tp.p(' {'); htp.p(' document.my_form.submit();'); htp.p(' }'); htp.p(' //-->'); htp.p(' </script>'); htp.p(''); htp.p(' </HEAD>'); htp.p(''); htp.p(' <BODY>'); htp.p(' <a href="javascript:doSubmit()">Click me to submit the form</a>'); htp.p(' <class action="test_insert" name=my_form method=post>'); htp.p('<P>Files:<INPUT TYPE="file" ENCTYPE="multipart/form-data" Proper name="resume" </P>'); htp.p(' </form>'); htp.p(' </Torso>'); htp.p(' </HTML>'); finish; Procedure TEST_INSERT ( resume in varchar2 ) as begin insert into test9(id,file_text) values (1,resume); commit; stop; SQL> desc test9 Name Null? Type ----------------------------------- -------- ------------------------ ID NUMBER(five) FILE_TEXT LONG SQL> select * from test9; ID ---------- FILE_TEXT ---------------------------------------------------------------------- 1 c:\ims\t1.txt
June 10, 2003 - 8:02 pm UTC
one) try it and meet? most browsers (used to) limit you to 30,000 characters in a text field so it was moot.
I gotta feeling that information technology only gets truncated. annihilation that could forseeably even approach 32k should be a FILE UPLOAD.
2) (note: should accept said BLOB, non CLOB) long/long raw as database column types are deprecated and if you had more than then 32k in a long raw (as long is non a choice with mod_plsql and uploads) you would have NO WAY to parse it at all in plsql.
a blob would exist the simply logical datatype to employ.
3 did you read the documentation well-nigh how file upload works with mod_plsql? y'all got passed a "key" if you will to a document tabular array where the data actually exists.
check out the docs.
June sixteen, 2003 - 12:16 pm UTC
ane) no i didn't, read closer
question was:
...How can I upload a file from customer motorcar to server file system (not
database) .....
reply is:
file organisation upload is not supported via mod_plsql.
pregnant to upload to a file system (not the database) is non supported. You tin upload from your client machine INTO the database.
come across
</code> http://docs.oracle.com/docs/cd/A97335_01/alphabetize.htm <code>
affiliate one, file upload download.
Tom:
Thanks,
1. Is there a manner to notice out the name of the document table?
ii. To parse the data you would parse information technology in the document tabular array or you would copy it to the original table or it does not thing really?
iii. When I save the .xls into .csv (text format) some information comes out betwixt double quotes while other does non. Would I even so be able to pull the needed info from this using PL/SQL by searching for commas or double quotes then using SUBTR/INSTR.
Visitor Code:,,DD11,,,Appointment:,5/eight/03,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
,,,,,,,
Agency:,,Land LIBRARY,,,,,
,,"AGENCY, Middle, Due east",,,,Center,
Accost:,,222 LEE Place,,,,,
,,"DENVER, CO 22215",,,,,East
,,,,,,,
Cheers,
June 17, 2003 - 7:05 am UTC
1) read the documentation please.
</code> http://docs.oracle.com/docs/cd/A97335_01/apps.102/a90099/feature.htm#1006876 <lawmaking>
you actually create and proper name this tabular array if you lot like.
2) exercise what is all-time, easiest and about efficient for you. It is just "code" at this point. Use skillful algorithms, endeavour different methods, write something efficient and maintainable that performs well.
three) depends on how yous write your parsing lawmaking! Of course you *can* -- it just makes it a niggling trickier and then only "search for commas", you have to make certain y'all are NOT in quotes when yous discover a comman.
Exporting data to CSV and then using it every bit external table or by SQL*Loader is a good approach, but if it doesn't fit to the specific needs Oracle Objects for OLE may be used. And they come with documentation and samples just for Excel.
And they are downloadable from OTN.
BTW, I unremarkably use them in contrary direction - to brand reports in Excel format from data in Oracle.
Tom,
I am trying to load an Excel sail data into Oracle. The column which has time, is in HH:MI AM format. But when I pick the value from Excel, I become 0.445138888888889. Is this in some different format that I accept to convert while inserting into Oracle ?
I know, this is more like VBA question just I thought there might be Oracle way to catechumen this number into time.
Thanks in advance !
Ignore my question, found the problem !!!
A reader, July 11, 2003 - 11:19 am UTC
Did anyone tried using Excel 2003 XML iterface for importing/manipuating Oracle 9i Information? Read that even it has validation funcionality using smart tags.
Whatever feedback is welcome.
thanks,
Chowdary
How-do-you-do Tom,
Thank you, Y'all are doing a corking piece of work.
Delight can you explain me how to load data from excel sheet into Oracle table with an example.considering Iam unable to search in OTN.
Thanks in Accelerate,
Srinivas
March 31, 2004 - 8:51 am UTC
why can't you search in otn? if you tin search hither, you can search there.
but in whatever example, I would open the spreadsheet, file save as CSV and employ sqlldr.
Hello folks,
I have put together a script to extract data from ODBC sources, or direct from .xls (MS Excel) or .mdb (MS Access); and dump information technology out as csv, tab delimited, pipe delimited, or html. I am a unsure of how good it is with Access, considering I do non have Access - I have merely got a sample database.
Yous can discover it on </code> http://www.orafaq.net/scripts/win/dumpodbc.txt <code>
Please presume that the script CONTAINS A VIRUS, until y'all accept tested it throughly on your exam organisation. Read the comments at the top of the file, which includes the requirements. You lot volition demand to save it as 'dumpodbc.js'.
For loading into oracle, I would utilise TAB or Piping delimited. Its just so much easier than CSV because of the rules well-nigh quoting.
Takmeister
A reader, December 01, 2004 - 4:46 pm UTC
I used HTMLDB to upload a CSV file into the wwv_flow_files tabular array.
I dont desire to write code to parse the csv (terminated by "," optionally enclosed past '"' and all), the external table characteristic looks perfect for this.
How can I dump the CLOB containing the CSV file into a temp file and use the external table characteristic to read this temp file? i.e. insert into real_table select * from et;
Thanks
February eighteen, 2005 - 3:05 pm UTC
you would have to use utl_file to write it out.
OK I got so far
CREATE OR Supersede PROCEDURE blob_to_file (
p_id IN NUMBER,
p_filename IN VARCHAR2
)
IS
l_blob Hulk;
l_fp utl_file.file_type;
l_size PLS_INTEGER;
BEGIN
SELECT blob_content INTO l_blob FROM wwv_flow_file_objects$
WHERE id=p_id;
l_size := dbms_lob.getlength(l_blob);
l_fp := utl_file.fopen('upload',p_filename,'w');
EXCEPTION WHEN NO_DATA_FOUND
And so RAISE_APPLICATION_ERROR(-20000,'No such file with id='||p_id);
END;
/
How can I read the lob and write it to the file?
Thank you
Doh!
A reader, Feb 18, 2005 - 3:49 pm UTC
I accept the same problem as discussed here, but with 1 "niggling departure" - all the job MUST be done under UNIX-compartible surround. So it's impossible to use such Windows-specific technologies as OLE, ODBC, OO4O, VB(S) and and then on. And then I need some tool to read .XLS files nether UNIX directly into Oracle. Or, at least, some tool to catechumen .XLS files under UNIX into some temporary format (CSV, for example) and then utilize some my-own-written software to import temporary file into Oracle. But there is no info here on how to do that.
July 01, 2005 - 10:08 am UTC
that is because this is non "askbill@microsoft.com" I suppose? Why would I know all virtually converting XLS into something under unix?
although point #i is notwithstanding valid, the reference to Text.
only y'all might google around to see if there are whatsoever unix utilities to convert an XLS into something more friendly.
You lot might exist able to automate openoffice for case to batch catechumen them or something.
How-do-you-do,Tom,
I have a screen written in Forms6i where the user writes the name of ascii file that is on unix server and when he presses on the push - there is called process from bundle that accepts file name and reads the ascii file by utl_file.get_line. Today the customers whant to load the data direcly from excel file that is on their local calculator.
I want to do that like this: when the client presses on push in the Forms6i screen i desire to activate pll from forms which read the data from Excel file into array and later on calling pll i want to call the database store procedure that take the array as parameter and put information technology into oracle table.I want to read the data from Excel file
with OLE2.I tried to do information technology but haven't succed.Information technology weel help me if show how to write such pll with ole2 that is activating from Forms 6I.
Thank you very much.
Mari
July 25, 2005 - 8:17 am UTC
otn.oracle.com -> discussion forums
lamentable but I oasis't touched forms in over a decade now...
May you refer me to another site that has the examples
off plls from FORM6I that load excel to oracle by ole2?
Give thanks's a lot,
MARY
July 25, 2005 - 11:13 am UTC
otn.oracle.com -> give-and-take forums
it is upwards and running from where I sit down.
Here is the Macro that I wrote in Excel to export/append data to an Oracle Table. It starts at the beginning A1 and uploads to the table one line at a time. It may not be the most efficient plan every bit far as loading fourth dimension, but for my purpose (an average append of 100-300 lines per day) it works fine. Since I already have to run the Macro daily to exercise Financial Adjustments, information technology just handles the backup without having to use another tool.
Sub UploadData()
'Set Variables For Login
Dim logon Every bit String
Allow logon = "loginName"
Dim password As String
Let password = "Countersign"
'Set Variables for Upload
'Brand certain that your variable accept the same data type as
'your Oracle Tabular array
'I had an issue with the Excel Date Format, so I used string
'then edited it in the SQL statement
Dim Reference As String
Dim LoanNumber As String
Dim Payment As Double
Dim Batch Equally String
Dim SQLArray As String
Dim AddDate Equally Cord
Dim SQLBegin Equally String
'Loop and retrieve the data that needs to be entered
'into the Oracle DB
Range("A1").Select
Do Until Option.Outset(0, 1).Value = ""
LoanNumber = Pick.Value
Reference = Option.Get-go(0, ane).Value
Payment = Selection.Showtime(0, 2).Value
Batch = Selection.Offset(0, iii).Value
AddDate = Pick.Kickoff(0, iv).Value
SQLBegin = "INSERT INTO BILL_PAYER_BATCH_HIST (REFERENCE, BATCH, LOAN_NUM, AMOUNT, ADD_DATE) VALUES ("
SQLArray = SQLBegin & "'" & Reference & "','" & Batch & "','" & LoanNumber & "'," & Payment & ", TO_DATE('" & AddDate & "','MMDDYYYY'))"
With ActiveSheet.QueryTables.Add(Connection:=Assortment(Assortment( _
"ODBC;DSN=DATAMART;UID=" & logon & ";PWD=" & countersign & ";DBQ=;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=x;LOB=T;RST=T;FRL=F;PFC=one thousand;TL" _
), Assortment("O=0;")), Destination:=Range("Z1"))
.Sql = Array(SQLArray)
.FieldNames = Truthful
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = Faux
.HasAutoFormat = Fake
.BackgroundQuery = Fake
.TablesOnlyFromHTML = Imitation
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = False
Stop With
Selection.Offset(ane, 0).Select
Loop
End Sub
If you want to load Microsoft Access onto oracle, forget odbc go to
</code> http://www.hxtt.cyberspace/alphabetize.jsp <code>
and purchase their HXTT Admission package.
This allows you to use a real language to load the MS data onto a real database. Because it is a Type IV JDBC driver you can run your java loading routine on a real operating system (similar solaris) or loadjava and do it from inside oracle using the embedded jvm
how exercise I post my question in your spider web site?
May 02, 2006 - iii:38 am UTC
well, you but did postal service one.
When I am taking them (there is one of me), at that place is a button on the home page - where right now it probably says "deplorable, I have a backlog".
I created a ameliorate process than the one before:
Sub AppendOracleTable()
'Create and gear up login data variables
Dim logon Every bit Cord
Let logon = "login"
Dim countersign As String
Allow countersign = "countersign"
'Ready Variables for Upload
Dim Reference As String
Dim LoanNumber Equally String
Dim Payment As Double
Dim Batch Equally Cord
Dim EffDate Equally Date
'Create and Prepare Session / Create Dynaset = Column Names
Dim OraSession As Object
Ready OraSession = CreateObject("OracleInProcServer.XOraSession")
Dim nst Equally String
nst = "link.odbc.com"
Dim OraDatabase As Object
Set OraDatabase = OraSession.OpenDatabase("" & nst & "", "" & logon & " / " & countersign & "", 0&)
Dim Oradynaset Every bit Object
Gear up Oradynaset = OraDatabase.DBCreateDynaset("SELECT * FROM CARTERCH.loan_list", 0&)
Range("A2").Select
Practice Until Pick.Value = ""
LoanNumber = Selection.Value
EffDate = Selection.Offset(0, ane).Value
Oradynaset.AddNew
Oradynaset.Fields("LOANNUMBER").Value = LoanNumber
Oradynaset.Fields("EFFDATE").Value = EffDate
Oradynaset.Update
Selection.Beginning(1, 0).Select
Loop
Finish Sub
You can add equally many fields as you like to insert many rows of information.
Thanks for this piece of code. I got it working when I removed "" & nst & "" from the OpenDatabase method and replaced with my ODBC Service Name.
nst = "link.odbc.com"
OraSession.OpenDatabase("" & nst & "", "" & logon & " / " & password & "", 0&)
Thanks,
Paul
Tom:
Concerning your 1st solution on automating the loading of excel spreadsheet into an oracle table tin can you lot elaborate how your proposed solution of using intermedia to convert the spreadsheet into big html and so parse it and load it into an oracle table?
I am trying to create the solution on mod_plsql web application. For example:
a. Create the html form for user.
b. You lot allow the user select the file on his customer and then submit it.
c. and so what how you invoke intermedia and how practise you create the html file and you parse that.
is there an example of how to do this anywhere or tin can you evidence us how?
2. How do you handle the spreadsheet if permit united states of america say it has header information (like date, po number) and then columns of items, quantities, and prices?
iii. Has there been another newer solutions wiht 8i/9i since this was written back in 2001?
cheers,
August 12, 2006 - 7:48 pm UTC
step 1; load into the database (a blog)
step 2; using text and the filter functions, catechumen the binary xls into text html
footstep 3; parse abroad (this is the hard part)
writing an html parser in plsql is going to exist "non-trivial"
if your server is windows - you could always put the file into the Bone after uploading it and use ODBC and the generic connectivity gateway also
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206#18830681837358 <lawmaking>
Tom:
1. It looks the upload of excel into blob column and and then converting into html and and so parsing the html going to be difficult task.
a. For the parser to work the format of the file has to be always fixed and any changes in colum or row formats will screw up the pl/sql parser. correct?
b. how practice y'all convert a binay xls into html file?
2. The excel file is on user machine on windows xp. The oracle database is on unix. Are you saying the second solution using ODBC would non work (Every bit a user solution)? What I did a few times (developer solution not end user) is to take the spreadsheet and salvage information technology to Access and so use ODBC to save that Access file into oracle table. Then you lot take to parse the data.
three. Since Excel tin can save a file in comma delimited format is not in that location a way to upload that file from the user machine into oracle server so load that into an oracle table? would y'all be using sqlloader here?
August 14, 2006 - x:39 am UTC
one) yes. a) depends on how flexible you would code it b) I prepare a spreadsheet with two columns, 4 rows. row 4 was the sum of rows ii and 3 (formula) and row 1 was column headings (just used "column ane" and "column two") In 10g: ops$tkyte%ORA10GR2> exec ctx_ddl.create_policy( 'my_policy', 'ctxsys.inso_filter' ); PL/SQL process successfully completed. ops$tkyte%ORA10GR2> create or supervene upon directory MY_DIR as '/home/tkyte' 2 / Directory created. ops$tkyte%ORA10GR2> declare 2 l_bfile bfile; iii l_clob clob; 4 begin 5 l_bfile := bfilename( 'MY_DIR', 'Book1.xls' ); 6 dbms_lob.fileopen( l_bfile ); 7 ctx_doc.policy_filter( 'my_policy', l_bfile, l_clob, faux ); 8 dbms_output.put_line( l_clob ); 9 cease; 10 / <html><body><p/> <div dir="ltr"> <table border="1"> <th nowrap width="5" bgcolor="#ffffff"> </th> <th bgcolor="#ffffff" width="64">A</th> <th bgcolor="#ffffff" width="64">B</thursday> <tr> <thursday bgcolor="#ffffff">1</th> <td align="left"><font face="Arial" size="ii"> column i</font></td> <td align="left"><font face up="Arial" size="2"> column 2</font></td> </tr> <tr> <th bgcolor="#ffffff">two</thursday> <td marshal="correct"><font face up="Arial" size="2"> one</font></td> <td align="right"><font face up="Arial" size="ii"> 2</font></td> </tr> <tr> <th bgcolor="#ffffff">three</th> <td marshal="right"><font face="Arial" size="2"> 3</font></td> <td marshal="correct"><font confront="Arial" size="2"> 4</font></td> </tr> <tr> <thursday bgcolor="#ffffff">4</th> <td marshal="correct"><font face up="Arial" size="2"> 4</font></td> <td align="right"><font face="Arial" size="2"> 6</font></td> </tr> </table> <p /> </div> <br/><br/><br/></body></html> PL/SQL procedure successfully completed. ops$tkyte%ORA10GR2> declare 2 l_bfile bfile; 3 l_clob clob; 4 begin 5 l_bfile := bfilename( 'MY_DIR', 'Book1.xls' ); 6 dbms_lob.fileopen( l_bfile ); seven ctx_doc.policy_filter( 'my_policy', l_bfile, l_clob, true ); 8 dbms_output.put_line( l_clob ); ix end; 10 / A B i cavalcade 1 column 2 2 ane ii 3 3 4 4 4 6 PL/SQL procedure successfully completed. prior to 10g, ifilter existed (search this site for that) 2) I don't know what "second solution" you mean - simply if y'all meant from my original answer - you lot can apply ODBC to query the spreadsheet from your own custom application, you can use odbc to write to oracle. 3) sqlldr or any one of hundreds of "exercise it yourself" techniques, certain.
Tom:
Excel 2003 tin can relieve excel file in XML format. Would information technology be easier to upload the XML file into a Hulk column so use the 9i XML parser (instead of HTML)? That should save me writing my own html parser.
two. If yes, do I still need to upload file to database for the oracle parser to run on it?
Baronial 15, 2006 - 8:32 pm UTC
csv would be easiest if yous just accept rows and columns (sqlldr)
if you are going to involve the end user (file save as then upload), apply whatever file format works best for you.
If you lot want to employ our xml parsing stuff, yeah, you would have to get information technology to us.
Tom:
for a user uploading the file, is this an accurate argument.
if I relieve excel file in HTML it will involve a lot of work writing a parser in pl/sql.
IF it is in XML, oracle has already written the parser and I will save myself a lot of work parsing the xml file and loading it into table.
August 19, 2006 - 9:17 am UTC
yes. assuming the xml generated by excel is something usable to yous. you might have to write an XSL "plan" to reformat into something useful to y'all.
Please Detect the Excel VBA to tranfser data from excel to Oracle using ADO and OLE DB
Individual Sub CommandButton1_Click()
'create table rab_cash_br_detail_tmp_tab
'(
'account_number varchar2(100),
'reference_no varchar2(100),
'transaction_date varchar2(100),
'description nvarchar2(500),
'cheque_number varchar2(100),
'debit varchar2(100),
'credit varchar2(100),
'residual VARCHAR2(100)
')
'
'create or supercede PROCEDURE Rab_Cash_BR_Detail_Tmp (
' account_number_ IN VARCHAR2,
' reference_no_ IN VARCHAR2,
' transaction_date_ IN VARCHAR2,
' description_ IN NVARCHAR2,
' cheque_number_ IN VARCHAR2,
' debit_ IN VARCHAR2,
' credit_ IN VARCHAR2,
' balance_ IN VARCHAR2
') IS
'BEGIN
'
' INSERT INTO rab_cash_br_detail_tmp_tab
' (account_number, reference_no, transaction_date, description,
' cheque_number,
' debit, credit, balance )
' VALUES (account_number_, reference_no_, transaction_date_,
' description_,
' cheque_number_,
' debit_, credit_, balance_ );
'Finish;
'
'
Range("A5:G5").Select
'Change the connect string as necessary in OraSession.DbOpenDatabase("ExampleDb", "scott/tiger", 0&)
Dim conn As New ADODB.Connection
Dim cmd Every bit New ADODB.Command
conn.ConnectionString = "Provider=OraOLEDB.Oracle;Information Source=oracle.WORLD;User ID=user;Password=pwd;NDatatype=TRUE;DistribTX=False"
conn.Open
cmd.ActiveConnection = conn
cmd.CommandText = "RAB_CASH_BR_DETAIL_TMP2"
cmd.CommandType = adCmdStoredProc
Ready Param1 = cmd.CreateParameter("Prm1", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param1
Set Param2 = cmd.CreateParameter("Prm2", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param2
Set Param3 = cmd.CreateParameter("Prm3", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param3
'Set Param4 = cmd.CreateParameter("Prm4", adVarWStr, adParamInput, 500)
Set Param4 = cmd.CreateParameter("Prm4", adVarWChar, adParamInput, 500)
cmd.Parameters.Append Param4
Set Param5 = cmd.CreateParameter("Prm5", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param5
Set Param6 = cmd.CreateParameter("Prm6", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param6
Set Param7 = cmd.CreateParameter("Prm7", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param7
Set Param8 = cmd.CreateParameter("Prm8", adVarChar, adParamInput, 100)
cmd.Parameters.Append Param8
Dim v_retcode Every bit Integer
Dim Msg, Style, Championship, Aid, Ctxt, Response, MyString
Msg = "Statement of Account From Excel Sail will be sent To Oracle " + Chr(xiii) + " Do you desire to continue ?"
' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton1 ' Define buttons.
Title = "Transfer Bank Argument To Oracle" ' Ascertain title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = k
' Ascertain topic
' context.
' Brandish message.
' Response = MsgBox(Msg, Style, Title, Help, Ctxt)
Response = MsgBox(Msg, Way, Title)
If Response = vbYes So ' User chose Yes.
start_num = 18
num_chars = 18
Acc_No = Mid(Worksheets(one).Cells(5, one).Value, start_num, num_chars)
MsgBox Acc_No
'MID(Worksheets(one).Cells(v, 1).Value,start_num,num_chars)
Row = 11
Col = 1
MsgBox Worksheets(1).Cells(Row, Col).Value
While Worksheets(1).Cells(Row, Col) <> ""
'Account Number
cmd.Parameters("Prm1").Value = Acc_No
'Reference_No
cmd.Parameters("Prm2").Value = Worksheets(1).Cells(Row, Col + 0).Value
'Transaction_Date_
cmd.Parameters("Prm3").Value = Worksheets(1).Cells(Row, Col + 1).Value
'Description_
cmd.Parameters("Prm4").Value = Worksheets(1).Cells(Row, Col + 2).Value
'Cheque_Number_
cmd.Parameters("Prm5").Value = Worksheets(1).Cells(Row, Col + iii).Value
'Debit_
cmd.Parameters("Prm6").Value = Worksheets(one).Cells(Row, Col + 4).Value
'Credit_
cmd.Parameters("Prm7").Value = Worksheets(ane).Cells(Row, Col + five).Value
'Balance_
cmd.Parameters("Prm8").Value = Worksheets(1).Cells(Row, Col + 6).Value
cmd.Execute
Row = Row + 1
Col = 1
Wend
Else ' User chose No.
MyString = "No" ' Perform some activeness.
Terminate If
conn.Close
MsgBox "Washed"
ActiveWorkbook.Save
Workbooks.Close
End Sub
Also please notice the excel sheet for review
Account Report
Report Criteria
Account Number : 123-45678-90-01-23 (SAR)
Engagement Range : iii/seven/2006 to three/8/2006
Corporeality Range : Not specified
Check Range : Non specified
Reference Engagement Description Cheque Number Debit Credit Balance
001234 01/04/2006 Sweep To 12345678 -13.83 1,648,887.03
138855 01/04/2006 Greenbacks Deposit بدر الزامل 179,100.00 one,827,987.03
I wanted to know the link to the software for loading data from excel sheet to Oracle Application Forms. I used it in one case and it worked for me simply this time i am unable to find the link to download it. To use this software we simply creat Excel sheet and then give reference of excel columns in this software columns and connect the software to data base of operations. It is different from the data loader i institute on
</code> http://world wide web.download.com/Data-Loader/3000-10258_4-10453609.html <code>I volition exist keen ful to you if you could help me in this regard.
September 30, 2006 - 7:44 am UTC
I don't really know what tool you lot are looking for, pitiful.
Hi Tom,
This is regarding "importing information from oracle to excel".
I have worked on "Import external data" characteristic in MS Excel to extract the data from Oracle to Excel(using SQL). Merely now my requirement is one of the column value in Excel is to be referred in the "WHERE" clause of SQL used in "Import external data".
For e.g.
Input Excel file looks like below:
Emp_Id Emp_Name
1 Tom
ii Sri
3 John
4 Peter
5 Mahe
The required output excel file is like below:
Emp_Id Emp_Name Dept_Id
ane Tom lx
2 Sri 10
3 John 20
4 Peter l
5 Mahe 30
Hither, to go the Dept_Id, I connect to the Department table in the Oracle database through ODBC. The WHERE clause used in the SQL should refer the Emp_Id equally reference. Promise I'm clear in explaining my requirement. Please guide me to get a solution for this. Thanks.
All-time Regards
Sridhar.S
November 22, 2006 - 3:27 pm UTC
would that not exist "consign information from oracle TO excel"
this should be asked of people that use excel perhaps? Which would not include me in the effect set :)
Can nosotros load an excel file using an external table? Can you signal somewhere that shows how? I'm wondering if Oracle volition recognize the different cells equally columns and then I don't have to re-create into a text file and delimit information technology myself. I also take to ftp information technology from my client windows desktop to a unix db server to exercise this, no idea if that's even possible. Thanks.
February 01, 2007 - one:17 pm UTC
Tom as per your earlier communication I create the following codes SQL> exec ctx_ddl.create_policy( 'my_policy', 'ctxsys.inso_filter' ); SQL> create or replace directory MY_DIR as 'c:\temp' SQL> declare l_bfile bfile; l_clob clob; begin l_bfile := bfilename( 'MY_DIR', 'Book1.xls' ); dbms_lob.fileopen( l_bfile ); ctx_doc.policy_filter( 'my_policy', l_bfile, l_clob, true ); dbms_output.put_line( l_clob ); end; / OUTPUT is A B C D 1 Senthil 2 Debasish 12333 2 India 4 Deepak 1100 Is information technology Possible to shop that 4 columns into a table from that CLOB datatype Thankx in Accelerate
Hi Tom,
Sometime back, you seem to have suggested that xls tin be converted to .csv and subsequently loaded by SQLLDR. However, information technology would fail if the individual xls columns themselves contain , (comma) in the values.
To counter this, I was thinking of converting xls into tab delimited file, which could exist consumed past SQLLDR.
Any suggestions shall be highly appreciated.
Thanks,
Dheeraj
Baronial 22, 2008 - ii:48 pm UTC
you can, in excel, file "save equally" and salvage every bit csv.
if there were comma's, it would enclose them in quotes.
try it out.
Tom:
accept yous written earlier any articles on those users that similar to take information out of DB, put information technology in a spreadsheet and then sort the information, update information technology, and so put it back into DB. Their principal event is "simplicity". Awarding cant exercise what Excel can.
How do you respond to those users. Practise not yous lose all kind of data integrity with that. Would you let your customer exercise information technology if he asked you to build a programme to practise it.
March 07, 2009 - 12:57 pm UTC
... Awarding deceit do what
Excel tin.
...
then, umm, alibi me, but and then application was incorrectly coded.
what you lot just described takes about 30 seconds to develop using APEX. Point at a time, and you lot can "spreadsheet" it immediately - sort by whatever you want, blazon over values, delete rows, any.
... Do not you lose all kind of data integrity
with that. ....
If information integrity is put where information technology belongs (in the database), information technology is impossible to lose it - you lot cannot by pass it. Only if integrity was (not intelligently) wrapped upward entirely in the application would this be truthful.
Well let us say nosotros did non have apex for the time being (running oracle server 9.2). we have a web folio using pl/sql
besides i do not think a WEB HTML page tin exactly mimic a spreadsheet kind of look (i.due east you can't motion columns effectually, yous cant resize the columns, yous tin can't hibernate columns).
there is more flexibility a spreadsheet tin give in viewing the data
it is not easy to lawmaking functionality like spreadsheet (i.e sort any cavalcade asc, desc, play with the
numbers and see totals, etc.)
What is your definition of Data Integrity? is it Chief Keys, Foreign Keys, column types, cavalcade sizes, check constraints
March 07, 2009 - 1:39 pm UTC
ix.ii has apex.
you can build *anything* you want - actually, it is really true, you lot can build annihilation you want.
apex does that....
no no no, let us turn this effectually, what is YOUR definition of data integrity SMK.
Tom:
Apex is non supported on 9.2.0.2 it has to be 9.2.0.4,
not only that, they have apex installed on another server. so i have to develop on i server and connect to the other DB on another machine to get data.
i do not want to employ DB links for every SQL statement.
I have to wait till 11g upgrade for that.
I installed on my home pC just to see validate your point. you brand information technology audio like you develop a whole application in a few minutes.
Data integrity - to me is ensuring that information is accurate before it gets stored in DB. So this mean validating numbers are numbers and strings are strings, dates are dates, strange keys have valid values, tables have primrary keys. only that may be but office of it.
Hi Tom,
But want to thank you for the wonderful work you are doing. Your suggestons accept always proved useful to me. Do keep upwards the expert work.
Thank you..
New style to load spreadsheet data into Oracle
Ivo, September 09, 2011 - ii:51 am UTC
Source: https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:243814703172,
0 Response to "Plsql to Convert Xlsx to Html and Upload From Html"
Post a Comment