How to read CSV file and insert that each CSV row

Question

I am using React js, Mobx as storage management and API control with .NET core

User has to select *.csv file and process that file, each row to be inserted in the SQL Server. I am struggling to get CSV data from UI to API

I tried in React Js

 const loadData = (event: any) => {
        if (window.File && window.FileReader && window.FileList && window.Blob) {
            var file = event.target.files[0];
            var reader = new FileReader();
           // reader.readAsDataURL(file);
            reader.onload = function (e) {
                console.log(e.target?.result);
                var csvData =  e.target?.result //This holding huge data         
                processCSVfile(csvData);
            };
            reader.readAsText(file);
            
            event.target.value = null;
        }
    }

after processing in the store, I tried to pass that whole data to API, here I am facing issue as "Request URL is too long"

//Making api Get
//strData is holding whole CSV data
function getSampleCSV(strData: string) {
    console.log("csvTest", strData);      
    return requests.get(`/ControllerName/SampleMethod/csvSample/${strData}`);
}

Since you're putting the whole .csv file into strData, just how big is it? See: [What is the maximum length of a URL in different browsers?](https://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url-in-different-browsers) — AlwaysLearning, Jul 01 '21 at 08:27
There is maximum length in URL address for each different web browsers. [What is the maximum length of a URL in different browsers? ](https://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-a-url-in-different-browsers). Using URL to pass info is not suitable for this case. — Thiam Hooi, Jul 01 '21 at 08:28
That URL length is not predictable in my case, is there any other way to pass data from UI to API ? — MyNameIsDND, Jul 01 '21 at 09:09

score 0 · Answer 1 · answered Jul 01 '21 at 10:41

There is no need to read the File into memory just so you can upload the data to the server, and you don't have to convert it to json before uploading it

Change the server endpoint to accept a POST request that recives raw Data

const loadData = event => {
  fetch('/ControllerName/SampleMethod/csvSample', {
    method: 'POST',
    body: event.target.files[0]
  })
}

And you don't have to check if File, FileReader, FileList, Blob all exist, all modern browser have them now FileReader is now a legacy thing when we have new read methods such as str = await blob.text()

score -1 · Answer 2 · answered Jul 01 '21 at 09:08

Convert the CSV file to JSON format (in an array) and send it to the API. Inside the API code create a class for Object[] and iterate. I recommend you to use "antd" and "react-excel-renderer"

import { Upload } from "antd";
import { ExcelRenderer } from "react-excel-renderer";

fileHandler = (fileList) => {
    //console.log("fileList", fileList);
    //let fileObj = fileList.target.files[0];
    let fileObj = fileList;
    if (!fileObj) {
      this.setState({
        errorMessage: "No file uploaded!",
      });
      return false;
    }
    //console.log("fileObj.type:", fileObj.type);
    if (
      !(
        fileObj.type === "application/vnd.ms-excel" ||
        fileObj.type ===
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
      )
    ) {
      this.setState({
        errorMessage: "Unknown file format. Only Excel files are uploaded!",
      });
      return false;
    }

    //just pass the fileObj as parameter
    ExcelRenderer(fileObj, (err, resp) => {
      if (err) {
        console.log(err);
        return false;
      } else {
        this.setState({
          dataLoaded: true,
          cols: resp.cols,
          rows: resp.rows,
        });
        //Modify your rows from excel/csv   
        this.exceldenGelenDegerleriYansit(resp.rows);
        return false;
      }
    });
    return false;
  };

.net core API side should be something like this

 public bool APIFunction(Object[] objects)
        {
            bool sonuc = false;

            using (var db = new WebApiContext())
            {
                using (var command = db.Database.GetDbConnection().CreateCommand())
                {
                    db.Database.OpenConnection();

                    foreach (Object item in objects)
                    {
                        ...
                        int result = (int)command.ExecuteScalar();
                        if (result > 0)
                        {
                            sonuc = true;
                        }
                        ...
                    }
                }
            }
            return sonuc;
        }

How to read CSV file and insert that each CSV row

2 Answers2