0

We use a closed online record system which allows the upload of CSV files into the database as Image BLOBs. These are used by the system to do various jobs.

I am trying to find a way to upload a CSV then access the corresponding BLOB from the datatable for my own reports using OpenRowset or similar, and export the CSV data into a temporary table.

I have no idea where to start with this. I have tried playing with OpenRowset but failed.

Any tips would be gratefully received.

iainc
  • 862
  • 6
  • 20
  • By "image BLOBs", do you mean columns of the deprecated `IMAGE` type? You can't do much with that. If the CSV is encoded in Windows-1252 or Unicode, try casting it to `VARCHAR(MAX)` or `NVARCHAR(MAX)` (respectively). Even that will only give you the CSV file in textual form. SQL Server can't import CSV until 2017, and even that can't read it from anything that's not a file. You can save the blob as a file using OLE automation, but I strongly recommend you solve this problem with client code instead ([`bcp` would probably do](https://stackoverflow.com/q/2404709/4137916)). – Jeroen Mostert Nov 02 '18 at 16:22
  • Forget T-SQL to do this job. Better approach is an external program that extracts the blob from the database to an external file, parse csv file and insert into table. – Jesús López Nov 02 '18 at 16:25
  • Unfortunately an external programme is out of the question. I have to work within the framework of the application. – iainc Nov 02 '18 at 16:48
  • Yes, columns of IMAGE type.Casting to VARCHAR(MAX) might be an option. Once I have the text I can split it. – iainc Nov 02 '18 at 16:49

1 Answers1

0

Following the comments, it seems the easiest way is to cast first to varbinary, then to wrap that in a cast to varchar(max).

This allows me to grab the text from the BLOB.

Thanks folks!

Select cast(cast(my_column as varbinary(max)) as varchar(max)) from mytable
iainc
  • 862
  • 6
  • 20