Sitecore 9.3 Forms Upload export

Created: 6 Dec 2019, last update: 30 Jan 2020

Sitecore 9.3 Forms Upload export

One of the new things in Sitecore 9.3 is the Out of the box File upload Form element. Also, deleting form data is now possible with a date range within the Form apps, this includes the uploaded files.

The uploaded files are stored in the ExperienceForms database in a new table sitecore_forms_filestorage. The data is unencrypted. When you do an Export data you get the form data and an url where you can download the uploaded files.

Use this SQL script to get all uploaded files, files are write by SQL server so this work for local development, with a local SQL server.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO
 
--folder must exist!
DECLARE @outPutPath varchar(50) = 'C:\tmp\forms'
, @i bigint
, @init int
, @data varbinary(max) 
, @fPath varchar(max)  
, @folderPath  varchar(max) 
  
--Get Data into temp Table variable so that we can iterate over it 
DECLARE @Doctable TABLE (id int identity(1,1), [MediaGuid]  uniqueidentifier , [FileName]  varchar(100), [Doc_Content] varBinary(max) )
  
INSERT INTO @Doctable([MediaGuid] , [FileName],[Doc_Content])
Select [Id] , [FileName],[FileContent] FROM  [sitecore_forms_filestorage].[FileStorage]
--SELECT * FROM @table


SELECT @i = COUNT(1) FROM @Doctable
  
WHILE @i >= 1
BEGIN
 
    SELECT
     @data = [Doc_Content],
     @fPath = @outPutPath + '\' + convert(varchar(38),[MediaGuid])+[FileName],
     @folderPath = @outPutPath + '\' 
    FROM @Doctable WHERE id = @i
  

  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 1;  
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources
  
  print 'Document Generated at - '+  @fPath   
 
--Reset the variables for next use
SELECT @data = NULL 
, @init = NULL
, @fPath = NULL 
, @folderPath = NULL
SET @i -= 1
END

Note, adjust the query if you want it for a specific form or a specific upload, now you get the upload of all forms