Importing files in SAS and storing them in Oracle as blobs

[Proventus]

Hi, Complete noob here.

I receive a lot of excel files, not very big, maybe 40 rows and a dozen columns, and I want to store them as blobs in Oracle. Does SAS have the capability to store, then recall the excel files?

This would certainly help me to manage all these files (thousands).

Thanks.

[Kurt_Bremser]

It can be done. Use proc import and point the out= option to the Oracle library.

But be aware that importing directly from Excel files involves guessing on the part of SAS, with inconsistent results.

[Proventus]

That's great!.

I know about that one. I always QA the data I import because the layout has been known to spontaneously change 😉

Thanks Kurt!

[DBailey]

you can definitely store the row/colum data from the excel sheet in oracle as described....but is that what you want to do?  It sounded like you want to store the spreadsheet itself as a binary large object in oracle.  I think it can be done as well, but not using proc import.

[Proventus]

I'll be storing and reporting on the data, but I also need a spot for the physical files. Storing the physical files on a network disk is not a viable solution in this case. I don't want to start building utilities outside of SAS to do the work. That's what they have now, a mish-mash of tools to do one job.

[LinusH]

SAS doesn't unfortunately have a BLOB object. Therefore the SAS/ACCESS to Oracle is quite limited in thus sense as well. You could interact with BLOB using a SAS CHAR. But then you need a way to import an Excel into one variable, and frankly I don't know how to do this.

So, it seem more appropriate to use Oracle tools for this. Still, you could control Oracle action using explicit SQL pass through.

Then I wonder you need to store Excel file in Oracle? Perhaps you should us a store more appropriate for the specific data, such as a content or a document server.

[Kurt_Bremser]

The largest "object" that SAS can put into datasets (and handle in procedure and data steps) are strings of length 32767.

IMO, if you want to setup a store for Excel files from within SAS, you will have to create a file repository/directory, copy the files there (preferably using XCMD), and save the location in a character variable in a dataset.

[AngusLooney]

Sounds like a pretty exotic/extreme use case to store small Excel files IN Oracle as files/blobs.

Why can't you store them in a managed file repository and just track them, i.e. the paths, locations, md5 hash values, file sizes data times etc in a table in Oracle.

What apps/system are subsequently going to access/retrieve the files? (And how?)