2

We get daily data in Excel files and I want to load that Excel sheet data into the database on a daily basis.

I want that Excel sheet data to be loaded automatically into the database.

How can I set auto-load for this? I am using SQL Developer.

halfer
  • 19,824
  • 17
  • 99
  • 186
Gayathri
  • 339
  • 2
  • 15
  • 26
  • If you are using an **Excel** file, then you are limited to **Excel import**. The **External table** and **SQL Loader** methods are only available when doing a **delimited text** or **CSV file import**. However, you could easily save it as CSV file and then use the options I said. – Lalit Kumar B Sep 07 '15 at 05:18
  • The whole concept is called "ETL" in the Reporting process (Extraction and Transformation Layer), and there are many tools out there (also Open Source) which speak Excel and Oracle. – flaschenpost Sep 07 '15 at 05:27
  • Thanks Lalit. We will convert the file into CSV but is there any option to automate this process without an ETL tool? – Gayathri Sep 07 '15 at 05:29
  • Have you gone through some of the google answers for "oracle read excel"? I get [this](http://www.dba-oracle.com/t_read_excel_file_from_pl_sql.htm) for example, but I don't know if it helps. – flaschenpost Sep 07 '15 at 05:34
  • You can try batch programming or powershell. http://stackoverflow.com/questions/21554062/executing-set-of-sql-queries-using-batch-file and http://stackoverflow.com/questions/1021039/read-a-value-from-a-xls-file-using-bat-files – Adrian Stanculescu Sep 07 '15 at 05:47
  • Not very elegant, however once it is in CSV format you can use UTL_FILE to open the document, then read this line by line and parse the string. This can then be automated via the database scheduler. – Shaun Peterson Sep 07 '15 at 23:17

1 Answers1

1

One way to solve this is to write an Excel macro in VBA. Saving to CSV first is risky in my experience as data like telephone numbers (represented as text) lose the leading zeros. Furthermore, UK/US date formats can get misinterpreted.

The macro can load source files from a directory, validate the spreadsheet in its native format and then push insert statements to the database or call a stored procedure.

The only downside is someone needs to open and run the macro, however this can be automated if desired: How can you run an Excel macro through a schedule task.

Community
  • 1
  • 1
AdamRossWalker
  • 294
  • 1
  • 10