0

we are currently getting the content of an Excel Sheet into a bidimensional array this way:

Excel.Range rango = excelSheet.UsedRange;
object[,] excelData = (object[,])rango.Value2;

And later the info is used to store in a Database but, the issue is that within the array some N/A values come from Excel and cause troubles when saving to the Database (we try later with a bulk insert), is there any way to replace the N/A values for empty strings when getting the range? I have tried looping through the array but this is not viable since we can have up to 3 million records and it takes too long.

Thanks in advance.

JCO9
  • 960
  • 1
  • 15
  • 24
  • 3
    You're using Excel Interop in an ASP.NET environment? This is a [bad idea](https://support.microsoft.com/en-us/kb/257757). – mason Jul 27 '16 at 13:05
  • I don't understand how this could ever be achieved without looping through the recordset.. – Alec. Jul 27 '16 at 13:25
  • Yes I am, it works since the server has Office installed (and it will have forever) but thanks for the comment, I will start searching for the right way to do it and align to that (+1 to your comment) – JCO9 Jul 27 '16 at 13:52
  • ClosedXml, EPPlus are great for handling excels. Also you might want to check this for future development or for refactoring phase ;-) http://stackoverflow.com/questions/444522/import-and-export-excel-what-is-the-best-library – Shuffler Jul 27 '16 at 14:35

1 Answers1

2

Thanks for the comments, I did the trick this way:

Excel.Range rango = excelSheet.UsedRange;
rango.Replace("#N/A","");
object[,] excelData = (object[,])rango.Value2;

Hope this helps someone in the future.

JCO9
  • 960
  • 1
  • 15
  • 24