XLSX Reader and reading a 200meg Spreadsheet

[Mike H.]

I'm having a little bit of an issue reading a 200 Meg + Excel File for BulkCopying into SQL Server.   I am getting a buffer overflow when I try and referencing the file in the using statement.  So I thought about streaming it in.  Can I use a MemoryStream Object and pass that into XLSXDataReade ? I'm getting the error about can't read beyond the end of the stream, Which I can fix by using a for loop to  read the file.  I just want to make sure I'm going down the right path

private static void ExCompImporter()

{

DirectoryInfo dir = new DirectoryInfo(Settings.Default.FilePath.ToString());

FileInfo[] files = dir.GetFiles("*.XLSX");

foreach (FileInfo file in files)

{

byte[] bytearray = File.ReadAllBytes(file.FullName);

MemoryStream stream = new MemoryStream();

stream.Write(bytearray, 0, (int)bytearray.Length);

using (DataStreams.Xlsx.XlsxDataReader XLSReader = new DataStreams.Xlsx.XlsxDataReader(stream))

using (SqlConnection conn = new SqlConnection(Settings.Default.sqlconn))

using(SqlBulkCopy copy = new SqlBulkCopy(conn))

{

conn.Open();

XLSReader.Settings.HasHeaders = true;

copy.DestinationTableName = "stg.Orig";

copy.WriteToServer(XLSReader);

                   

　

}

}

[shriop]

Have you tried the current version from the site, or is this with a previous version? The latest version specifically changes the internal Zip implementation. Another thing to mention is that this is a Zip format, so the 200 MB file is close to 2 GB in memory. I've attempted to stream this data access internally, which might be the cause of the end of stream error, but it will still be a sizeable amount of data into memory. If this is using the latest version, I would have to look into the end of the stream error, if you can give me some of your code as an example of usage.

 

Bruce Dunwiddie