Upload CSV data into SQL Database using MVC and EF

Question

I am new MVC framework and trying to figure out on how to Parse the CSV file in such a way that only data from certain columns are saved to the database.

I am able to select the CSV file and upload it via the View and pass it to my controller using the following code as mentioned here Codelocker

public ActionResult UploadMultipleFiles(FileUploadViewModel fileModel)
{
    //open file
    if (Request.Files.Count == 1)
    {
        //get file
        var postedFile = Request.Files[0];
        if (postedFile.ContentLength > 0)
        {
            //read data from input stream
            using (var csvReader = new System.IO.StreamReader(postedFile.InputStream))
            {
                string inputLine = "";

                //read each line
                while ((inputLine = csvReader.ReadLine()) != null)
                {
                    //get lines values
                    string[] values = inputLine.Split(new char[] { ',' });

                    for (int x = 0; x < values.Length; x++)
                    {
                        //do something with each line and split value
                    }
                }

                csvReader.Close();
            }
        }
    }
    return View("Index");
}

However, I am not really sure as how to only select the required columns in CSV file and store it to the database?

Any suggestions guys?

You should already know the schema of your CSV file. By taking values[x] you can build your query or assign to your database variable based on particular columns x. — ydoow
– ydoow, Commented Jun 7, 2016 at 23:55

Nkosi · Accepted Answer · 2016-06-08 01:39:35Z

1

Solved the problem by creating a DataTable method where by creating required columns and then using StreamReader and looping through the lines and selecting the required columns

[HttpPost]
public ActionResult UploadMultipleFiles()
{
    FileUploadService service = new FileUploadService();

    var postedFile = Request.Files[0];

    StreamReader sr = new StreamReader(postedFile.InputStream);
    StringBuilder sb = new StringBuilder();
    DataTable dt = CreateTable();
    DataRow dr;
    string s;
    int j = 0;

    while (!sr.EndOfStream)
    {
        while ((s = sr.ReadLine()) != null)
        {
            //Ignore first row as it consists of headers
            if (j > 0)
            {
                string[] str = s.Split(',');

                dr = dt.NewRow();
                dr["Postcode"] = str[0].ToString();
                dr["Latitude"] = str[2].ToString();
                dr["Longitude"] = str[3].ToString();
                dr["County"] = str[7].ToString();
                dr["District"] = str[8].ToString();
                dr["Ward"] = str[9].ToString();
                dr["CountryRegion"] = str[12].ToString();
                dt.Rows.Add(dr);
            }
            j++;
        }
    }
    service.SaveFilesDetails(dt);
    sr.Close();
    return View("Index");
}

edited Jun 8, 2016 at 1:39

Nkosi

249k38 gold badges472 silver badges506 bronze badges

answered Jun 8, 2016 at 1:33

KJSR

1,7577 gold badges29 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luis Teijon Over a year ago

Since you are accessing a file, you should think of converting synchronous requests to asynchronous requests by converting synchronous action methods to asynchronous action methods.

Tjaart van der Walt Over a year ago

Not relevant to the answer but you can also look at this file helper library to handle the logic of processing the file. filehelpers.net

Collectives™ on Stack Overflow

Upload CSV data into SQL Database using MVC and EF

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related