Goal
Explore Spotify data. I’d like to:
- View stats about my listening history
- View my listening history
- Would be nice to be able to search
- List the podcasts I listen to
- Ideally I’d like an RSS feed of updates to my Podcast feed. Things like “followed new podcast xxx on x date”
Design
- Put the exported files into PostgreSQL so it’s easy to query and slice and dice to display
Issues
My beloved#datagrip wasn’t working importing a CSV to#neonpostgres with the error:
I’m not sure why… so I had to go the psql
CLI route 🤷♂️
TODO cat4egforize the below
Get list of podcasts from spotify and migrate to other app (kicked off export 2024-01-06 2:52pm) (dataframed addition) (downloaded spotify data on 1-9-24)
I only exported a years worth of data.
I’d like to be able to search my listen history.
Primary Goal Get a list of all the podcasts I follow, curate, add to AntennaPod app. Expose a OPML download. Expose a markdown list.
To Do
Generate Podcast List
The file we want is YourLibrary.json
Install dependencies:
npm install -g @json2csv/cli
Get it in a CSV format to load:
cat YourLibrary.json | jq -r '.shows' > your-library-just-shows-v1.json
json2csv -i your-library-just-shows-v1.json -o your-library-just-shows-v1.csv
Load it:
psql YOUR_CONNECTION_STRING
create table spotify_podcasts (
name text,
publisher text,
uri text
);
\copy spotify_podcasts FROM '/home/lane/Desktop/Spotify Account Data/your-library-just-shows-v1.csv' DELIMITER ',' CSV HEADER
Random
# Run a random inline sql script
psql -c "select * from spotify_streaming_history" YOUR_CONNECTION_STRING
# Run a random sql script
psql -f ./test-script.sql YOUR_CONNECTION_STRING
To Dos
- Create stats and show in nextjs ✅ 2024-01-13
Create opml page manually or with library(I don’t know the RSS url so I’m not going to do this)- Generate URL to spotify like so:
https://open.spotify.com/show/2GFqbdH3xEtu0ZKUP6tr13
✅ 2024-01-13 - export from the open podcast app to see the format for opml (it was simple, nothing too crazy) ✅ 2024-01-14
- need flyway or something for this schema (this will come some day)
Brain Dump
- Import | DataGrip Documentation
- Import | DataGrip Documentation
- Neon data import guides - Neon Docs
- Import data from CSV - Neon Docs
- milliseconds to hours - Google Search
- expand nested array into rows? · Issue #139 · zemirco/json2csv
- @json2csv/cli - npm
- How to Convert from JSON to CSV at The Command Line - Earthly Blog