HN Explorer

81 comments

[ 4.9 ms ] story [ 152 ms ] thread
danbruc
Notepadd++ search and replace using the regular expressions

  ^\{"name":("[^"]+)".+"creditcard":("[^"]+")\},?$
and replace it with

  \1,\2
then sort the file using the TextFX plugin and delete all the lines without credit card number at the bottom. Type the header line and done. Five to ten minutes including some manual sanity checking. Of course don't forget to look at the calendar and save with the correct file name.
corbinpage
Possibly use transformy, which was on HN yesterday and is featured on Product Hunt today.

http://www.transformy.io/

Or Ruby.

danbruc 

  "Keeley Bosco","1228-1221-1221-1431"
  "Rubye Jerde","1221-1221-1221-"
  "Miss Darian","null---"
  "Celine Ankunding","0700-creditcard-creditcard-1221"
  "Dr Araceli","creditcard-1211-1211-1234"
  "Esteban Von","---"
  "Everette Swift","creditcard-null-null-"
  "Terrell Boyle","0700-creditcard-creditcard-1221"
  "Miss Emmie","---"
  "Libby Renner","1221-1211-1211-"
nyir
They shouldn't have put each record on a single line, that makes it way too easy for text editors. Assume that this a single line or there are arbitrary newlines inserted and then use a proper parser/filter, e.g. jq in the shell as mentioned in the comments.
danbruc
Replace },{ with },\r\n{ and done. Using more sophisticated tools than a text editor only makes sense if you know them well, the task is more complex or the file larger.
nyir
Sure, which is why I suggested to make the task complex enough so that the "usual" *nix text tools aren't enough, or significantly more complex to use then a real parser. I mean it's a basically a rehash of "how do I parse HTML with regexes".
whoopdedo
Also could have thrown in one or two names with a comma. Hardly anyone is quoting the output fields.
vitorbaptistaa
Using the excellent jq [1] command

  wget https://gist.githubusercontent.com/jorin-vogel/7f19ce95a9a842956358/raw/e319340c2f6691f9cc8d8cc57ed532b5093e3619/data.json
  echo 'Name,Credit Card' > 20150425.csv
  jq -r '.[] | [.name, .creditcard] | @csv' data.json >> 20150425.csv
This consider that all timestamps are from 2015/04/25 (which is the case with the example dataset, but might not be).

[1] https://stedolan.github.io/jq/

th0br0
You forgot the select(.creditcard) - see my comment on the gist - as this one does not filter out null'ed creditcards:

  name=`date +'%Y%m%d'`; 
  echo "name,creditcard" > $name; 
  jq -r '.[] | select(.creditcard) | [.name, .creditcard] | @csv' data.json >> $name
nburger
If we're going for succinctness, you could combine these all together:

(echo '"Name","Credit Card"'; jq -r '.[] | select(.creditcard) | [.name, .creditcard] | @csv' data.json) >> `date +%Y%m%d`.csv

ekimekim
I used jq as well, though instead of doing a seperate echo I did this:

    ([{name: "name", creditcard: "creditcard"}] + .)[]
instead of .[]

Another less hacky way would be:

    [["name", "creditcard"]] + map(select(.creditcard) | [.name, .creditcard]) | .[] | @csv
_cipher_
vim ftw. (there is an incorrect solution at link) :)

  Gddggdd
  :g/creditcard\":null/d   # to get rid of null credit cards :)
  gg
  qa
  9x
  f"
  d/creditcard
  c2f"
  ,
  ^[
  f"d$
  j^q
After that, just <num>@a and set.

edit: fix format and add gg after null deletion.

Spittie
On a whim, I'd probably do something like this

  tr -d '[{"}]' < data.json | awk -F ',' '{ print $1","$6 }' | awk -F '[:,]' '{ print $2";"$4 }' | grep -v ";null" > $(date +%Y%m%d).csv
mslot
Not very fast and lacks headers, but otherwise does the trick. Assumes date should be taken from timestamp, not sure if that's the case :)

    jq -r 'map(select(.creditcard)) | .[] | .timestamp + ";" + .name + "," + .creditcard' data.json | tr '\n' ';' | xargs -n 2 -d ';' sh -c 'echo $1 >> $(TZ=GMT date --date="$0" +"%Y%m%d").csv'
raverbashing
(Assuming data has already been downloaded)

cat data.json | grep -v 'null}' | sed -r 's/\"name\"\:\"([a-zA-Z.'\'' -]{2,50})\",.{80,200}\"creditcard\":\"([0-9-]{2,50})\"},?/\1,\2/' | tr -d '{}]['

(replace with sed -E on Mac OSX)

greggyb
No need to cat first. Command line utilities can act on files directly:

    grep -v 'null}' data.json | ....
raverbashing
Yes, it's just like this so you can take it and curl the url in place.
oevi
JavaScript in the browser console.

Assuming you are on this URL:

    https://gist.githubusercontent.com/jorin-vogel/7f19ce95a9a842956358/raw/e319340c2f6691f9cc8d8cc57ed532b5093e3619/data.json
you can do this (might take a few seconds):

    var csv=""; JSON.parse(document.querySelector('pre').textContent).forEach(function(item){if (item.name && item.creditcard){csv+=item.name+','+item.creditcard+'\n'}});
PedroSena
Single line, no assumptions about data previously stored, no invalid CSV with quotes getting the entire line and no useless spaces in the beginning of each line:

echo "name,creditcard$(wget -qO- https://gist.githubusercontent.com/jorin-vogel/7f19ce95a9a84... | jq -r 'map(select(.creditcard != null) | .name + "," + .creditcard)' | sed -E -e 's/\]|\"|\[| {2,}|,$//g')" > $(date +"%Y%m%d").csv

stonogo
UNIX was born for this.

    awk 'BEGIN { FS="," } /"creditcard":"/ { split ($5, a, " "); split (a[1], b, "\""); date=b[4]; gsub (/-/, "", date);  split ($1, a, "\""); name=a[4]; name=sprintf("\"%s\"", name); split ($6, a, "\""); card=a[4]; print "echo",name","card,">>",date".csv";  }' data.json | sh
steakejjs
The first way I did it was just read the file, parse the json, check for nulls, and print. Then I piped this to a file. That is too boring though.

The next way I did it was with Vim replaces. There might be sexier ways of doing this?

1) A big replace to get rid of all the junk.

    %s/\%Vemail.*creditcard/creditcard/g
2) A big replace to fix nulls in the credit card

    %s/\%V.*creditcard.*null.*//g
Then I could either change my code to not check for nulls or keep cleaning up this data until I have a CSV.

This Vim flow is the exact thing I do every week or two at work when building the Chrome HSTS preload list into our product. Anyone know the sexier ways to make this really really fun?

I should note to those not super familiar with vim that may try this, my vim commands are over a visual block (the json string), since this was in the same file as my code.

prakashk
Perl (assuming the data is downloaded):

    perl -0777 -MJSON -le '
        print for "name,creditcard\n",
                  map {"$_->{name},$_->{creditcard}\n"}
                  grep {$_->{creditcard}}
                  @{JSON->new->decode(<>)}
    ' data.json > 20150425.csv
jakejake
It's been years since I wrote perl. Any kind soul care to comment some of this code? It looks like the logic starts at the bottom, but I must admit defeat in truly understanding how it works!
a1369209993
decode input from JSON

grep for items with a 'creditcard' field

map each item to a string with its 'name' and 'creditcard' fields

for each string (and "name,creditcard\n"), print it

into 20150425.csv

hawkice
Perl is water in the cupped hands of our minds. I have yet to meet someone who can keep it from slipping between their fingers without consistent effort.
vram22
Innovative analogy.
prakashk
In addition to the explanation by a1369209993's explanation (https://news.ycombinator.com/item?id=9438730):

    * <> reads from file(s) specified as command-line arguments (data.json in this case)
    * Option -0777 tells Perl to slurp the whole file
    * Option -MJSON loads the JSON module
adpreese
I think it's usually better to do this with a reproducible script instead of manual editing in a text editor. There are too many times where the requirements change after the fact for "simple one-off" transformations.
zackmorris
I was going to say the same thing. I think of everything as black boxes, so choice of language is not as important as approach. I would use PHP because it's informal - it's a hybrid between shell and C so expressiveness and speed come basically for free and there isn't much friction. Being able to quickly iterate often makes relationships become apparent at a meta level. For example a quick lookup of a parsing error might reveal that the data was generated by a standard tool, so I could drop what I'm doing and grab that instead. It's not so much about individual choices, but a way of attacking problems that compounded over time leads to a great deal of leverage.
vladharbuz
A simple solution in Clojure:

  (require '[clj-http.client :as client]
           '[cheshire.core :as json])
  (let [url "https://gist.githubusercontent.com/jorin-vogel/7f19ce95a9a842956358/raw/e319340c2f6691f9cc8d8cc57ed532b5093e3619/data.json"
        body (:body (client/get url))
        data (json/parse-string body true)
        data-with-cc (remove #(nil? (:creditcard %)) data)
        rows (map #(str (:name %) "," (:creditcard %)) data-with-cc)
        csv (str "Name,Credit Card\n" (clojure.string/join "\n" rows))
        filename (str (.format (java.text.SimpleDateFormat. "YYYYMMDD") (java.util.Date.)) ".csv")]
    (spit filename csv))
lgas
This is a great solution. For fun I had a go at golfing it a bit:

  (-> "https://gist.githubusercontent.com/jorin-vogel/7f19ce95a9a842956358/raw/e319340c2f6691f9cc8d8cc57ed532b5093e3619/data.json"
      client/get :body
      (json/parse-string true)
      (->> (remove (comp nil? :creditcard))
           (map (comp (partial join ",") (juxt :name :creditcard)))
           (join "\n")
           (apply str "Name,Credit Card\n")
           (spit (-> (java.text.SimpleDateFormat. "YYYYMMDD")
                     (.format (java.util.Date.))
                     (str ".csv")))))
edit: Shorter, requires `(require '[clojure.string :refer [join]])`
peapicker
The one thing that was not left fully specified: Should the filename in YYYYMMDD.csv be the date that the program is run, or should a file of that spec be created grouping the lines in the .json file by their date field? If it is the latter, none of the github solutions are coding this correctly.
ICWiener
Common Lisp

     (ql:quickload :drakma)
     (ql:quickload :cl-json)
     (ql:quickload :local-time)

     (defparameter *data*
       (cl-json:decode-json-from-string
        (drakma:http-request 
         "https://gist.githubusercontent.com/jorin-vogel/7f19ce95a9a842956358/raw/e319340c2f6691f9cc8d8cc57ed532b5093e3619/data.json")))

     (with-open-file 
         (stream (make-pathname
                  :name (local-time:format-timestring
                         t
                         (local-time:now)
                         :format '((:year 4)(:month 2)(:day 2)))
                  :type "csv") 
          :direction :output
          :if-exists :supersede)
       (loop initially (format stream "Name,Credit Card~%")
             for slot in *data*
             for name = (cdr(assoc :name slot))
             for card = (cdr(assoc :creditcard slot))
             when (and name card)
               do (format stream "~a,~a~%" name card)))
greggyb
Simple pipeline prettied up a little.

    curl https://gist.githubusercontent.com/jorin-vogel/7f19ce95a9a842956358/raw/e319340c2f6691f9cc8d8cc57ed532b5093e3619/data.json |
        cut -d, -f1,6 |
        cut -d\" -f4,8 |
        awk -F\" -e 'BEGIN {print "name,creditcard"};
            {if (NF==2) print $1 "," $2}' \
        > 20150425.csv
ProfDreamer 

    echo 'name,creditcard' > `date '+%Y%m%d'`.csv
    grep 'name"' data.json | \
        grep -v 'creditcard":null' | \
        tr '{:}' ',' | \
        cut -d',' -f3,20 | \
        sed 's/"//g' \
            >> `date '+%Y%m%d'`.csv
phillc73
The R example posted on the GitHub thread is very nice. [1]

I use R every day, often parsing json and writing csv files. The solution posted by mrdwab is much more succinct than my go to method, especially the use of pipes. I've read about pipes in the dplyr/magrittr packages before, but this is the first time I've seen them used and thought they made complete sense.

[1] https://gist.github.com/jorin-vogel/2e43ffa981a97bc17259#com...

Gatsky
I agree, the solution in R seems by far the most simple and intelligible. But then R is specifically designed to do this kind of thing, so that's not too surprising.
pravj
A lot of people have already solved this but here is my 2 cents.

    wget https://gist.githubusercontent.com/jorin-vogel/7f19ce95a9a842956358/raw/e319340c2f6691f9cc8d8cc57ed532b5093e3619/data.json
    echo "Name,Credit Card" > `date +"%Y%m%d.csv"`
    jq -r '.[] | select(.creditcard) | .name+","+.creditcard' data.json >> !$
bfstein
Copying my answer from the website, but this can be done by extremely nontechnical people:

1) Copy all of the JSON data.

2) Paste it into this link: http://konklone.io/json/ (I don't know if this is cheating)

3) Download the file.

3a) If it opens in a new page, just manually save the source as a .csv

4) Open it in excel.

5) Click the column of credit cards.

6) Click the button to sort in descending order.

7) Scroll down to the point where you run out of credit cards.

8) Delete everything after that.

maxerickson
The goal of posting the challenge is to see tools like the one you link.

Your solution is probably more sensible as a one off than a lot of the other answers.

traviscj
If this was a list of cat pictures, I would not consider posting to a third party cheating. But seeing as how it's credit card data, even though it is already "in the wild," it is still worth not posting to another random website.
gwu78
Considering these are credit card numbers and you are trying to protect the account holders, is it really a smart idea to upload the names and numbers to some third party website?
whoopdedo
Lowest effort solution: Post to stackexchange/github/reddit with the question "I've got a programming challenge. Can you find the shortest code to convert this JSON to CSV?" Then sit back and watch the answers roll in.
bch
Or go to say, a #json channel in IRC and say that the problem can't be solved.
HaseebR7
FTFY

sit back and watch the answers and downvotes roll in.