How to replace commas within strings when converting a CSV to JSON (JS)
I'm having a mapping issue when trying to convert a client supplied CSV into JSON data using JS. One of the columns in the CSV contains address data which will contain commas. I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas. This means the mapping when converting the data to JSON is incorrect. code and outputs below :-
JS :-
$(document).ready(function() {
$.ajax({
type: "GET",
url: "result.csv",
dataType: "text",
success: function(data) { $("body").append(csvJSON(data));}
});
});
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var headers=lines[0].split(",");
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(",");
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j];
}
result.push(obj);
}
return JSON.stringify(result);
}
CSV DATA :-
"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Whitechapel Ltd","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",13077399478,"+1 307 739-9478","whitechapel@wyoming.com","www.whitechapel-ltd.com"
"Stockist","Point",103.82705,1.30637,"Thrive Design & Trading","19, Tanglin Road, #03-35","Tanglin Shopping Centre","Singapore",,247909,"Singapore","65-67357333","65-67357333","francis@thrive-products.com.sg",
CURRENT RESULT :-
{
"type":"Stockist",
"geometry__type":"Point",
"geometry__coordinates__001":"-110.788",
"geometry__coordinates__002":"43.4705",
"properties__name":"Whitechapel Ltd",
"properties__address":""Box 11719",
"properties__address2":" 1135 Maple Way"",
"properties__city":""Jackson",
"properties__state":""",
"properties__postal":"Wyoming",
"properties__country":"WY",
"properties__phone":"83002",
"properties__phoneFormatted":"US",
"properties__email":"13077399478",
"properties__webr":"+1 307 739-9478"
},
DESIRED RESULT :-
{
"type":"Stockist",
"geometry__type":"Point",
"geometry__coordinates__001":"-110.788",
"geometry__coordinates__002":"43.4705",
"properties__name":"Whitechapel Ltd",
"properties__address":"Box 11719, 1135 Maple Way",
"properties__address2":"Jackson,",
"properties__city":"Wyoming",
"properties__state":"WY",
"properties__postal":"83002",
"properties__country":"US",
"properties__phone":"13077399478",
"properties__phoneFormatted":"+1 307 739-9478",
"properties__email":"whitechapel@wyoming.com",
"properties__web":"www.whitechapel-ltd.com"
},
javascript jquery arrays json csv
|
show 4 more comments
I'm having a mapping issue when trying to convert a client supplied CSV into JSON data using JS. One of the columns in the CSV contains address data which will contain commas. I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas. This means the mapping when converting the data to JSON is incorrect. code and outputs below :-
JS :-
$(document).ready(function() {
$.ajax({
type: "GET",
url: "result.csv",
dataType: "text",
success: function(data) { $("body").append(csvJSON(data));}
});
});
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var headers=lines[0].split(",");
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(",");
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j];
}
result.push(obj);
}
return JSON.stringify(result);
}
CSV DATA :-
"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Whitechapel Ltd","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",13077399478,"+1 307 739-9478","whitechapel@wyoming.com","www.whitechapel-ltd.com"
"Stockist","Point",103.82705,1.30637,"Thrive Design & Trading","19, Tanglin Road, #03-35","Tanglin Shopping Centre","Singapore",,247909,"Singapore","65-67357333","65-67357333","francis@thrive-products.com.sg",
CURRENT RESULT :-
{
"type":"Stockist",
"geometry__type":"Point",
"geometry__coordinates__001":"-110.788",
"geometry__coordinates__002":"43.4705",
"properties__name":"Whitechapel Ltd",
"properties__address":""Box 11719",
"properties__address2":" 1135 Maple Way"",
"properties__city":""Jackson",
"properties__state":""",
"properties__postal":"Wyoming",
"properties__country":"WY",
"properties__phone":"83002",
"properties__phoneFormatted":"US",
"properties__email":"13077399478",
"properties__webr":"+1 307 739-9478"
},
DESIRED RESULT :-
{
"type":"Stockist",
"geometry__type":"Point",
"geometry__coordinates__001":"-110.788",
"geometry__coordinates__002":"43.4705",
"properties__name":"Whitechapel Ltd",
"properties__address":"Box 11719, 1135 Maple Way",
"properties__address2":"Jackson,",
"properties__city":"Wyoming",
"properties__state":"WY",
"properties__postal":"83002",
"properties__country":"US",
"properties__phone":"13077399478",
"properties__phoneFormatted":"+1 307 739-9478",
"properties__email":"whitechapel@wyoming.com",
"properties__web":"www.whitechapel-ltd.com"
},
javascript jquery arrays json csv
1
in which field you want to remove comma
– Negi Rox
Nov 15 '18 at 12:53
" I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas."...the code above uses commas throughout. Can you show us an example of what you did, exactly? Your statement doesn't entirely make sense...code doesn't "ignore" things, it just does what it's told, so perhaps you made some small mistake. Also please show the source CSV data which would translate to the JSON above. That will make it a lot clearer. Normally a CSV puts double-quotes around the fields so you can tell which bits is a delimiter and which is content
– ADyson
Nov 15 '18 at 12:54
I dont want to remove the commas, as you can see im splitting the CSV data by comma, this is causing data to not match up to the correct header. Changing the delimiter doesn't seem to work, JS always reads the file with comma as the delimiter.
– M T
Nov 15 '18 at 12:55
what is content of result.csv
– Kamuran Sönecek
Nov 15 '18 at 12:55
can you please post a simple fiddle with sample data (csv) ?
– fanjabi
Nov 15 '18 at 12:56
|
show 4 more comments
I'm having a mapping issue when trying to convert a client supplied CSV into JSON data using JS. One of the columns in the CSV contains address data which will contain commas. I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas. This means the mapping when converting the data to JSON is incorrect. code and outputs below :-
JS :-
$(document).ready(function() {
$.ajax({
type: "GET",
url: "result.csv",
dataType: "text",
success: function(data) { $("body").append(csvJSON(data));}
});
});
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var headers=lines[0].split(",");
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(",");
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j];
}
result.push(obj);
}
return JSON.stringify(result);
}
CSV DATA :-
"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Whitechapel Ltd","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",13077399478,"+1 307 739-9478","whitechapel@wyoming.com","www.whitechapel-ltd.com"
"Stockist","Point",103.82705,1.30637,"Thrive Design & Trading","19, Tanglin Road, #03-35","Tanglin Shopping Centre","Singapore",,247909,"Singapore","65-67357333","65-67357333","francis@thrive-products.com.sg",
CURRENT RESULT :-
{
"type":"Stockist",
"geometry__type":"Point",
"geometry__coordinates__001":"-110.788",
"geometry__coordinates__002":"43.4705",
"properties__name":"Whitechapel Ltd",
"properties__address":""Box 11719",
"properties__address2":" 1135 Maple Way"",
"properties__city":""Jackson",
"properties__state":""",
"properties__postal":"Wyoming",
"properties__country":"WY",
"properties__phone":"83002",
"properties__phoneFormatted":"US",
"properties__email":"13077399478",
"properties__webr":"+1 307 739-9478"
},
DESIRED RESULT :-
{
"type":"Stockist",
"geometry__type":"Point",
"geometry__coordinates__001":"-110.788",
"geometry__coordinates__002":"43.4705",
"properties__name":"Whitechapel Ltd",
"properties__address":"Box 11719, 1135 Maple Way",
"properties__address2":"Jackson,",
"properties__city":"Wyoming",
"properties__state":"WY",
"properties__postal":"83002",
"properties__country":"US",
"properties__phone":"13077399478",
"properties__phoneFormatted":"+1 307 739-9478",
"properties__email":"whitechapel@wyoming.com",
"properties__web":"www.whitechapel-ltd.com"
},
javascript jquery arrays json csv
I'm having a mapping issue when trying to convert a client supplied CSV into JSON data using JS. One of the columns in the CSV contains address data which will contain commas. I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas. This means the mapping when converting the data to JSON is incorrect. code and outputs below :-
JS :-
$(document).ready(function() {
$.ajax({
type: "GET",
url: "result.csv",
dataType: "text",
success: function(data) { $("body").append(csvJSON(data));}
});
});
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var headers=lines[0].split(",");
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(",");
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j];
}
result.push(obj);
}
return JSON.stringify(result);
}
CSV DATA :-
"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Whitechapel Ltd","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",13077399478,"+1 307 739-9478","whitechapel@wyoming.com","www.whitechapel-ltd.com"
"Stockist","Point",103.82705,1.30637,"Thrive Design & Trading","19, Tanglin Road, #03-35","Tanglin Shopping Centre","Singapore",,247909,"Singapore","65-67357333","65-67357333","francis@thrive-products.com.sg",
CURRENT RESULT :-
{
"type":"Stockist",
"geometry__type":"Point",
"geometry__coordinates__001":"-110.788",
"geometry__coordinates__002":"43.4705",
"properties__name":"Whitechapel Ltd",
"properties__address":""Box 11719",
"properties__address2":" 1135 Maple Way"",
"properties__city":""Jackson",
"properties__state":""",
"properties__postal":"Wyoming",
"properties__country":"WY",
"properties__phone":"83002",
"properties__phoneFormatted":"US",
"properties__email":"13077399478",
"properties__webr":"+1 307 739-9478"
},
DESIRED RESULT :-
{
"type":"Stockist",
"geometry__type":"Point",
"geometry__coordinates__001":"-110.788",
"geometry__coordinates__002":"43.4705",
"properties__name":"Whitechapel Ltd",
"properties__address":"Box 11719, 1135 Maple Way",
"properties__address2":"Jackson,",
"properties__city":"Wyoming",
"properties__state":"WY",
"properties__postal":"83002",
"properties__country":"US",
"properties__phone":"13077399478",
"properties__phoneFormatted":"+1 307 739-9478",
"properties__email":"whitechapel@wyoming.com",
"properties__web":"www.whitechapel-ltd.com"
},
javascript jquery arrays json csv
javascript jquery arrays json csv
edited Nov 15 '18 at 13:06
M T
asked Nov 15 '18 at 12:51
M TM T
326
326
1
in which field you want to remove comma
– Negi Rox
Nov 15 '18 at 12:53
" I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas."...the code above uses commas throughout. Can you show us an example of what you did, exactly? Your statement doesn't entirely make sense...code doesn't "ignore" things, it just does what it's told, so perhaps you made some small mistake. Also please show the source CSV data which would translate to the JSON above. That will make it a lot clearer. Normally a CSV puts double-quotes around the fields so you can tell which bits is a delimiter and which is content
– ADyson
Nov 15 '18 at 12:54
I dont want to remove the commas, as you can see im splitting the CSV data by comma, this is causing data to not match up to the correct header. Changing the delimiter doesn't seem to work, JS always reads the file with comma as the delimiter.
– M T
Nov 15 '18 at 12:55
what is content of result.csv
– Kamuran Sönecek
Nov 15 '18 at 12:55
can you please post a simple fiddle with sample data (csv) ?
– fanjabi
Nov 15 '18 at 12:56
|
show 4 more comments
1
in which field you want to remove comma
– Negi Rox
Nov 15 '18 at 12:53
" I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas."...the code above uses commas throughout. Can you show us an example of what you did, exactly? Your statement doesn't entirely make sense...code doesn't "ignore" things, it just does what it's told, so perhaps you made some small mistake. Also please show the source CSV data which would translate to the JSON above. That will make it a lot clearer. Normally a CSV puts double-quotes around the fields so you can tell which bits is a delimiter and which is content
– ADyson
Nov 15 '18 at 12:54
I dont want to remove the commas, as you can see im splitting the CSV data by comma, this is causing data to not match up to the correct header. Changing the delimiter doesn't seem to work, JS always reads the file with comma as the delimiter.
– M T
Nov 15 '18 at 12:55
what is content of result.csv
– Kamuran Sönecek
Nov 15 '18 at 12:55
can you please post a simple fiddle with sample data (csv) ?
– fanjabi
Nov 15 '18 at 12:56
1
1
in which field you want to remove comma
– Negi Rox
Nov 15 '18 at 12:53
in which field you want to remove comma
– Negi Rox
Nov 15 '18 at 12:53
" I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas."...the code above uses commas throughout. Can you show us an example of what you did, exactly? Your statement doesn't entirely make sense...code doesn't "ignore" things, it just does what it's told, so perhaps you made some small mistake. Also please show the source CSV data which would translate to the JSON above. That will make it a lot clearer. Normally a CSV puts double-quotes around the fields so you can tell which bits is a delimiter and which is content
– ADyson
Nov 15 '18 at 12:54
" I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas."...the code above uses commas throughout. Can you show us an example of what you did, exactly? Your statement doesn't entirely make sense...code doesn't "ignore" things, it just does what it's told, so perhaps you made some small mistake. Also please show the source CSV data which would translate to the JSON above. That will make it a lot clearer. Normally a CSV puts double-quotes around the fields so you can tell which bits is a delimiter and which is content
– ADyson
Nov 15 '18 at 12:54
I dont want to remove the commas, as you can see im splitting the CSV data by comma, this is causing data to not match up to the correct header. Changing the delimiter doesn't seem to work, JS always reads the file with comma as the delimiter.
– M T
Nov 15 '18 at 12:55
I dont want to remove the commas, as you can see im splitting the CSV data by comma, this is causing data to not match up to the correct header. Changing the delimiter doesn't seem to work, JS always reads the file with comma as the delimiter.
– M T
Nov 15 '18 at 12:55
what is content of result.csv
– Kamuran Sönecek
Nov 15 '18 at 12:55
what is content of result.csv
– Kamuran Sönecek
Nov 15 '18 at 12:55
can you please post a simple fiddle with sample data (csv) ?
– fanjabi
Nov 15 '18 at 12:56
can you please post a simple fiddle with sample data (csv) ?
– fanjabi
Nov 15 '18 at 12:56
|
show 4 more comments
1 Answer
1
active
oldest
votes
Since all your values are wrapped in quotes, you should change your split parameter to a regular expression that only splits when a comma is outside of a pair of quotes.
Because of the way your data is formatted you should keep in mind that you'll have a lot of unnecessary escaped quotation strings that you'll need to clean up. But you have to make sure you don't accidentally clean up any quotes that were already escaped in the csv to begin with. But I suppose these edge cases are why people use a prebuilt library.
EDIT
In regards to my previous statement. You might be able to get away with simply removing only the quotes at the beginning and end of the string. But I'm just gonna throw in a disclaimer and say your mileage with this result may vary depending on your data.
const csv=`"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Dummy address","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",12313213213213,"+111111111","dummy ","dummy web address"`
console.log(csvJSON(csv))
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var commaRegex = /,(?=(?:[^"]*"[^"]*")*[^"]*$)/g
var quotesRegex = /^"(.*)"$/g
var headers = lines[0].split(commaRegex).map(h => h.replace(quotesRegex, "$1"));
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(commaRegex);
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j].replace(quotesRegex, "$1");
}
result.push(obj);
}
return result;
//return JSON.stringify(result);
}
I edited the CSV data in your answer to include all of my data not just the first two rows, notice the error, I'm having a bit of trouble finding the undefined value, i'm a bit nooby when it comes to chromes debugging tools, any ideas?
– M T
Nov 15 '18 at 14:08
I saw. The error comes from a trailing newline at the end of the input. Remove it and it works. Note that I used a template literal string, so any newline in the code will also be in the string. But this is another edge-case reason to use a pre-built parser. You don't have to deal with empty space at the beginning or end of the file.
– Khauri McClain
Nov 15 '18 at 14:10
Whitespace is actually a small problem for your homebrew parser. But If you want a quick work around you can just use trim on csv string before you start parsing. And even potentially on each line too. But that's up to you.
– Khauri McClain
Nov 15 '18 at 14:12
sorted, you're solution works perfectly. note to self: stop trying to stay away from regex
– M T
Nov 15 '18 at 14:23
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319890%2fhow-to-replace-commas-within-strings-when-converting-a-csv-to-json-js%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Since all your values are wrapped in quotes, you should change your split parameter to a regular expression that only splits when a comma is outside of a pair of quotes.
Because of the way your data is formatted you should keep in mind that you'll have a lot of unnecessary escaped quotation strings that you'll need to clean up. But you have to make sure you don't accidentally clean up any quotes that were already escaped in the csv to begin with. But I suppose these edge cases are why people use a prebuilt library.
EDIT
In regards to my previous statement. You might be able to get away with simply removing only the quotes at the beginning and end of the string. But I'm just gonna throw in a disclaimer and say your mileage with this result may vary depending on your data.
const csv=`"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Dummy address","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",12313213213213,"+111111111","dummy ","dummy web address"`
console.log(csvJSON(csv))
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var commaRegex = /,(?=(?:[^"]*"[^"]*")*[^"]*$)/g
var quotesRegex = /^"(.*)"$/g
var headers = lines[0].split(commaRegex).map(h => h.replace(quotesRegex, "$1"));
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(commaRegex);
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j].replace(quotesRegex, "$1");
}
result.push(obj);
}
return result;
//return JSON.stringify(result);
}
I edited the CSV data in your answer to include all of my data not just the first two rows, notice the error, I'm having a bit of trouble finding the undefined value, i'm a bit nooby when it comes to chromes debugging tools, any ideas?
– M T
Nov 15 '18 at 14:08
I saw. The error comes from a trailing newline at the end of the input. Remove it and it works. Note that I used a template literal string, so any newline in the code will also be in the string. But this is another edge-case reason to use a pre-built parser. You don't have to deal with empty space at the beginning or end of the file.
– Khauri McClain
Nov 15 '18 at 14:10
Whitespace is actually a small problem for your homebrew parser. But If you want a quick work around you can just use trim on csv string before you start parsing. And even potentially on each line too. But that's up to you.
– Khauri McClain
Nov 15 '18 at 14:12
sorted, you're solution works perfectly. note to self: stop trying to stay away from regex
– M T
Nov 15 '18 at 14:23
add a comment |
Since all your values are wrapped in quotes, you should change your split parameter to a regular expression that only splits when a comma is outside of a pair of quotes.
Because of the way your data is formatted you should keep in mind that you'll have a lot of unnecessary escaped quotation strings that you'll need to clean up. But you have to make sure you don't accidentally clean up any quotes that were already escaped in the csv to begin with. But I suppose these edge cases are why people use a prebuilt library.
EDIT
In regards to my previous statement. You might be able to get away with simply removing only the quotes at the beginning and end of the string. But I'm just gonna throw in a disclaimer and say your mileage with this result may vary depending on your data.
const csv=`"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Dummy address","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",12313213213213,"+111111111","dummy ","dummy web address"`
console.log(csvJSON(csv))
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var commaRegex = /,(?=(?:[^"]*"[^"]*")*[^"]*$)/g
var quotesRegex = /^"(.*)"$/g
var headers = lines[0].split(commaRegex).map(h => h.replace(quotesRegex, "$1"));
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(commaRegex);
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j].replace(quotesRegex, "$1");
}
result.push(obj);
}
return result;
//return JSON.stringify(result);
}
I edited the CSV data in your answer to include all of my data not just the first two rows, notice the error, I'm having a bit of trouble finding the undefined value, i'm a bit nooby when it comes to chromes debugging tools, any ideas?
– M T
Nov 15 '18 at 14:08
I saw. The error comes from a trailing newline at the end of the input. Remove it and it works. Note that I used a template literal string, so any newline in the code will also be in the string. But this is another edge-case reason to use a pre-built parser. You don't have to deal with empty space at the beginning or end of the file.
– Khauri McClain
Nov 15 '18 at 14:10
Whitespace is actually a small problem for your homebrew parser. But If you want a quick work around you can just use trim on csv string before you start parsing. And even potentially on each line too. But that's up to you.
– Khauri McClain
Nov 15 '18 at 14:12
sorted, you're solution works perfectly. note to self: stop trying to stay away from regex
– M T
Nov 15 '18 at 14:23
add a comment |
Since all your values are wrapped in quotes, you should change your split parameter to a regular expression that only splits when a comma is outside of a pair of quotes.
Because of the way your data is formatted you should keep in mind that you'll have a lot of unnecessary escaped quotation strings that you'll need to clean up. But you have to make sure you don't accidentally clean up any quotes that were already escaped in the csv to begin with. But I suppose these edge cases are why people use a prebuilt library.
EDIT
In regards to my previous statement. You might be able to get away with simply removing only the quotes at the beginning and end of the string. But I'm just gonna throw in a disclaimer and say your mileage with this result may vary depending on your data.
const csv=`"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Dummy address","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",12313213213213,"+111111111","dummy ","dummy web address"`
console.log(csvJSON(csv))
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var commaRegex = /,(?=(?:[^"]*"[^"]*")*[^"]*$)/g
var quotesRegex = /^"(.*)"$/g
var headers = lines[0].split(commaRegex).map(h => h.replace(quotesRegex, "$1"));
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(commaRegex);
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j].replace(quotesRegex, "$1");
}
result.push(obj);
}
return result;
//return JSON.stringify(result);
}
Since all your values are wrapped in quotes, you should change your split parameter to a regular expression that only splits when a comma is outside of a pair of quotes.
Because of the way your data is formatted you should keep in mind that you'll have a lot of unnecessary escaped quotation strings that you'll need to clean up. But you have to make sure you don't accidentally clean up any quotes that were already escaped in the csv to begin with. But I suppose these edge cases are why people use a prebuilt library.
EDIT
In regards to my previous statement. You might be able to get away with simply removing only the quotes at the beginning and end of the string. But I'm just gonna throw in a disclaimer and say your mileage with this result may vary depending on your data.
const csv=`"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Dummy address","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",12313213213213,"+111111111","dummy ","dummy web address"`
console.log(csvJSON(csv))
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var commaRegex = /,(?=(?:[^"]*"[^"]*")*[^"]*$)/g
var quotesRegex = /^"(.*)"$/g
var headers = lines[0].split(commaRegex).map(h => h.replace(quotesRegex, "$1"));
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(commaRegex);
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j].replace(quotesRegex, "$1");
}
result.push(obj);
}
return result;
//return JSON.stringify(result);
}
const csv=`"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Dummy address","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",12313213213213,"+111111111","dummy ","dummy web address"`
console.log(csvJSON(csv))
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var commaRegex = /,(?=(?:[^"]*"[^"]*")*[^"]*$)/g
var quotesRegex = /^"(.*)"$/g
var headers = lines[0].split(commaRegex).map(h => h.replace(quotesRegex, "$1"));
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(commaRegex);
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j].replace(quotesRegex, "$1");
}
result.push(obj);
}
return result;
//return JSON.stringify(result);
}
const csv=`"type","geometry__type","geometry__coordinates__001","geometry__coordinates__002","properties__name","properties__address","properties__address2","properties__city","properties__state","properties__postal","properties__country","properties__phone","properties__phoneFormatted","properties__email","properties__web"
"Stockist","Point",-110.788,43.4705,"Dummy address","Box 11719, 1135 Maple Way","Jackson,","Wyoming","WY",83002,"US",12313213213213,"+111111111","dummy ","dummy web address"`
console.log(csvJSON(csv))
function csvJSON(csv){
var lines=csv.split("n");
var result = ;
var commaRegex = /,(?=(?:[^"]*"[^"]*")*[^"]*$)/g
var quotesRegex = /^"(.*)"$/g
var headers = lines[0].split(commaRegex).map(h => h.replace(quotesRegex, "$1"));
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline=lines[i].split(commaRegex);
for(var j=0;j<headers.length;j++){
obj[headers[j]] = currentline[j].replace(quotesRegex, "$1");
}
result.push(obj);
}
return result;
//return JSON.stringify(result);
}
edited Nov 15 '18 at 16:09
M T
326
326
answered Nov 15 '18 at 13:26
Khauri McClainKhauri McClain
2,0961414
2,0961414
I edited the CSV data in your answer to include all of my data not just the first two rows, notice the error, I'm having a bit of trouble finding the undefined value, i'm a bit nooby when it comes to chromes debugging tools, any ideas?
– M T
Nov 15 '18 at 14:08
I saw. The error comes from a trailing newline at the end of the input. Remove it and it works. Note that I used a template literal string, so any newline in the code will also be in the string. But this is another edge-case reason to use a pre-built parser. You don't have to deal with empty space at the beginning or end of the file.
– Khauri McClain
Nov 15 '18 at 14:10
Whitespace is actually a small problem for your homebrew parser. But If you want a quick work around you can just use trim on csv string before you start parsing. And even potentially on each line too. But that's up to you.
– Khauri McClain
Nov 15 '18 at 14:12
sorted, you're solution works perfectly. note to self: stop trying to stay away from regex
– M T
Nov 15 '18 at 14:23
add a comment |
I edited the CSV data in your answer to include all of my data not just the first two rows, notice the error, I'm having a bit of trouble finding the undefined value, i'm a bit nooby when it comes to chromes debugging tools, any ideas?
– M T
Nov 15 '18 at 14:08
I saw. The error comes from a trailing newline at the end of the input. Remove it and it works. Note that I used a template literal string, so any newline in the code will also be in the string. But this is another edge-case reason to use a pre-built parser. You don't have to deal with empty space at the beginning or end of the file.
– Khauri McClain
Nov 15 '18 at 14:10
Whitespace is actually a small problem for your homebrew parser. But If you want a quick work around you can just use trim on csv string before you start parsing. And even potentially on each line too. But that's up to you.
– Khauri McClain
Nov 15 '18 at 14:12
sorted, you're solution works perfectly. note to self: stop trying to stay away from regex
– M T
Nov 15 '18 at 14:23
I edited the CSV data in your answer to include all of my data not just the first two rows, notice the error, I'm having a bit of trouble finding the undefined value, i'm a bit nooby when it comes to chromes debugging tools, any ideas?
– M T
Nov 15 '18 at 14:08
I edited the CSV data in your answer to include all of my data not just the first two rows, notice the error, I'm having a bit of trouble finding the undefined value, i'm a bit nooby when it comes to chromes debugging tools, any ideas?
– M T
Nov 15 '18 at 14:08
I saw. The error comes from a trailing newline at the end of the input. Remove it and it works. Note that I used a template literal string, so any newline in the code will also be in the string. But this is another edge-case reason to use a pre-built parser. You don't have to deal with empty space at the beginning or end of the file.
– Khauri McClain
Nov 15 '18 at 14:10
I saw. The error comes from a trailing newline at the end of the input. Remove it and it works. Note that I used a template literal string, so any newline in the code will also be in the string. But this is another edge-case reason to use a pre-built parser. You don't have to deal with empty space at the beginning or end of the file.
– Khauri McClain
Nov 15 '18 at 14:10
Whitespace is actually a small problem for your homebrew parser. But If you want a quick work around you can just use trim on csv string before you start parsing. And even potentially on each line too. But that's up to you.
– Khauri McClain
Nov 15 '18 at 14:12
Whitespace is actually a small problem for your homebrew parser. But If you want a quick work around you can just use trim on csv string before you start parsing. And even potentially on each line too. But that's up to you.
– Khauri McClain
Nov 15 '18 at 14:12
sorted, you're solution works perfectly. note to self: stop trying to stay away from regex
– M T
Nov 15 '18 at 14:23
sorted, you're solution works perfectly. note to self: stop trying to stay away from regex
– M T
Nov 15 '18 at 14:23
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319890%2fhow-to-replace-commas-within-strings-when-converting-a-csv-to-json-js%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
in which field you want to remove comma
– Negi Rox
Nov 15 '18 at 12:53
" I've tried changing the delimiter but for some reason when i read the CSV JS ignores the set delimiter and converts them to commas."...the code above uses commas throughout. Can you show us an example of what you did, exactly? Your statement doesn't entirely make sense...code doesn't "ignore" things, it just does what it's told, so perhaps you made some small mistake. Also please show the source CSV data which would translate to the JSON above. That will make it a lot clearer. Normally a CSV puts double-quotes around the fields so you can tell which bits is a delimiter and which is content
– ADyson
Nov 15 '18 at 12:54
I dont want to remove the commas, as you can see im splitting the CSV data by comma, this is causing data to not match up to the correct header. Changing the delimiter doesn't seem to work, JS always reads the file with comma as the delimiter.
– M T
Nov 15 '18 at 12:55
what is content of result.csv
– Kamuran Sönecek
Nov 15 '18 at 12:55
can you please post a simple fiddle with sample data (csv) ?
– fanjabi
Nov 15 '18 at 12:56