improve csv file reader by removing quotes from fields, supporting a header, and allowing other seperators

Question

I would like my file to not have quotations when displayed in html. Meaning there are quotes by every word. I would like to take that away. And I would also like to add a th tag so I can have a header. And I don't wanna just have comma separated, I also wanna have other separators such as |

Here is my code:

css:

table { border: 1px solid #ccc; } table th { background-color: #F7F7F7; color: #333; font-weight: bold; } table th, table td { padding: 5px; border-color: #ccc; }

Javascript:

$(function () {
            $("#upload").bind("click", function () {
                var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.csv|.txt)$/;
                if (regex.test($("#fileUpload").val().toLowerCase())) {
                    if (typeof (FileReader) != "undefined") {
                        var reader = new FileReader();
                        reader.onload = function (e) {
                            var table = $("<table />");
                            var rows = e.target.result.split("\n");
                            for (var i = 0; i < rows.length; i++) {
                                var row = $("<tr />");
                                var cells = rows[i].split(",");
                                for (var j = 0; j < cells.length; j++) {
                                    var cell = $("<td />");
                                    cell.html(cells[j]);
                                    row.append(cell);
                                }
                                table.append(row);
                            }
                            $("#dvCSV").html('');
                            $("#dvCSV").append(table);
                        }
                        reader.readAsText($("#fileUpload")[0].files[0]);
                    } else {
                        alert("This browser does not support HTML5.");
                    }
                } else {
                    alert("Please upload a valid CSV file.");
                }
            });
        });

HTML:

<input type="file" id="fileUpload" />
    <input type="button" id="upload" value="Upload" />
    <hr />
    <div id="dvCSV">
    </div>

I think you'll have to be more specific on what you're getting and what you're expecting, adding some examples etc. — adeneo, Jan 04 '15 at 18:25
So...is what you've got not working? Are you seeing errors in the console? — danwellman, Jan 04 '15 at 18:31
No. It's all working. I just wanna take away the quotes I see in every cell. They get automatically added. And I would like to add a th tag so I can have a header. Lastly, I wanna have other seperators other than comma — , Jan 04 '15 at 18:33
Splitting on commas will not satisfy RFC 4180. "Smith,John", "$64,000" See http://stackoverflow.com/questions/1293147/javascript-code-to-parse-csv-data — Paul, Jan 06 '15 at 06:58
If you don't know how to remove `"` from a string, see [String.replace()](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/replace) — Paul, Jan 06 '15 at 06:59

TheIronDeveloper · Answer 1 · 2015-01-06T05:55:56.773

So I took a look at your code and made a jsfiddle off of it.

Heres the full fiddle

$(function() {
  $("#upload").bind("click", function() {
    var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.csv|.txt)$/;
    if (regex.test($("#fileUpload").val().toLowerCase())) {
      if (typeof(FileReader) != "undefined") {
        var reader = new FileReader(),
          table = $("<table>"),
          separator = ',',
          rows,
          rowLength;

        reader.onload = function(e) {
          rows = e.target.result.split(/[\r\n|\n]+/);
          rowLength = rows.length;

          for (var i = 0; i < rowLength; i++) {
            var row = $("<tr>"),
              cells = rows[i].split(separator),
              cellLength = cells.length,
              cell;

            for (var j = 0; j < cellLength; j++) {
              // Special case for the first row
              cell = (i === 0) ? $("<th>") : $("<td>");
              cell.html(cells[j]);
              row.append(cell);
            }

            // Special case for first row (thead)
            if (i === 0) {
              row = $('<thead>').html(row);
            }
            table.append(row);
          }
          $("#dvCSV").html('');
          $("#dvCSV").append(table);
        }
        reader.readAsText($("#fileUpload")[0].files[0]);
      } else {
        alert("This browser does not support HTML5.");
      }
    } else {
      alert("Please upload a valid CSV file.");
    }
  });
});

a {
  display: block;
}
table {
  border: 1px solid #ccc;
}
table th {
  background-color: #F7F7F7;
  color: #333;
  font-weight: bold;
}
table th,
table td {
  padding: 5px;
  border-color: #ccc;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<a href="http://samplecsvs.s3.amazonaws.com/SalesJan2009.csv">Dummy CSV</a>
<input type="file" id="fileUpload" />
<input type="button" id="upload" value="Upload" />
<hr />
<div id="dvCSV">
</div>

I made slight adaptations to it. It turns out (at least from the dummy csv I am using), that no quotes are being added...

The split-by-rows regex was not seeming to work, so I changed it to:

var rows = e.target.result.split(/[\r\n|\n]+/);

In the comments you wanted to add thead and th support. To do that, I just added a conditional on the two lines:

cell = (i === 0) ? $("<th>") : $("<td>");
if (i === 0) {
    row = $('<thead>').html(row);
}

You mentioned wanting to separate on things other than a comma. As you already know, you are dealing with "Comma-Separated-Values", so separating by comma is expected... but to make your code more generic, I added the separator in a variable instead (so you can swap that out with what you expect the separator to be):

var separator = ',';
var cells = rows[i].split(separator);

Hope that helped! :)

score -1 · Answer 2 · answered Jan 06 '15 at 06:45

$(function() {
  $("#upload").bind("click", function() {
    var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.csv|.txt)$/;
    if (regex.test($("#fileUpload").val().toLowerCase())) {
      if (typeof(FileReader) != "undefined") {
        var reader = new FileReader(),
          table = $("<table>"),
          separator = ',',
          rows,
          rowLength;

        reader.onload = function(e) {
          rows = e.target.result.split(/[\r\n|\n]+/);
          rowLength = rows.length;

          for (var i = 0; i < rowLength; i++) {
            var row = $("<tr>"),
              cells = rows[i].split(separator),
              cellLength = cells.length,
              cell;

            for (var j = 0; j < cellLength; j++) {
              // Special case for the first row
              cell = (i === 0) ? $("<th>") : $("<td>");
              cell.html(cells[j]);
              row.append(cell);
            }

            // Special case for first row (thead)
            if (i === 0) {
              row = $('<thead>').html(row);
            }
            table.append(row);
          }
          $("#dvCSV").html('');
          $("#dvCSV").append(table);
        }
        reader.readAsText($("#fileUpload")[0].files[0]);
      } else {
        alert("This browser does not support HTML5.");
      }
    } else {
      alert("Please upload a valid CSV file.");
    }
  });
});

a {
  display: block;
}
table {
  border: 1px solid #ccc;
}
table th {
  background-color: #F7F7F7;
  color: #333;
  font-weight: bold;
}
table th,
table td {
  padding: 5px;
  border-color: #ccc;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<a href="http://samplecsvs.s3.amazonaws.com/SalesJan2009.csv">Dummy CSV</a>
<input type="file" id="fileUpload" />
<input type="button" id="upload" value="Upload" />
<hr />
<div id="dvCSV">
</div>

improve csv file reader by removing quotes from fields, supporting a header, and allowing other seperators

2 Answers2

Linked