• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2052
  • Last Modified:

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

Wanted to know if there is a way for converting excel to .csv.

.xls is a Microsoft proprietary binary format for storing Excel workbooks.
.csv is a flat data file (character seperated values) for a set of data.

The two are not compatible.
We cannot just "read" the binary file format and convert it to .csv. I am aware of this but just wanted to post this question if any changes are there with newer version of oracle.

i have a directory say 'F:\purple_top\'. I will be getting .xls file into this directory. I need to change it to .csv and load into oracle external tables. Any thoughts or advance coding? Help or reply appreciated.
0
anumoses
Asked:
anumoses
  • 11
  • 6
  • 6
  • +1
5 Solutions
 
slightwv (䄆 Netminder) Commented:
I don't think there are any Oracle tools that will do this in an automated way.  From reading I think SQL Developer can import an XLS file and export to CSV but there is no command line way.

You can probably use Powershell and write your own program but I'm not a Powershell Expert.
0
 
anumosesAuthor Commented:
As you mentioned SQL Developer can do but I think not in 9i. That is my problem here. Oracle version.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
sql developer's import feature has nothing to do with the oracle version

you could try building a java stored procedure to read the xls file.
0
 
anumosesAuthor Commented:
Yes correct. Cannot install sql developer on existing oracle_home. So no  luck here.
0
 
sdstuberCommented:
then you'll probably need to go with the java method if you want it done in the database.

if it can be done external to the db, then there are lots of tools to read xls and write csv
0
 
anumosesAuthor Commented:
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?
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
anumosesAuthor Commented:
In the upload.ftp can we add another line to convert xlsx to csv? That was my question
0
 
sdstuberCommented:
>>>  xlsx to be csv

you just changed the request

do you have xls  or xlsx ?

the idea is similar, but the structures of each are different and hence require different libraries.
either way - try a java stored procedure.


or, if you want to do it with a script outside the db, then download a command line conversion tool.  there are many, you may even have one already.  choose whichever you like best
0
 
slightwv (䄆 Netminder) Commented:
>>In the upload.ftp can we add another line to convert xlsx to csv? That was my question

As we posted:  With some pre-written utility or by writing your own code.  Not natively using Oracle tools.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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.
0
 
anumosesAuthor Commented:
Thanks
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
So you're not going to convert your Excel file first to csv?
0
 
sdstuberCommented:
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

0
 
anumosesAuthor Commented:
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?
0
 
sdstuberCommented:
notepad is sufficient
0
 
anumosesAuthor Commented:
I will try now. Thanks,
0
 
anumosesAuthor Commented:
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?
0
 
anumosesAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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...
0
 
sdstuberCommented:
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
0
 
anumosesAuthor Commented:
see attached
command-prompt.JPG
0
 
slightwv (䄆 Netminder) Commented:
>>see attached

Not seeing what I believe you want to show us...
0
 
anumosesAuthor Commented:
I have to run the command at c:\temp
Now converted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 11
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now