Created: 6 Dec 2019, last update: 30 Jan 2022
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
