Datatype to save excel file in sql server?

Question

Hi,

I have a table in which there are two columns : 1. import type, 2. Excel import template.

The second column - "Excel import template" should store the whole excel file.

How would I save excel file in databse...can I use binary datatype column, convert excel file to bytes and save the same ?

Thanks in advance !

Answer 1

Excel does support saving to XML. You could use this and store the sheet in an XML datacolumn, or a varchar(max)

Answer 2

Yes, you can use a binary file type. IMAGE VARBINARY(MAX) is likely to fit the purpose best.

Regarding how to "convert the Excel file to bytes" (it really is bytes from the beginning), we will need to know more about your programming environment in order to help. If you are using .NET, you should be able to do something like this:

var insert = new SqlCommand("INSERT INTO tbl (xls) VALUES (@xls)", conn);
insert.Parameters.AddWithValue("xls", File.ReadAllBytes("template.xls"));
insert.ExecuteNonQuery();

Answer 3

varbinary(max)

An Excel file is binary so you can't use characters like varchar(max)

And you can't use IMAGE because it's deprecated as MSDN says