Coding a CSV upload function in Webmatrix

Question

User-457298706 posted

I'm attempting to code a cshtml page in Webmatrix that would allow a user to upload(or open to be more precise) a CSV file, write a small note in text box, and have the data contained within the CSV inserted into my data base(SQL Server 2008) in the correct columns along with the note. My execution of this is a bit shaky as the knowledge on how to go about this isn't fully there but it would be great if fellow members could look at my code and give me advice.

Main trouble so far is telling the "File.ReadAllLines" function where the file is located as I'm trying to use the "File Upload" helper for the opening of the file. As well as getting the logic down for having the note inserted for each row inserted into the db from the CSV.

    @{
        // variables

        var Notes = "";
        var creatorOfEntry = "";
        DateTime EntrytDate = DateTime.Now;
        


Validation.RequireField("Notes", "You must enter a note.");
        Validation.Add("Notes",
        Validator.StringLength(
        maxLength: 200, 
        errorMessage: "Notes cannot exceed 200 characters"));

        if (IsPost){
            creatorOfEntry = WebSecurity.CurrentUserName;
            var uploadedFile = Request.Files[0];
            var fileLoc = Path.GetFileName(uploadedFile.FileName); //Throwing error
            var db = Database.Open("My_DB");
            var data = File.ReadAllLines(fileLoc);
            foreach (var row in data)
            {
                var columns = row.Split(new[] { ';' });
                var sqlInsert = "INSERT INTO General_Information(TicketNo, FName, LName, creatorOfEntry, Date, Notes ) VALUES (@0, @1, @2, @3, @4)";
                db.Execute(sqlInsert, creatorOfEntry, EntryDate, Notes);
                
                
            }

Answer 1

User-821857111 posted

Difficult to know what your issue is without knowing the error details, but you need to save the file first. Once you have done that, you are better off using BULK INSERT to insert the contents into a database rather than looping through the contents line by line.

Answer 2

User-457298706 posted

Thanks for replying Mike. I came to realization yesterday that one of my issues was that I was not saving the file anywhere so it could be read. I fixed that up but I'm still lost on how to get the data from the CSV into my DB. I've been reading up on the BULK INSERT you've suggested but I'm having a small issue of not knowing how to call/execute the insert during post.

This is my current code at the moment

  @{
        // variables

        var Notes = "";
        var creatorOfEntry = "";
        DateTime EntrytDate = DateTime.Now;
        


Validation.RequireField("Notes", "You must enter a note.");
        Validation.Add("Notes",
        Validator.StringLength(
        maxLength: 200, 
        errorMessage: "Notes cannot exceed 200 characters"));
        
            //Save CSV to Server
        
        if ((IsPost) && (Request.Files[0].FileName!="")){
            var fileSavePath = "";
            var uploadedFile = Request.Files[0];
            fileName = Path.GetFileName(uploadedFile.FileName);
            fileSavePath = Server.MapPath("~/Uploads/" + fileName);
            uploadedFile.SaveAs(fileSavePath);
            
            //Insert code here that will kick off the Bulk Insert Stored procedure.
        }    

This is my BULK INSERT Stored Proc

PROCEDURE TESTBulkInsert 
AS

BEGIN
	BULK INSERT MyDB.dbo.Information
    FROM 'path to my file\Uploads\Test.csv'
    WITH
    (
      FIRSTROW = 2,
      FIELDTERMINATOR = ','

    )
END

I'm also wondering, is it possible to have a BULK INSERT that does not need the specific filename and instead would process what is contained within the "Upload" folder. I'm thinking perhaps using variables would be the way to start?

Answer 3

User-821857111 posted

If you are planning on using a stored procedure, you can pass a parameter in that represents the path to the file:

CREATE PROCEDURE TESTBulkInsert 
@FileName nvarchar(200)
AS

BEGIN

DECLARE @sql nvarchar(MAX);
SET @sql=
    'BULK INSERT MyDB.dbo.Information
    FROM '+char(39)+@FileName+char(39)+'
    WITH
    (
    FIELDTERMINATOR = '','',
    ROWTERMINATOR = ''\n'',
    FIRSTROW = 2  
    )'
EXEC(@sql)
END

Then you call it using the Database helper like this:

var command = "EXEC TestBulkInsert @FileName = @0";
db.Execute(command, path_to_your_file);

Answer 4

User-457298706 posted

Thank you very much Mike. Learned a great deal today. Spent most of the day reading up on BULK INSERT and was able to get my desired initial results thanks to you and the readings. Appreciate the sharing of your knowledge here and in your blog..and I'll stop gushing now.

If someone else is running across this problem, my solution was:

To create a VIEW of the desired table I wanted to import the CSV data.

Create the VIEW to match the columns I wanted the CSV data to be inserted to.

Use Mike's Stored Procedure Code and call code to my own.