1

I'm trying out a project with ASP.Net MVC and have a large CSV file that I want to save to the LocalDB.

I have been following this tutorial (and the ones before that are about MVC): https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/creating-a-connection-string

Now I want to add data to this database that I have set up and I would like to read this data from a csv file and then save it to my database.

I have tried this: https://www.aspsnippets.com/Articles/Upload-Read-and-Display-CSV-file-Text-File-data-in-ASPNet-MVC.aspx but when I try to upload my file I get an error that my file is too large?

I would love it if it could be automated so that when I start my application the database will be populated with the data from my csv file (and if it already is populated it will not do it again) or just some way of coding so that I can add the data from my csv file to the database (LocalDB).

    protected override void Seed(ProductsDBContext context)
    {
        Assembly assembly = Assembly.GetExecutingAssembly();
        string resourceName = "WebbApplication.App_Data.SeedData.price_detail.csv";
        using (Stream stream = assembly.GetManifestResourceStream(resourceName))
        {
            using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
            {
                CsvReader csvReader = new CsvReader(reader);
                var products = csvReader.GetRecords<PriceDetail>().ToArray();
                context.PriceDetails.AddOrUpdate(c => c.PriceValueId, products);
            }
        }
    }
  • So you want to save the contents of the CSV rather than the whole file? I'm assuming you have models that correlate to the data in your CSV already? – Usman Khan Feb 14 '19 at 11:22
  • From V N Karthikeyan: Please check if this link serves your purpose : https://www.telerik.com/blogs/upload-large-files-asp-net-radasyncupload – Panagiotis Kanavos Feb 14 '19 at 11:27
  • Why are you uploading the file? Can't you just read it into memory once the application starts? Then you could create a DbContext and use Seed method to read data from .csv to your DB. Like so: https://www.davepaquette.com/archive/2014/03/18/seeding-entity-framework-database-from-csv.aspx – LeonidasFett Feb 14 '19 at 11:34
  • @LeonidasFett Have you tried this? I've followed this tutorial but when I'm trying the update-database command I get the error: "Value cannot be null. Parameter name: stream". You wouldn't know what's wrong? –  Feb 14 '19 at 13:26
  • Please update your answer with the code that's throwing the exception. – LeonidasFett Feb 14 '19 at 13:29
  • @LeonidasFett I have updated with my Seed method, it's what throws the exception when I'm trying to update database. Could it be that I'm writing in the wrong filepath? –  Feb 14 '19 at 13:56
  • try putting `string[] names = Asm.GetManifestResourceNames(); foreach(var name in names) Debug.WriteLine(name);` before your Assembly definition and look at the output. I think you declared the namespace of your resource wrong. – LeonidasFett Feb 14 '19 at 14:10
  • @LeonidasFett Sorry, I'm really new to this, but where can I find this output? –  Feb 14 '19 at 14:20
  • Look at the output window (Ctrl+Alt+O) and make sure "Show output from" is set to Debugger. – LeonidasFett Feb 14 '19 at 14:29

2 Answers2

0

Your second link includes the following line:

string csvData = System.IO.File.ReadAllText(filePath);

If you are getting an Out of Memory Exception, then you should not load the entire file into memory at once - i.e. do not read all of the text.

The StreamReader has a built-in function to handle this.

System.IO.StreamReader file = new System.IO.StreamReader("WebbApplication.App_Data.SeedData.price_detail.csv"); 
while((line = file.ReadLine()) != null)  
{  
    System.Console.WriteLine(line);
    //Replace with your operation below
}

Potentially the same problem solved at this question.

bluemoon6790
  • 467
  • 2
  • 11
0

With Cinchoo ETL - an open source library, you can bulk load CSV file into sqlserver with few lines of code.

using (var p = new ChoCSVReader(** YOUR CSV FILE **)
     .WithFirstLineHeader()
    )
{
    p.Bcp("** ConnectionString **", "** tablename **");
}

For more information, please visit codeproject article.

Hope it helps.

Cinchoo
  • 6,088
  • 2
  • 19
  • 34