1 Reply Latest reply on Jun 1, 2013 4:42 PM by lake

    excel file uploading

      actually i am using oracle forms 10g and and i have to upload an excel file in a table from a browse button..
      just want to select file from hard drive and upload data in table...
      so what i wll have to do..
      thanks in advace..
        • 1. Re: excel file uploading
          If this is just a one time thing I'd use sqlldr (part of the total client). You would make sure to add a trailing column to
          the spreadsheet (past the last data column) and save as a tab delimited text file say mydata.txt
          Then you have to create a control file that would be something like this:
          (note that terminated by is a tab however you have to do that in your text editor)
          load data APPEND into table
           fields terminated by '     ' trailing nullcols
          (SOME_DATE "to_date(trim(:SOME_DATE),'MM/DD/YYYY')",
          SOME_CHARS "trim(:SOME_CHARS)",
          SOME_NUMBERS "trim(:SOME_NUMBERS)"
          Then it would be 
          sqlldr control=mydata.ctl data=mydata.txt 
          (it asks for id/password@instance )
          that would load 3 columns some_date,some_chars and some_numbers in that order. The data items must appear
          in the order specified in the control file. You can specify the same data item more than once though if necessary.

          PS: one thing that users manage to do that can really goof things is up is embed carriage returns (newlines) into the
          spreadsheet. For that reason it is best to remove them in excel first. You do a search and replace, searching for
          alt-010 (alt zero one zero where the numbers are on the numeric keypad). If this worked ok, excel represents the
          search item as a dot, then you would replace with a space. Likewise if they embedded tabs that would be a good thing to remove before saving as a tab delimited text file.

          Note you have to look at the log file and it may have not loaded things besides the headers which would go into the .bad file. Sqlldr is quite enigmatic and using it is an art to say the least. Good to know however as it's very important to get stuff into the database :-)

          This would be a great feature for forms btw if it had a column name recognition load feature. I mean if it would match a column header line with a database table column NAME. Suppose that we have a way in forms to
          tell it we want to load some data from excel into MYTABLE and then forms looks at ideally the excel file specified and
          highlights what columns matched column names in MYTABLE. [step here for user to change things in excel, and or the
          table.] [We need forms to optionally get rid of bad things that could be in cells such as returns, delimiters, and possibly escape some bad characters like colons, ampersands...] Somewhere in there it would have to be worked out how to convert dates, Case and various details. Then hopefully when the user pressed a button it would load the data and make it very clear what was not loaded as opposed to the "I don't like something" sort of message you can get. If it doesn't like something we want to know what exactly it did not like and why it did not like it. This would be a fabulous feature, smarter than sqlldr because it would be driven by column names and not solely column order.