How to retrieve and convert SQL database BLOB data, convert byte datatype to files and save on local disk using Studio activities ?
Whenever files (like images, excel files, documents) are stored in a SQL database, they are stored as binary data type (an array of bytes- System.Byte).
When are queried from Studio/Robot, the data is returned as System.Byte type and needs to be converted in its original file extension.
To achieve this, depending on file type/extension, the following steps can be used:
- Connect to the database and execute a query that selects the BLOB table, add a For Each Row activity to parse the datatable resulted on first two steps
- Create a variable of MemoryStream type
- Assign the following value to the MemoryStream variable (row("BLOBData")
- Another variable can be created to have the dynamic path, where the file will be saved concatenated with original filename and extension ( in case the columns for filename and extension are present)
- Add an "Invoke Code" activity and choose VBNet as Language in the Properties pane, inside the following expression will convert the MemoryStream to the file and save it to the chosen path : File.WriteAllBytes(path, memoryStreamdoc.ToArray())
For images stored in the database, no invoke code is required as there is an activity Save Image. The same MemoryStream will be used that will be assigned to a variable of type Image
then saved to desired location.