3

I exported an SQL table in a JSON file using PhpMyAdmin tools.

I got a file like this:

/**
 Export to JSON plugin for PHPMyAdmin
 @version 0.1
 */

// Database 'table_name'
// ...

[{"id": 7,"field1": "value1","field2": 2,"field3": "Lorem^M
ipsum^M
dolor^M
sit^M
amet","field4": "value4"}]

It's clear that the first lines (that are comments are not valid because JSON doesn't allow comments).

I know that ^M is caused by the DOS/Windows line-ending characters.

Running jsonlint myexportedfile.json after removing the comments I get this:

[Error: Parse error on line 1:
dolorld2": 2,"field3": "Lorem
-----------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '[', got 'undefined']

Still, is this a valid JSON format? What character am I supposed to replace ^M character? New line (\\n), or?

I want to be able to do require("./myexportedfile") in NodeJS and to get the data and then to make some changes.

Community
  • 1
  • 1
Ionică Bizău
  • 109,027
  • 88
  • 289
  • 474

1 Answers1

1

after removing the lines with comments you can apply the following perl command on the file:

perl -pi -e 's/\r\n/\n/g'

its changing the file inplace (!) removing all the ^M in them and making a real unix file out of it.

Axel Amthor
  • 10,980
  • 1
  • 25
  • 44
  • I run `perl -pi -e 's/\r\n/\n/g' myexportedfile.json` but it just replaced `^M` characters with nothing. The json file is still invalid... :-( – Ionică Bizău Jan 21 '14 at 18:29
  • Its not about phpMyAdmin. The `^M` is the value of your JSON data and replacing this with "nothing" is the only thing you can do at this time. I assume this data is coming from some textbox where the user has hit on RETURN or copied DOS text into it. Removing the ^M and pasting into JSONLINT gave me an ok? – Axel Amthor Jan 21 '14 at 18:35
  • I found the following workaround: export as csv, replace `^M` with nothing, convert to JSON. – Ionică Bizău Jul 01 '14 at 06:33