First of all, let me recap the situation. I need an interface that allows users to upload excel data into a sql database.
1) I need a way to get excel data into my sql database.
2) I use the following sql statement to get the excel data into my sql database…
INSERT INTO SpreadsheetDataDump (f1,f2,f3,f4,f5,f6)
SELECT f1,f2,f3,f4,f5,f6
FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0','Data Source="D:\spreadsheets\test.xls";Extended Properties="Excel 8.0;IMEX=1;HDR=NO"')
...WorksheetName$
Keep in mind, in this scenario, the excel file must exist on the sql server.
3) Users need a way to upload the excel file onto the sql server.
4) I cannot grant users direct access to the sql server’s file system.
Task: How does the user get the excel file onto the sql server file system without granting access to the sql server’s file system?
In essence, there are three steps in this process. 1. Get excel file into sql table. 2. Put excel file (stored as Image) onto disc. 3. Insert excel data into sql database.
Step-1) I use a web (asp.net) interface to upload the excel file into a sql table as an Image data type. I’m going to defer you to
http://aspnet.4guysfromrolla.com/articles/120606-1.aspx, which explains the process of getting a binary file uploaded into a sql table. You’ll get a lot more stuff out of this article, but we’re concerned with the part about getting the file uploaded into the database. I thought it was a neat article, so I'm giving you the link instead of my code.
I use a similar table structure in my database. This is the table structure I use in my code:
CREATE TABLE BinaryDataFiles(
[PkID] [int] IDENTITY(1,1) NOT NULL,
[FileName] [nvarchar](255) ,
[FileSize] [real],
[FileData] [image],
[UploadDate] [datetime] DEFAULT (getdate()),
[ContentType] [nvarchar](30)
)
Step-2) Here’s the part that actually gets the excel file onto the sql server’s file system. I use a console application to dump the excel file (stored as an Image) onto disc. This console application is called from a sql stored procedure.
Here’s the code that streams the image data to file. It compiles to a file called StreamImageData.exe.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Configuration;
namespace StreamImageData {
class Program {
static int Main(string[] args) {
try {
string ConStr = "Data Source=theServer;Initial Catalog=theDatabase;Persist Security Info=True;User ID=theUser;Password=thePassword";
SqlConnection dbConn = new SqlConnection(ConStr);
string sql_str = "select FileSize, FileName, FileData, ContentType from BinaryDataFiles where (PkID = @PkID)";
SqlCommand cmdGetFile = new SqlCommand(sql_str, dbConn);
SqlParameter paramPkID = new SqlParameter("@PkID", SqlDbType.Int);
cmdGetFile.Parameters.Add(paramPkID);
paramPkID.Direction = ParameterDirection.Input;
paramPkID.Value = args[0].ToString(); // The primary key of the blob.
string fileName = args[1].ToString(); // The output file name.
dbConn.Open();
SqlDataReader dr;
dr = cmdGetFile.ExecuteReader();
if(dr.Read()) {
// Create random data to write to the file.
byte[] dataArray = (byte[])dr["FileData"];
using(FileStream fileStream = new FileStream(fileName, FileMode.Create)) {
// Write the data to the file, byte by byte.
for(int i = 0; i < dataArray.Length; i++) {
fileStream.WriteByte(dataArray[i]);
}
}
}
return 0;
} catch(Exception ex) {
Debug.Write(ex.ToString());
return -1;
}
}
}
}
This application expects two arguments – the primary key for the record which stores the image data and an output file name.
I use a stored procedure to execute StreamImageData.exe. The stored procedure goes something like this...
USE [YourDatabase]
GO
CREATE PROCEDURE [dbo].[BinaryDataFiles_StreamImage2File](
@FileName AS varchar(256)
,@ID AS varchar(64)
,@Debug AS bit = 0 --defaults to 0 (off)
)
AS
SET nocount ON
DECLARE @TheResult int
declare @command_string AS nvarchar(2000)
SET @command_string = '"d:\apps\bin\StreamImageData.exe" '
+ @ID + ' ' + @FileName
EXEC @TheResult = master.dbo.xp_cmdshell @command_string
print @TheResult
declare @ItExists AS int
exec master..xp_fileexist @FileName, @ItExists out
IF @ItExists = 0 -- It failed; the file doesn't exist.
begin
-- Dump some kind of fail message
end
Step-3) Now that the excel file is parsed onto the sql server’s file system, you can insert the excel data into your database table.
Once again, I use some code that looks like the following to get the excel data into the sql table.
INSERT INTO SpreadsheetDataDump (f1,f2,f3,f4,f5,f6)
SELECT f1,f2,f3,f4,f5,f6
FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0','Data Source="D:\spreadsheets\test.xls";Extended Properties="Excel 8.0;IMEX=1;HDR=NO"')
...WorksheetName$
And this is what the SpreadsheetDataDump table’s structure looks like…
CREATE TABLE BinaryDataFiles(
[f1] [nvarchar](255) ,
[f2] [nvarchar](255) ,
[f3] [nvarchar](255) ,
[f4] [nvarchar](255) ,
[f5] [nvarchar](255) ,
[f6] [nvarchar](255)
)
These are the fundamental components of getting this task done. I have all three of these steps launch automatically, so to the user, it seems like the spreadsheet data automatically populated into the database. In the end, I also delete the image data out of the table and delete the excel file from the file system. Once the sql data is put into the table, my application had no reason to keep the file or image data.
There's a lot more that I've done in my production code. I didn't post all of it because I don't know what applies to your situation, so instead of cluttering this up with unnecessary code, I choose to provide the basics. I hope this helps - if not, please let me know your specifics and I will answer them as best as I can.
Also note that in this case, the spreadsheet data is being thrown into varchar fields. You may need to tweak your data if you want it in a different data type other that character data.
