Js прочитать csv файл
Where could I find some JavaScript code to parse CSV data?
Most of the answers below are just wrong, aside from the one by Andy. Any answer that uses pattern matching or splits is doomed to fail --they will not support escape sequences. For that, you need a finite state machine.
Papa Parse is another option with a lot of features (multi-threaded, header row support, auto-detect delimiter, and more)
Note to people coming upon this via search or otherwise; this is not an example of a good question. Back when Stack Overflow was first starting out, these types of questions were allowed.
3 Answers 3
Here is how to use the readAsBinaryString() from the FileReader API to load a local file.
Basically, just need to listen to change event in and call the readFile function.
I see. thanks!! However, I don't want the user to choose the csv file. the form will have a text field to enter in the subjects, and then it will have a drop down menu of all the schools to choose from. Based on the school they pick, the script needs to import the proper csv file and just output the results for each subject entered, not the whole csv file. Would this still apply?
@ocean800 If you need to load predefined csv files, depending on what the user inputs, then you can do that through the jquery $.ajax or you can include the csv files in your html page as . This is secure, because browsers ignore script without javascript. See this excellent answer
@ocean800 For security reasons, it is not possible to programatically load files from local file system in JS. The only way to do it, is as I did it: using an to let the user be aware of that specific file loading, that the user selected himself. If this does not fit your needs, then you need a backend
Thanks, that link was very helpful! So then, in order to do it that way, I'd have to put the content of each csv file into the html document?
@ocean800 If your csv files never change, then yes you can place their data inside script tags or, more conveniently, in JS arrays such as var csv1 = [< name: "bill", age: 34 >, < name: "ana", age: 44 >, < name: "cory", age: 18>]; var csv2 = [. ] . This exemplifies a csv1 file with 3 rows and 2 columns, name and age.
There are as many ways of accomplishing what you want as you could possibly imagine.
If I were doing this, I might start by splitting the input text into lines like so:
Then, I would extract the names of the headers from the first line. You need a function to read the values from each line.
Next, I would loop over the remaining lines and create an array of objects representing the values in the lines.
If this all works, you should end up with an array of objects representing the values in each line in your CSV.
The above is a rough outline of what the code might look like. I have not tested it and it certainly is not production ready, but I hope it gives you an idea of how to go about doing what you want.
I've used several built-in Javascript functions. I suggest looking them up on MDN if you're not familiar with them; they are good to know.
Finally, there is an odd quirk in Javascript with its automatic semi-colon insertion (a bad feature of the language, IMO). In order to avoid problems, do not put a new-line before an opening brace.
What You'll Be Creating
The CSV (Comma Separated Values) file format is a popular way of exchanging data between applications. In this quick tip, we’ll learn how JavaScript can help us visualize the data of a CSV file.
Converting a CSV File Into an HTML Table
Now that we’ve generated the file, we’re ready to parse it and build an associated HTML table.
As a first step, we’ll use jQuery’s ajax function to retrieve the data from this file:
Assuming the AJAX request is successful, the successFunction is executed. This function is responsible for parsing the returned data and transforming them into an HTML table:
The idea is to convert each of the CSV rows into a table row. With that in mind, let’s briefly explain how the code above works:
- First, we use a regex to split the AJAX response, and thus separate the CSV rows.
- Then, we iterate through the CSV rows and split their data fields.
- Finally, we loop through the data fields and create the corresponding table cells.
Furthermore, to get a better understanding of this code, consider the following visualization:
At this point, it’s important to clarify why we used the /\r?\n|\r/ regex to split the CSV rows.
As you probably already know, there are different representations of a newline across operating systems. For example, on Windows platforms, the characters representing a newline are \r\n . That said, by using the regex above, we’re able to match all those possible representations.
In addition, most text editors allow us to choose the format for a newline. Take, for instance, Notepad++. In this editor, we can specify the desired format for a document by navigating to this path:
To illustrate it, consider our file. Depending on the format we choose, it would look like this:
Преобразование файла CSV в таблицу HTML
Теперь, когда мы сгенерировали файл, мы готовы разобрать его и создать связанную таблицу HTML.
В качестве первого шага мы будем использовать функцию ajax jQuery для извлечения данных из этого файла:
Если запрос AJAX выполнен успешно, функция success выполняется. Эта функция отвечает за разбор возвращаемых данных и преобразование их в таблицу HTML:
Итак, идея состоит в том, чтобы преобразовать каждую из строк CSV в строку таблицы. Имея это в виду, давайте кратко объясним, как работает вышеприведенный код:
- Во-первых, мы используем регулярное выражение для разделения ответа AJAX и, таким образом, разделяем строки CSV.
- Затем мы перебираем строки CSV и разбиваем их поля данных.
- Наконец, мы перебираем поля данных и создаем соответствующие ячейки таблицы.
Более того, чтобы лучше понять этот код, рассмотрите следующую визуализацию:
На данном этапе важно выяснить, почему мы использовали регулярно выражение /\r?\N|\r/ для разбиения строк CSV.
Как вы, наверное, уже знаете, существуют различные представления новой строки в разных операционных системах. Например, на платформах Windows символы, представляющие новую строку, - \r\n . Тем не менее, используя приведенное выше регулярное выражение, мы можем сопоставить все эти возможные представления.
Кроме того, большинство текстовых редакторов позволяют нам выбирать формат для новой строки. Возьмем, к примеру, Notepad ++. В этом редакторе мы можем указать желаемый формат документа, перейдя по этому пути:
Чтобы проиллюстрировать это, рассмотрим наш файл. В зависимости от выбранного формата он будет выглядеть так:
How to Parse a CSV File With the Papa Parse Library
In this section, we’ll see how you can use the Papa Parse library to parse a CSV file in the blink of an eye! Papa Parse is a really powerful CSV parser which provides you with a lot of configuration options, and you could use it for really big CSV files as well.
The Papa Parse library is available on npm, and if you don’t want to use npm, you can download the official Papa Parse npm package from unpkg instead.
Заключение
В этой короткой статье мы рассмотрели процесс преобразования CSV-файла в таблицу HTML. Конечно, мы могли бы использовать веб-инструмент для этого преобразования, но я думаю, что это всегда сложнее достичь, написав собственный код.
Добавление стилей в таблицу HTML
Прежде, чем мы посмотрим на итоговую таблицу, добавим несколько основных стилей:
Вот сгенерированная таблица:
How It Works
The following example demonstrates how easy it is to parse a CSV string.
The results variable holds the following contents.
As you can see, Results.data holds an array of all the rows. If there are any errors during parsing, they will be in Results.errors . Finally, you can use Results.meta to access meta information about the CSV string.
On the other hand, if you want to directly parse a local CSV file, you can pass a JavaScript File object:
And you can also pass in the URL to a remote CSV file:
Apart from basic parsing, Papa Parse provides a lot of other features like:
- streaming large files (so you can process them line by line)
- reverse parsing (to emit CSV from a JavaScript object)
- jQuery integration
- and more
I encourage you to explore this library since it’s really powerful and easy to use!
15 Answers 15
No need to write your own.
The jQuery-CSV library has a function called $.csv.toObjects(csv) that does the mapping automatically.
Note: The library is designed to handle any CSV data that is RFC 4180 compliant, including all of the nasty edge cases that most 'simple' solutions overlook.
Like @Blazemonger already stated, first you need to add line breaks to make the data valid CSV.
Using the following dataset:
Use the code:
The output saved in 'data' will be:
Note: Technically, the way you wrote the key-value mapping is invalid JavaScript. The objects containing the key-value pairs should be wrapped in brackets.
If you want to try it out for yourself, I suggest you take a look at the Basic Usage Demonstration under the 'toObjects()' tab.
Disclaimer: I'm the original author of jQuery-CSV.
Update:
Edited to use the dataset that the op provided and included a link to the demo where the data can be tested for validity.
Update2:
Due to the shuttering of Google Code. jquery-csv has moved to GitHub
IOW, "toObject" is or can be thought of as "toJSON", no? And, is the colon following the call to toObjects(csv) a typo? IOW, shouldn't that be a semicolon?
Fantastic library. FYI, parameter csv passed is a csv string - read the csv file as text to get the csv string.
@RichaSinha Read the file in as a text buffer via the HTML5 File API or AJAX. Then pass the string buffer into the parser. It'll spit out an array of data as a result. See the project page for examples.
@GreySage Understandable, jquery-csv doesn't actually require jquery. It's just a set of add-on utility functions that get attached to the jquery namespace for consistency. The original intent was to extend the jquery (pseudo monad) object model. Maybe one day if the :: operator is added to Javascript, that will become a reality, for now the project scope is frozen in maintenance mode.
NOTE: I concocted this solution before I was reminded about all the "special cases" that can occur in a valid CSV file, like escaped quotes. I'm leaving my answer for those who want something quick and dirty, but I recommend Evan's answer for accuracy.
This code will work when your data.txt file is one long string of comma-separated entries, with no newlines:
The following code will work on a "true" CSV file with linebreaks between each set of records:
By the way, this assumes that the CSV file does in fact have multiple rows -- that's what the allText.split(/\r\n|\n/) splits on. If all your data is in fact one long string of comma-separated data with no newlines, it's not a real CSV file.
Hi I've use this Code: But there is no output. Just a blank alert displayed. my file is look like: heading1,heading2,heading3,heading4,heading5,value1_1,value2_1,value3_1,value4_1,value5_1,value1_2,value2_2,value3_2,value4_2,value5_2 Both csv.html and data.txt are in same folder
The code may not handle all valid IETF standard CSV files, and may fail if there are strings which have embedded commas, line breaks or double quotes. For instance, 1, "IETF allows ""quotes"", commas and \nline breaks" which is allowed since the string is surrounded with double quotes, and the double quotes are escaped.
I was trying to read a .csv file from a mac. I was only able to get this script to recognize newline characters when I changed the first split to this var allTextLines = allText.split("\r"); After that it worked great! Thanks!
Don't split on commas -- it won't work for most CSV files, and this question has wayyyy too many views for the asker's kind of input data to apply to everyone. Parsing CSV is kind of scary since there's no truly official standard, and lots of delimited text writers don't consider edge cases.
This question is old, but I believe there's a better solution now that Papa Parse is available. It's a library I wrote, with help from contributors, that parses CSV text or files. It's the only JS library I know of that supports files gigabytes in size. It also handles malformed input gracefully.
1 GB file parsed in 1 minute:
(Update: With Papa Parse 4, the same file took only about 30 seconds in Firefox. Papa Parse 4 is now the fastest known CSV parser for the browser.)
Parsing text is very easy:
Parsing files is also easy:
Streaming files is similar (here's an example that streams a remote file):
If your web page locks up during parsing, Papa can use web workers to keep your web site reactive.
Papa can auto-detect delimiters and match values up with header columns, if a header row is present. It can also turn numeric values into actual number types. It appropriately parses line breaks and quotes and other weird situations, and even handles malformed input as robustly as possible. I've drawn on inspiration from existing libraries to make Papa, so props to other JS implementations.
Want to improve this question? Update the question so it focuses on one problem only by editing this post.
Closed 7 years ago .
[EDIT] I solved the problem using D3, nevermind thanks!
So I have a csv file that looks something like this, and I need to import a local csv file into my client side javascript:
I eventually need do parse it and output something like:
But for now, I am stuck on just importing it into javascript.
My current code looks like this:
I've researched and found some helpful links on Stackoverflow like this, this, and this but I'm new to javascript and I don't completely understand it. Should I use Ajax? FileReader? jQuery? What are the benefits of using one over the other? And how would you implement this in code?
But yeah, I'm just confused since I'm very new to javascript, so any help in the right direction would be great. Thank you!!
@cybernetic Why did you edit this question to remove all of the details? And neither the original question nor any of the current answers have any mention of the Fetch API.
It was closed, as too general. SO invited me to edit to presumably make it more useful. If it’s not then just ignore my edit.
Sometimes I think SO Posts are closed as "too general" purely for political reasons. Look at the number of upvotes. This question is obviously useful.
13 Answers 13
You can use the CSVToArray() function mentioned in this blog entry.
It gives undefined for empty fields that is quoted. Example: CSVToArray("4,,6") gives me [["4","","6"]] , but CSVToArray("4,\"\",6") gives me [["4",undefined,"6"]] .
I've had issues with this in firefox, and the script has become unresponsive. It seemed to only affect a few users though, so couldn't find the cause
There is a bug in the regex: "([^\"\\" should be "([^\\" . Otherwise a double quote anywhere in an unquoted value will prematurely end it. Found this the hard way.
It's a jQuery plugin designed to work as an end-to-end solution for parsing CSV into JavaScript data. It handles every single edge case presented in RFC 4180, as well as some that pop up for Excel/Google spreadsheet exports (i.e., mostly involving null values) that the specification is missing.
track,artist,album,year
Dangerous,'Busta Rhymes','When Disaster Strikes',1997
Update:
Oh yeah, I should also probably mention that it's completely configurable.
Update 2:
It now works with jQuery on Node.js too. So you have the option of doing either client-side or server-side parsing with the same library.
Update 3:
Disclaimer: I am also the author of jQuery-CSV.
Why is it jQuery csv? Why does it depend on jQuery? I've had a quick scan through the source. it doesn't look like you're using jQuery
@paulslater19 The plugin doesn't depend on jquery. Rather, it follows the common jQuery development guidelines. All of the methods included are static and reside under their own namespace (ie $.csv). To use them without jQuery simply create a global $ object that the plugin will bind to during initialization.
is csv in the solution code refer to the .csv filename ? i'm interested in a good JS/JQuery tool to parse a csv file
Given that it's not dependent on jQuery, it would be better to remove the global "$" dependency and let users pass any object reference they want. Perhaps default to jQuery if it's available. There are other libraries that use "$" and it might be used by development teams with minimal proxies of those libraries.
Here's an extremely simple CSV parser that handles quoted fields with commas, new lines, and escaped double quotation marks. There's no splitting or regular expression. It scans the input string 1-2 characters at a time and builds an array.
This seems cleaner and more straight forward. I had to parse a 4mb file and the other answers crashed on me in ie8, but this managed it.
This also worked for me. I had to do one modification though to allow proper handling of line feeds: if (cc == '\r' && nc == '\n' && !quote) < ++row; col = 0; ++c; continue; >if (cc == '\n' && !quote) < ++row; col = 0; continue; >
Yeah, anytime speed is needed or memory footprints matter, a clean solution like this is far superior. State machine-esque parsing is so much smoother.
I have an implementation as part of a spreadsheet project.
This code is not yet tested thoroughly, but anyone is welcome to use it.
As some of the answers noted though, your implementation can be much simpler if you actually have DSV or TSV file, as they disallow the use of the record and field separators in the values. CSV, on the other hand, can actually have commas and newlines inside a field, which breaks most regular expression and split-based approaches.
If a comma is placed at the end of a line, an empty cell should follow it. This code just skips to the next line, resulting in an undefined cell. For example, console.log(CSV.parse("first,last,age\r\njohn,doe,"));
Also, empty cells should parse to empty strings. This code parses them into zeros, which is confusing since cells can actually contain zeros: console.log(CSV.parse("0,,2,3"));
@skibulk Your second comment is incorrect (at least in Chrome is works fine with your example). Your first comment is valid though, although it is easily fixed - add the following right before if ('\r' === chars[c]) < . >: if (end === c-1) < row.push(reviver(table.length-1, row.length, '')); >
A compact (645 bytes), but compliant function to convert a CSV string into a 2D array, conforming to the RFC4180 standard.
Common Usage: jQuery
Common usage: JavaScript
Override field separator
Override record separator
Override Skip Header
PEG? Isn't building an AST a little memory heavy for a Type III grammar. Can it handle fields that contain newline chars because that's the most difficult case to cover in a 'regular grammar' parser. Either way, +1 for a novel approach.
Well played. +1 for turning me on to PEG. I do love parser-generators. "Why program by hand in five days what you can spend five years of your life automating?" -- Terence Parr, ANTLR
Here's my simple vanilla JavaScript code:
Here's another solution. This uses:
- a coarse global regular expression for splitting the CSV string (which includes surrounding quotes and trailing commas)
- fine-grained regular expression for cleaning up the surrounding quotes and trailing commas
- also, has type correction differentiating strings, numbers and boolean values
For the following input string:
The code outputs:
Here's my implementation of parseCSVLine() in a runnable code snippet:
I made one change to the first regex: text.match( /\s*(\".*?\"|'.*?'|[^,]+|)\s*(,|$)/g ) I had to add the last | to the first capture group to allow for an empty cell in a CSV.
Now I quickly realised this created another edge case for me where it matches an empty string at the end superfluously. Tried adding a negative lookahead to not count an empty at the end: text.match(/\s*(".*?"|'.*?'|[^,]+|(?!$))\s*(,|$)/g) This created another issue where I can't have an empty last cell. I might go back to the original fix and just filter out extra empty cells in the last column.
I'm not sure why I couldn't get Kirtan's example to work for me. It seemed to be failing on empty fields or maybe fields with trailing commas.
This one seems to handle both.
I did not write the parser code, just a wrapper around the parser function to make this work for a file. See attribution.
Regular expressions to the rescue! These few lines of code handle properly quoted fields with embedded commas, quotes, and newlines based on the RFC 4180 standard.
You don't need a parser-generator such as lex/yacc. The regular expression handles RFC 4180 properly thanks to positive lookbehind, negative lookbehind, and positive lookahead.
Just throwing this out there.. I recently ran into the need to parse CSV columns with Javascript, and I opted for my own simple solution. It works for my needs, and may help someone else.
I have constructed this JavaScript script to parse a CSV in string to array object. I find it better to break down the whole CSV into lines, fields and process them accordingly. I think that it will make it easy for you to change the code to suit your need.
Lots of reasons. First, it doesn't remove the double quotes on delimited values. Doesn't handle line splitting. Doesn't escape double-double quotes used to escape double quotes used in delimited values. Doesn't allow empty values. etc, etc. The flexibility of the CSV format makes it very easy to use but difficult to parse. I won't downvote this but only because I don't downvote competing answers.
What about when you encounter a value that contains a newline char? A simple split function will incorrectly interpret it as the end of an entry instead of skipping over it like it should. Parsing CSV is a lot more complicated than just providing 2 split routines (one for newlines, one for delimiters).
(cont) Also split on null values (a,null,,value) returns nothing whereas it should return an empty string. Don't get me wrong, split is a good start if you are 100% positive that the incoming data won't break the parser but creating a robust parser that can handle any data that is RFC 4801 compliant is significantly more complicated.
Evan, I think your javascript library is awesome. But here's another perspective - I appreciated this answer, as I am simply storing a series of numbers in a very predictable fashion. It is much more important to me to get guaranteed cross-browser Javascript compatibility and maintainability as far into the future as possible, than include a large (albeit well-written and well-tested) library. Different needs require different approaches. If I ever need real CSV power I will DEFINITELY commit to using your library! :-)
How do you read this data and convert to an array like this using Javascript?:
[heading1:value1_1 , heading2:value2_1, heading3 : value3_1, heading4 : value4_1, heading5 : value5_1 ],[heading1:value1_2 , heading2:value2_2, heading3 : value3_2, heading4 : value4_2, heading5 : value5_2 ].
I've tried this code but no luck!:
Without line breaks in your CSV file, it will be impossible for any JavaScript code to know where one array (or object) stops and the other begins (unless you know in advance that there are always exactly five headings). Was this a cut-and-paste oversight?
Next question: is jQuery allowed in the solution? You used the tag but your sample code is pure JavaScript.
Creating a CSV File
To begin with, let’s create a simple CSV file. To do this, we’ll take advantage of Mockaroo, an online test data generator. Here’s our file:
Conclusion
In this short article, we went through the process of converting a CSV file into an HTML table. Of course, we could have used a web-based tool for this conversion, but I think that it’s always more challenging to achieve this by writing your own code.
What You'll Be Creating
Формат файла CSV (Comma Separated Values) является популярным способом обмена данными между приложениями.
В этом кратком совете мы узнаем, как JavaScript может помочь нам визуализировать данные CSV-файла.
Создание файла CSV
Для начала создадим простой CSV-файл. Чтобы сделать это, мы воспользуемся Mockaroo - онлайновым генератором тестовых данных. Вот наш файл:
Adding Styles to the HTML Table
Before we look at the resulting table, let’s add a few basic styles to it:
Here’s the generated table:
Читайте также: