shancarter / mr-data-converter Goto Github PK

When converting excel to PHP, strings with ampersands lose their spacing.

e.g. Home & Garden becomes -> Home&Garden.

Thanks,
Phil

I am having an issue when i parse a CSV file as shown below.

Original JSON:

{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"address":
{
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumber":
[
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "fax",
"number": "646 555-4567"
}
]
}

When i use one of the tool online, it converts the above JSON to the below.
firstName,lastName,age,address/streetAddress,address/city,address/state,address/postalCode,phoneNumber/0/type,phoneNumber/0/number,phoneNumber/1/type,phoneNumber/1/number
John,Smith,25,25 2nd Street,New York,NY,10021,home,212 555-1234,fax,646 555-4567

I am trying to convert the above CSV file back to JSON with your tool and i am expecting the output as shown above, but it is different and converting each column header as a element in JSON.

If i want to convert a CSV file to above JSON format how should i provide the CSV to your tool? Basically my JSON can have Arrays, objects and it will be huge. Could you please advise?

It would be nice to add escape double quotes for JSON.

For example:

NAME VALUE
Alan My "task"
Shan I say 'yes'

Please provide options to include quotation marks (simple or double) i order to allow to put in a IN clause on SQL

It would be awesome to be able to go back and forth at will between JSON and CSV or something similar!

URLs are not wrapped inside double quotes in the output

escaping as an option

Hi!

First let me say that Mr. Data Converter is super awesome. Thanks a lot!

I've had an issue when pasting the following code from Excel:

Tag/e Monat/e Jahr Veranstaltung Organisator Referatstitel Ort
14-15 8 2007 Event Stadt Zürich Title Zürich

The first value is a string. But it looks like a calculation. What Mr. D gives back is:

array("val0"=>14-15,"val1"=>8,"val2"=>2007,"val3"=>"Zürcher Ferienpass","val4"=>"Stadt Zürich","val5"=>"Dein Handy und die Technik","val6"=>"Zürich"),

And what PHP gives back for $row['val0'] is: -1
because it does the math for 14-15.

Would it therefore make sense to be able to add double quotes around each value, also numeric ones?

I tried typing out a sample CSV, starting with " and the browser just froze :(

This repository seems to no longer be maintained. Unfortunately or fortunately, the name "Mr data converter" comes to my mind and google very easily, and I'm often wasting a lot of time looking for the other good alternatives that exist out there.

Here is my favourite (works with fields containing quotes, commas, semicolons, line breaks, etc)

• https://www.convertcsv.com/csv-to-json.htm

Feel free to comment following this post to add your favourite alternatives as a memento.

Thanks for building Mr-Data-Converter. I noticed there's no license headers nor a "LICENSE" file in the project. What license is this distributed under?

Thanks!

I often have data that looks more like an object than a flat spreadsheet. Meaninng, there are quite a few cells with no data. A nice feature would allow for data files to be created without "null" values.

What I want is for quotes to wrap the zipcodes when i bring the excel file into Mr. Data Converter. How do I do this please? I've converted them to text in excel. Thank you

isNaN returns true for "34 1/2", "34 232" and so on.

Instead of isNaN() I have changed the function to to use a Regex object as...

isNumber: function(string) {
//trim leading and trailing whitespace
//string = string.toString().replace(/^\s+/,"").replace(/\s+$/,"");
if( (string == null) || !/^-?[0-9]+[.]?[0-9]+$/.test(string) ) {
return false;
}
return true;
},

Go to http://shancarter.github.com/mr-data-converter/ and just type " (one double-quote) in the top box -- chrome appears to freeze

When a column is empty, it would be nice to have an option to output it as an empty string ("") instead of null.

I have a postal code column that contains a leading zero (e.g., 0800 in Australia). Currently these are not quoted in the output, so it's being converted into 800. Please by default quote any integer that begins with zero.

#my data has given below:

title	sometitle	sometitle	sometitle	sometitle
content	xxx	xxx	xxx	xxxx
status	1	1	1	1
id	1	2	3	4
price	15			
icon	right	right	right	right

if you use a .tsv file divided in 5 colums, but:

1. only the first colum have a value on colum 5
2. have >10 rows

The system made error in create json file, the first key:value pair of TEST5Colum do not have ""
And all the other TEST5Colum value is Null instead of ""

Try the bug with this:

NAME	VALUE	COLOR	DATE	TEST5Colum
Alan	12	blue	Sep. 25, 2009	Pippo1
Shan	13	"green	blue"	Sep. 27, 2009	
John	45	orange	Sep. 29, 2009	
Minna	27	teal	Sep. 30, 2009	
Alan	12	blue	Sep. 25, 2009	
Shan	13	"green	blue"	Sep. 27, 2009	
John	45	orange	Sep. 29, 2009	
Minna	27	teal	Sep. 30, 2009	
Alan	12	blue	Sep. 25, 2009	
Shan	13	"green	blue"	Sep. 27, 2009	
Shan	13	"green	blue"	Sep. 27, 2009	

Result:

[{"NAME":"Alan","VALUE":12,"COLOR":"blue","DATE":"Sep. 25, 2009","TEST5Colum":Pippo1},
{"NAME":"Shan","VALUE":13,"COLOR":"green\tblue","DATE":"Sep. 27, 2009","TEST5Colum":null},

Steps to reproduce:

1. Paste this into the input field:

name    unit
"Attitude ""for"" Men"  Shop 5

1. Convert the data. For example, the generated JSON Properties data looks like this:

[{"name":"Attitude "for" Men","unit":"Shop 5"}]

The double quotes around "for" need to be escaped.

MYSQL doesnt show "Timestamp" for the date format copied from the excel file

Is there any specific date format for excel to get as timestamp?

Consider:

A1="A
B1="B

If you copy and just handle the plaintext clipboard, you see "A\t"B, which your system treats as a single cell.

The correct way to handle this is to look at the HTML clipboard, because that has the correct content:

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 14">
<link id=Main-File rel=Main-File
href="...">
<link rel=File-List
href="...">
<style>
<!--table
    {mso-displayed-decimal-separator:"\.";
    mso-displayed-thousand-separator:"\,";}
@page
    {margin:1.0in .75in 1.0in .75in;
    mso-header-margin:.5in;
    mso-footer-margin:.5in;}
td
    {padding-top:1px;
    padding-right:1px;
    padding-left:1px;
    mso-ignore:padding;
    color:black;
    font-size:12.0pt;
    font-weight:400;
    font-style:normal;
    text-decoration:none;
    font-family:Calibri, sans-serif;
    mso-font-charset:0;
    mso-number-format:General;
    text-align:general;
    vertical-align:bottom;
    border:none;
    mso-background-source:auto;
    mso-pattern:auto;
    mso-protection:locked visible;
    white-space:nowrap;
    mso-rotate:0;}
-->
</style>
</head>

<body link=blue vlink=purple>

<table border=0 cellpadding=0 cellspacing=0 width=130 style='border-collapse:
 collapse;width:130pt'>
 <col width=65 span=2 style='width:65pt'>
 <tr height=15 style='height:15.0pt'>
<!--StartFragment-->
  <td height=15 width=65 style='height:15.0pt;width:65pt'>&quot;A</td>
  <td width=65 style='width:65pt'>&quot;B</td>
<!--EndFragment-->
 </tr>
</table>

</body>

</html>

good input:

A,B
a,1
,1
,1
,1
,1
,1
,1
,1
,1
,1

good output:

[{"A":"a","B":1},
{"A":"","B":1},
{"A":"","B":1},
{"A":"","B":1},
{"A":"","B":1},
{"A":"","B":1},
{"A":"","B":1},
{"A":"","B":1},
{"A":"","B":1},
{"A":"","B":1}]

bad input:

A,B
a,1
,1
,1
,1
,1
,1
,1
,1
,1
,1
,1

bad output:

[{"A":a,"B":1},
{"A":null,"B":1},
{"A":null,"B":1},
{"A":null,"B":1},
{"A":null,"B":1},
{"A":null,"B":1},
{"A":null,"B":1},
{"A":null,"B":1},
{"A":null,"B":1},
{"A":null,"B":1},
{"A":null,"B":1}]

The SQL output option uses single quote to delimit strings, but doesn't escape single quotes inside the string. They should be escaped with two single quotes.

Happy to put together a patch / pull request, but wanted to check whether this project is still maintained first.

Feature request:

Is it possible to add Hierarchical JSON to list of formats?

example data:

fruit   color   taste
apple   red sweet
apple   red sour
apple   yellow  bitter
pear    green   sweet
pear    green   sour

desired output from Mr-Data-Converter:

[
   {
      "fruit":"apple",
      "color":{
         "red":{
            "taste":"sweet",
            "taste":"sour"
         }
      },
      "color":{
         "yellow":{
            "taste":"bitter"
         }
      },
      "fruit":"pear",
      "color":{
         "green":{
            "taste":"sweet",
            "taste":"sour"
         }
      }
   }
]

Excel supports a header line with sep=; to denote semicolon-delimited files.

Thanks for all the awesome work. This tool is amazing. Not really a feature request or issue, more like a desire :)
Would like to see this beautiful tool integraded with Notepad++

Regards!
Pablo.

Is there any way to save the data that is outputted from the converter in a .json file and upload it to a server. My idea is that a person types in a title for the data sheet, pastes in excel sheet, and then clicks submit to upload the Json output as a .json file to my server. Is this possible?

Hi! First, awesome tool. Love it.

A big improvement (IMO) would be native conversion of TRUE and FALSE (case-insensitive) to the lowercase, unquoted true/false JSON equivalents. Currently, they are outputted as strings, which is somewhat counterintuitive since numbers are outputted as numbers and not stringified ones.