Script Filter: Need Help with csv to json convertion

[drompono]

Hey there!

 

Im using the fuzzylist workflow template to display results from an .csv file, this here is the fuzzylist.py:

 

#!/usr/bin/python

from __future__ import print_function

theFile="test.csv"
fieldnames=["title","arg","subtitle"]

json_filename = theFile.split(".")[0]+".json"

import csv
import sys
import json
import os

def convert(csv_filename, json_filename, fieldnames):
    f=open(csv_filename, 'r')

    csv_reader = csv.DictReader(f,fieldnames, restkey=None, restval=None)

    jsonf = open(json_filename,'w')
    jsonf.write('{"items":[')

    data=""

    for r in csv_reader:

        r['uid']=r['arg']
        data = data+json.dumps(r)+",\n"

    jsonf.write(data[:-2])

    jsonf.write(']}')
    f.close()
    jsonf.close()

if (not os.path.isfile(json_filename)) or (os.path.getmtime(theFile) > os.path.getmtime(json_filename)) :
    convert(theFile, json_filename, fieldnames)

with open(json_filename, 'r') as fin:
    print(fin.read(), end="")

 

it works fine if I have only three rows in the csv file, but what I want is to write the json file like this:

 

{"items": [
    {
        "title": row[1] + ' ' + row[2],
        "subtitle": row[4]+row[5]+ row[6]
        }
    }
]}

 

For example if I have 6 rows in my csv file, I want to display row 1 and row2 together in the search result title.

 

Im sure it belongs somewhere at 

data = data+json.dumps(r)+",\n"

but Im not good in Python programming, so I dont know how to add this lines. Can somebody help?

[deanishe]

2 hours ago, drompono said:

but Im not good in Python programming, so I dont know how to add this lines. Can somebody help?

 

Don’t try to generate JSON yourself. Create regular Python list and dict objects, then convert them to JSON with json.dump().

 

I haven't tested this code because you didn't provide any data. In future, upload your workflow somewhere (Dropbox?) and post a link. Then we can give you an answer that's definitely correct instead of guessing.

 

#!/usr/bin/python

import json
import os

infile = 'text.csv'
outfile = os.path.splitext(infile)[0] + '.json'
fieldnames = ['title', 'arg', 'subtitle']


def convert(csv_file, json_file, fieldnames):
    items = []
    # read CSV file
    with open(csv_file) as fp:
        reader = csv.DictReader(fp, fieldnames)
        for row in reader:
            row['uid'] = row['arg']
            items.append(row)

    # generate Alfred JSON
    with open(json_file, 'wb') as fp:
        json.dump({'items': items}, fp)


if not os.path.exists(outfile) or os.path.getmtime(infile) > os.path.getmtime(outfile):
    convert(infile, outfile, fieldnames)

 

[drompono]

Thanks nfor your quick reply!

 

Ah ok, sorry here the link to my actual workflow:

 

https://dsc.cloud/93b597/Search-Shopify-CSV

 

the included .csv file has 14 rows - row with the fieldname "shipping_address.name" and "total_price" should appear in the alfred json title, 

"shipping_address.city" and "shipping_address.country" should appear in the subtitle - But If I do a search from Alfred all rows should be included, so if Im searching for an email address, the email row should be searched  - is this possible?

 

So should I just add to

the for row in reader

 

 

row['title'] = row['shipping_address.name'] + row['total_price']

or something like this?

 

 

[deanishe]

Here’s a working version of the workflow.

 

The way you were going about it was backwards: you were trying to find Alfred fields in the CSV data. You were also using an outdated version of the fuzzy.py script.

 

51 minutes ago, drompono said:

But If I do a search from Alfred all rows should be included, so if Im searching for an email address, the email row should be searched  - is this possible?

 

What do you mean? All fields should be searched? If so, yes, it’s possible. But it’s not a good idea, at least not using fuzzy.py. Like Alfred, it only filters against one field (match if present, else title), so you have to add all fields you want to filter against into match. But the more fields you add, the worse the results you get from fuzzy filtering.

 

You might get better results letting Alfred do the filtering (it uses word-starts-with matching).

[drompono]

yes thats it! Thank you very much, thats exactly what I was looking for!

Ok I will check what will be better for filterung the results

 

One last question, I replaced the dummy csv with my actual csv list, which is much more lager. I deleted the .json file and triggered the workflow, but then I get this error here:

 

[17:26:11.560] ERROR: Search Shopify CSV[Script Filter] Code 1: [fuzzy] .
[fuzzy] cmd=['/usr/bin/python', 'fuzzylist.py'], query='', session_id=None
[fuzzy] running command ['/usr/bin/python', 'fuzzylist.py'] ...
Traceback (most recent call last):
  File "fuzzylist.py", line 51, in <module>
    convert(INFILE, OUTFILE)
  File "fuzzylist.py", line 37, in convert
    'title': ITEM_TITLE.format(**row),
KeyError: 'shipping_address_name'
Traceback (most recent call last):
  File "./fuzzy.py", line 380, in <module>
    main()
  File "./fuzzy.py", line 367, in main
    fb = cache.load()
  File "./fuzzy.py", line 301, in load
    js = check_output(self.cmd)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/subprocess.py", line 223, in check_output
    raise CalledProcessError(retcode, cmd, output=output)
subprocess.CalledProcessError: Command '['/usr/bin/python', 'fuzzylist.py']' returned non-zero exit status 1

 

 

do you know what could be the reason? some fields are empty or have "(", ä, or other special signs included - could that be the cause?

[deanishe]

2 minutes ago, drompono said:

do you know what could be the reason? some fields are empty or have "(", ä, or other special signs included - could that be the cause?

 

Like the error message says, there's no "shipping_address.name" field in the CSV (i.e. it's not the same format as the one you put in the workflow). Non-ASCII characters like ä shouldn't be a problem as long as the CSV is UTF-8 encoded: your original script doesn't look like it will handle them, but my version will.

 

Thanks for the beer money!

[drompono]

ah ok seems the edit in Numbers App changed the seperators from

,

to

;

 

so I changed 

for row in csv.DictReader(fp, delimiter=';'):

 

but now I get this error:

 

[18:02:26.322] ERROR: Search Shopify CSV[Script Filter] Code 1: [fuzzy] .

[fuzzy] cmd=['/usr/bin/python', 'fuzzylist.py'], query='', session_id=None

[fuzzy] running command ['/usr/bin/python', 'fuzzylist.py'] ...

Traceback (most recent call last):

  File "fuzzylist.py", line 51, in <module>

    convert(INFILE, OUTFILE)

  File "fuzzylist.py", line 37, in convert

    'title': ITEM_TITLE.format(**row),

UnicodeEncodeError: 'ascii' codec can't encode character u'\u2019' in position 8: ordinal not in range(128)

Traceback (most recent call last):

  File "./fuzzy.py", line 380, in <module>

    main()

  File "./fuzzy.py", line 367, in main

    fb = cache.load()

  File "./fuzzy.py", line 301, in load

    js = check_output(self.cmd)

  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/subprocess.py", line 223, in check_output

    raise CalledProcessError(retcode, cmd, output=output)

subprocess.CalledProcessError: Command '['/usr/bin/python', 'fuzzylist.py']' returned non-zero exit status 1

 

Do I have to add .decode ascii somewhere?

Sorry for all the questions

[deanishe]

I don't know. Like I said earlier, I need a copy of the workflow and data that aren't working, or I'm just guessing. Which is a waste of everybody's time.

 

You can email it to me at deanishe@deanishe.net, or create some fake data that cause the same error.

 

EDIT: You can try this updated version, but I don’t know if it will fix all the issues because I don’t have real data to test it on.

 

Edited September 7, 2020 by deanishe