Converting .xls to .csv file using pl/sql or any other method ( oracle 9i)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

As you mentioned SQL Developer can do but I think not in 9i. That is my problem here. Oracle version.

I'm not a SQL Developer person so I'm not exactly sure what it can and can't do.

If automation isn't required, just open Excel and save it as a CSV.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Yes correct. Cannot install sql developer on existing oracle_home. So no luck here.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Created few things - sensitive data is renamed.

1. upload.ftp

open example.com
user_name
password
cd /home/my_dir/my_data/purple_top_data
mput F:\Purple_Top\purple_top.xlsx
bye

2. upload.bat
@echo off
@set logfile=upload.log
@ftp -i -v -s:"upload.ftp" > %logfile%
@del %logfile%

Created an purple_top.xlsx in the directory and clicked on the bat file. Worked fine and ftp'ed my file to the server side directory. Created task scheduler to run every day. But only problem here is I need that xlsx to be csv. Can any experts help?

>>Can any experts help?

We have posted what we know.

It looks like you need something that can be automated. If so, you'll need to find a tool that does this or write your own.

I know nothing in Oracle that can natively process an XLS file from a script.

ASKER

In the upload.ftp can we add another line to convert xlsx to csv? That was my question

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Gerwin Jansen

You would need a script or application to convert your Excel source to a csv file first.

On what platform are you receiving the Excel file, Windows? What version and what applications do you have available?

Once converted to cvs you can use the standard available tools like sql loader to load your csv into the database.

What is in the Excel file? Is it just one worksheet with one table or do you have multiple sheets that would make conversion more difficult.

ASKER

Thanks

Gerwin Jansen

So you're not going to convert your Excel file first to csv?

I don't do much vbscript, but I thought this sounded interesting so this is what I cobbled together
A real vbscripter might do it differently and better, I welcome suggestions

There are multiple CSV options supported, I wasn't sure which to go with, you can switch constants for whichever works best for you, or you can alter your external table rules to adjust as needed.

Const xlCSV = 6
Const xlCSVMac = 22
Const xlCSVMSDOS = 24
Const xlCSVWindows = 23

Set files = WScript.Arguments
xlsx_in = files(0)
csv_out = files(1)
Set xl = CreateObject("Excel.Application")
xl.Application.DisplayAlerts = False
Set book = xl.Application.Workbooks.Open(xlsx_in)
book.SaveAs csv_out,xlCSV
xl.Application.Quit

Open in new window

I saved the above into a file called dump_xlsx_to_csv.vbe

cscript dump_xlsx_to_csv.vbe c:\temp\test.xlsx c:\temp\test.csv

Open in new window

ASKER

so you have to use a notepad for the script and then save as file_name.vbe? Or do we need to have visual basic?

notepad is sufficient

ASKER

I will try now. Thanks,

ASKER

cscript dump_xlsx_to_csv.vbe c:\temp\test.xlsx c:\temp\test.csv

If I have to do mapped network drive instead of c: any additional steps?

ASKER

Ran this on the command prompt- Error ( see attached)

All files exist in c:\temp. Both vbe and xlsx

cscript dump_xlsx_to_csv.vbe c:\temp\purple_top.xlsx c:\temp\purple_top.csv
command-prompt.JPG

When you saved the file in Notepad, did it actually save as dump_xlsx_to_csv.vbe.txt

Do a dir in that folder and check...

if you did, then rename it

ren dump_xlsx_to_csv.vbe.txt dump_xlsx_to_csv.vbe

if it still doesn't work, then I suggest opening a new question in the VB Script topic area.
I am not the right person to try to debug windows scripting issues

if you do, then please post a link here to your new question.
I'd be interested in seeing what "real" vb scripters do with my script.
I'm sure it can be improved with best practices that I don't know

ASKER

see attached
command-prompt.JPG

>>see attached

Not seeing what I believe you want to show us...