0

I have an uploaded CSV file that is posted to a Web API route. I have been able to get the CSV into memory via a byte array and that is where I am stuck. I want to take each row of the CSV, add three additional fields that are posted from the upload form along with the file, and insert every CSV row with additional fields to a SQL server table. How do I take my byte array, transform the rows, and insert the data?

Here is my AngularJS that uses ng-file-upload.

$scope.upload = function (file) {
Upload.upload({
    url: 'api/UploadProfile',
    data: {
        file: file,
        'ProfileName': $scope.ProfileName,
        'SubmittedBy': $scope.SubmittedBy,
        'InsertDate': $scope.InsertDate
    }
})}

And here is my Web API controller

public class ProfileUploadController : ApiController
{
    private BIMarketOrderEntities db = new BIMarketOrderEntities();

    [HttpPost, Route("api/UploadProfile")]
    public async Task<IHttpActionResult> Upload()
    {
        if (!Request.Content.IsMimeMultipartContent())
            throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);

        var provider = new MultipartMemoryStreamProvider();
        await Request.Content.ReadAsMultipartAsync(provider);
        foreach (var file in provider.Contents)
        {
            var filename = file.Headers.ContentDisposition.FileName.Trim('\"');
            var buffer = await file.ReadAsByteArrayAsync();

            // How do I get 'buffer' to my database while adding the additional fields?
        }

        return Ok();
    }
}
B-Ray
  • 473
  • 1
  • 12
  • 29

1 Answers1

0

Here is what I ended up doing for this particular instance. If there more efficient methods, I am definitely open to them.

[HttpPost, Route("api/UploadProfile")]
public async Task<IHttpActionResult> Upload()
{
    //Get attachment and form data
    if (!Request.Content.IsMimeMultipartContent())
        throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);

    var provider = new MultipartMemoryStreamProvider();
    await Request.Content.ReadAsMultipartAsync(provider);

    //File and 3 form parameters will be saved to this array
    string[] results = new string[4];

    //Read all data into array
    int i = 0;
    foreach (var parameter in provider.Contents)
    {
        var bytes = await parameter.ReadAsByteArrayAsync();
        results[i++] = Encoding.Default.GetString(bytes);
    }

    //Split lines of CSV into array
    string[] stringArray = results[0].Split(new[] { "\r\n", "\r", "\n" }, StringSplitOptions.None);

    //Check if header row matches expected CSV layout
    if (stringArray[0] != "CSVField1,CSVField2,CSVField3,CSVField4,CSVField5,CSVField6")
    {
        //Failure
        var message = "File does not contain necessary header fields.";
        return Content(HttpStatusCode.BadRequest, message);
    }

    //Remove header row
    stringArray = stringArray.Skip(1).ToArray();

    //Create db object store all Insert data
    var profileObjs = db.Set<T_ProfileStaging>();
    foreach (var s in stringArray)
    {

        //Save each column in array
        string[] columns = s.Split(',');

        //Add form data to individial records
        T_ProfileStaging profileObj = new T_ProfileStaging();
        profileObj.Field1 = columns[0];
        profileObj.Field2 = results[1];
        profileObj.Field3 = columns[1];
        profileObj.Field4 = columns[2];
        profileObj.Field5 = columns[3];
        profileObj.Field6 = columns[4];
        profileObj.Field7 = results[2];
        profileObj.Field8 = columns[5];
        profileObj.Field9 = results[3];

        profileObjs.Add(profileObj);
    }

    //Save all objects to database
    db.SaveChanges();

    //Success
    return Ok();
}
B-Ray
  • 473
  • 1
  • 12
  • 29