locked
Coding a CSV upload function in Webmatrix RRS feed

  • 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);
                    
                    
                }

    Thursday, June 25, 2015 12:30 PM

Answers

  • 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);



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 26, 2015 3:02 PM

All replies

  • 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.

    Friday, June 26, 2015 7:10 AM
  • 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?

    Friday, June 26, 2015 12:55 PM
  • 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);



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 26, 2015 3:02 PM
  • 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.

    Friday, June 26, 2015 4:38 PM