5 minutes to import CSV to a database: What do you do?

I challenged my twitter peeps:

Challenge: you have a csv file and you need to import it into a new table in a database. You have 5 mins to get it done. What tools do you use?

@secretgeek

and was overwhelmed with the responses! So many different ways people approach the problem, and so many different tools.

It reminds me of Miles' Law:

Where you stand on an issue depends on where you sit.

  • If you sit in python all day, you'll write a python script.
  • If you sit in powershell all day, you'll use powershell
  • If you sit in SQL Server all day, you might use T-SQL itself to do the work.
  • If you sit in an editor all day, you might use search/replace to transform the data into sql statements.

I put all the solutions I saw into my "today I learneds" site, here's a table of contents for the topic:

In related CSV-news I should mention that I made a git repo: "Awesome CSV" where I collect all the coolest CSV tools, articles and specs from around the planet. It's a happening place for cool people to hang out and learn cool things.

And my own modest tool NimbleText treats everything as delimited text, lets you do powerful things quickly, and is arguably* the greatest invention in the history of the universe.

I also have an Awesome GUID repo, where I store awesome things related to GUIDs. There are surprisingly few things there. Hmmm.


* Whenever someone says 'arguably' it is safe to replace it with 'not'. This is arguably true in every circumstance.

 

Desert Skies - exclusive inside information from the developers

Unless you've been living under a rock like some disgusting sand-sneaking Seeker -- you're aware of the awesome sensation taking the gaming community by storm: Desert Skies.

Here's a video of the game play in case you're not familiar.

I recently sat down for an intense lunch with one of the minds from White Rabbit Games - the creative duo behind this Indy gaming sensation, and I found out some exclusive information.

Although the company is called "White Rabbit Games" the logo is of a cat.

white rabbit games logo... a white cat

SEE!?

What's that all about?

T: we have a fluffy white cat, and it looks like a bunny so we call it "bunny".

Thus the white rabbit is actually a cat.

Woah.

That's enough revelations for one day.

It's a really awesome game. I find it pretty exciting because it's a simple idea that is popular enough that it will get a chance to expand in some really interesting directions. Keep an eye on this!

 

What is NimbleText (a story about cheese)

A good friend asked me the other day, "Wait a second, what's nimble text?" and I stammered out an answer. I'm not good at elevator pitches. Waving my arms around didn't make it any clearer. I tried waving them in bigger circles and pointing as I talked. It still didn't help.

I decided to email him an answer. It turns out I don't have his email address, so I'm writing it here, and I'll transmit the URL some other way. You get to read my reply in public.

What then, is NimbleText?

NimbleText is an adhoc code generator.

For example, say you have a list of "keywords", (he's a digital advertising specialist... it takes all kinds) and you want to do something with each keyword, maybe insert it into a database.

Here's your list of keywords (these are French cheeses)

Abondance
Banon
Beaufort
Bleu
Brie
Brillat-Savarin
Brocciu Cara or Brocciu
Cabécou
Cancoillotte
Camembert de Normandie
Chabichou du Poitou
Chaource
Chevrotin
Comté

...you paste that into NimbleText, and type in a pattern... how about this:

Insert into Keywords(Keyword) Values ('$0')

NimbleText will *magically* create this SQL query for you:

Insert into Keywords(Keyword) Values ('Abondance')
Insert into Keywords(Keyword) Values ('Banon')
Insert into Keywords(Keyword) Values ('Beaufort')
Insert into Keywords(Keyword) Values ('Bleu')
Insert into Keywords(Keyword) Values ('Brie')
Insert into Keywords(Keyword) Values ('Brillat-Savarin')
Insert into Keywords(Keyword) Values ('Brocciu Cara or Brocciu')
Insert into Keywords(Keyword) Values ('Cabécou')
Insert into Keywords(Keyword) Values ('Cancoillotte')
Insert into Keywords(Keyword) Values ('Camembert de Normandie')
Insert into Keywords(Keyword) Values ('Chabichou du Poitou')
Insert into Keywords(Keyword) Values ('Chaource')
Insert into Keywords(Keyword) Values ('Chevrotin')
Insert into Keywords(Keyword) Values ('Comté')

Two seconds later, you run the SQL and insert all the keywords into the database.

You can play with that example here: https://nimbletext.com/Live/398054487/

If the input had more than one column, you would refer to the next column as $1, then $2, etc.

So if the keyword data included, I don't know... some search traffic numbers?...

Abondance,1000
Banon,3000
Beaufort,200
Bleu,12
Brie,6
Brillat-Savarin,90

We could use a pattern like this:

Insert into KeywordResult (Keyword,Result) Values ('$0', $1)

To generate this result:

Insert into KeywordResult (Keyword,Result) Values ('Abondance', 1000)
Insert into KeywordResult (Keyword,Result) Values ('Banon', 3000)
Insert into KeywordResult (Keyword,Result) Values ('Beaufort', 200)
Insert into KeywordResult (Keyword,Result) Values ('Bleu', 12)
Insert into KeywordResult (Keyword,Result) Values ('Brie', 6)
Insert into KeywordResult (Keyword,Result) Values ('Brillat-Savarin', 90)

(See this example here: https://nimbletext.com/Live/284342338/)

But it's not just for generating SQL queries, it's for text in general; and it's not just for keywords and it's not even specific to cheeses. There are many non-cheese uses.

It helps you anytime you want to rearrange some data and do something to every row, to generate a result.

There's an online version, and a downloadable version (with extra features). You can embed javascript inside your patterns to do really tricky formatting stuff. It's versatile.

It's just this thing, you know. For stuff.

 

Secrets of Mastering Excel

Inspired by a comment thread at hacker news I made this mini site:

excel.secretgeek.net

I often want to share Joel Spolsky's famous "You Suck at Excel" video tutorial with "important" business people inside the large enterprise where I spend a lot of time.

But it would be easily misconstrued as trolling if I sent a business customer a URL that literally tells them, right in the heading, that they "suck" at excel.

So, as advised by user TuringTest (in the thread mentioned above) I created a mini site, on its own sub-domain, that is palatable to a business mindset, and brutally overlaid a heading that says "Secrets of Mastering Excel" right over the top of the "You Suck at Excel" heading.

Now I've got something I can recommend to business people without causing disharmony. And I can leave a link to it in my corporate email signature, to nudge others to improve their Excel game.

Ideally the page would detect when the video starts and remove the label, so it doesn't obscure the video. Also, I'd like to write up a set of notes about the talk, but ain't nobody got time for that, so I've linked to the notes from Max Masnick. Perfect is the mortal enemy of done and they are locked in an ultimate death embrace and that is how storm clouds create lightning but I digress.

The whole thing from idea to delivery was about an hour. I was really pleased with the speed on this one.

 

The Market for Ideas (and is contenteditable really terrible?)

Some people invest in gold, others bitcoin. I put my money on ideas. The volatility can be wild.

Take this idea -- the idea that "Contenteditable" attribute is a terrible basis for a Wysiwyg editor, as expressed by this article from medium engineering.

5 years ago, this idea was only worth 3 points and 1 comment.

(I bought all I could! I liquidated all my other ideas so I could go long on this one....)

One year later -- it's worth had grown to 75 points and 9 comments:

(I was tempted to cash out... but some stubborn streak... I held my nerve)

It hit its peak 3 years ago when its worth grew to 155 points and 76 comments

(I sold it all, by god, I cashed it all in, right at the peak! It was glorious!)

Soon after: the idea came crashing down. A year ago it was old news, back down to 3 points and 0 comments.

I guess what I'm saying is... sometimes an idea is before it's time. Not enough people understand the fundamentals to be able to engage with the idea. Then the audience has caught up, and they understand the idea. And then, having grokked it, the idea doesn't captivate them any longer.

A different though related thought is "It's better to be wrong than early" as described at hackernoon, a sentiment that occurs a few times in Ray Dalio's Principles.

contenteditable

I don't really invest in ideas. Not explicitly. (At a deep level, just about everything we do is an investment in an idea.)

p.s. I built something with contenteditable this week... I agree it's terrible. A page that lets you edit its style, without using javascript

 

a html quine

html,

Yesterday I was on a bus! Yes, a bus! I don't get to ride buses so often due to the working from home lyfe, but I took the opportunity to write some simple code (as I used to do on buses in the past... i even gave a talk about it once, when alt.net was a thing.)

And the thing I coded on the bus was a html quine.

Go and see this html quine

It's... different?

html quine.jpg

A quine is a program that produces, as output, its own source code. I've discussed and demonstrated quines over here in the wiki.

And as I say in the html quine link, although the idea had bounced around in my head for a decade, the thing that reminded me to go and do it was seeing this piece of "Code as Art" from Geoff Huntley recently: no yaml. There must be more Code as Art in this world.


See also, lengthy Hacker News discussion

 

auto play

Even though I'm polite and I click yes to let all the websites keep their cookies on my machine, for their own devilish surveillance purposes, even though I say yes and there are no doubt a million cookies filled with details about me, knowing everything i like and what i click on, and invading every ounce of my privacy, it seems that they can't seem to find the one cookie that matters, they keep losing that one cookie i care about and it's the cookie that says: "Don't autoplay the fucking videos on youtube." Just don't do it. He's told us, over and over, on every device, not to autoplay those videos, just hold onto this one simple fact, this tiny binary digit of truth, and do not autoplay those videos. But that's the one cookie they cannot retain, they cannot keep hold of, it evades their grasp, it leaps out of their fingers like an oily fish cookie, a digusting cookie flavour that is not very popular when A/B tested in regular stores, it is the slipperiest cookie and i click once more to say "again, i tell you, please i beg you, do not autoplay your videos" and once more I know they will forget this fact as they have a specific form of memory loss, a Korsakoff syndrome all their own, where all private thoughts of an individual are retained indefinitely and sold to the highest bidder but one particular bit of data is unlike any other and is somehow coated in polytetrafluoroethene and cannot be persisted in any form, it is like those burn after reading messages that will self destruct as soon as they are read and so i am stuck endlessly watching one autoplayed video after another down stranger and stranger wormholes down rabbit rabbit holes in weird enclaves of digital degeneration as i see things unthinkable things things i cannot unsee and always i squirm and wish to escape but it cannot be done i must keep watching them play and play the playing must be played they cannot pause their playing, the playing must play, like clockwork orange i am bound before the screen with eyeballs clamped open by spidery metal fingers and please keep your cookie no more autoplay for me i cannot resist.

 

 

TimeSnapper for Mac: Beta Testers Needed

TimeSnapper Logo Finally some exciting news on the TimeSnapper front.

For a long time, the most requested feature for TimeSnapper has been a whole new version, for the Mac OS. Many of our customers and friends have moved from Windows to Mac over the last decade, and consistently told us that they miss the peace of mind and assistance that TimeSnapper provides.

One such customer, Jon Schneider was so tired of the situation that he built a new version of TimeSnapper, from the ground up, for the Mac OS. He is now part of the TimeSnapper team (along with Atli and I) and we're now ready -- with your help -- to welcome beta testers: people who are willing to run this software and give us feedback, suggestions and error reports about their experience.

A quick recap: TimeSnapper is a piece of software you can run in the background of your computer. It takes screenshots of your desktop every few seconds all week long. You can use it to recover text you've lost when (for example) your browser crashes, or you can use it to help fill out a timesheet, or just to gain an objective answer to the question "Where did my time go?" It's clever about the way it uses resources, so it doesn't fill up your hard drive or slow your computer.

Huge thanks to you for considering coming along on this journey, and a biiiiiiiiiig thank you to Jon for stepping up and getting us here.

ts mac options

ts mac playback

 

React is NOT ok

Look how tragic this is....

You create your new react app with:

npx create-react-app my-app

And it spits out 907 lines of techno-babblicious output, scrolling scrolling scrolling in your console, ending at last with exactly one useful shard of information:

Success! Created my-app at C:\example\my-app
Inside that directory, you can run several commands:

	yarn start
		Starts the development server.

	yarn build
		Bundles the app into static files for production.

	yarn test
		Starts the test runner.

	yarn eject
		Removes this tool and copies build dependencies, configuration files
		and scripts into the app directory. If you do this, you can’t go back!

We suggest that you begin by typing:

	cd my-app
	yarn start

Happy hacking!

But after the wall of gobbledygook that preceded it, it's easily overlooked.

If the React team knew about the wonders of "ok" (for bash or powershell) they'd simply create a ".ok" file inside the "my-app" folder that says:

yarn start #   Starts the development server.
yarn build #   Bundles the app into static files for production.
yarn test  #   Starts the test runner.

...Then you'd immediately be able to find the commands again, and run them with a simple "ok 1", or "ok 2" or "ok 3"

But instead: those commands, sailing by in the infinite neon-scroll of the console, will be lost in time like tears in rain.

blade runner ending, tears in rain

 

Using "ok" as a Project Control Room

Here's an example of the scrappy, useful sort of way I use an ok folder profile to help me get things done.

I'm co-writing a book with Joseph Cooney. When I want to work on the book I navigate into the correct folder, using this command:

j ev

j is an alias for jump which is part of my markjump.ps1 system for navigating around. "ev" is the first two letters of the bookmark i've got for this location, so markjump "jumps" me into the ~\evergreenskillsdotcom folder.

Inside that folder, if I type "ok" I see a list of all the actions that are easy to run.

ok evergreen

As shown in the screenshot the options I get are:

  1. clowncar # use clowncar to generate static html
  2. .\build_and_push.ps1
  3. browse "https://docs.google.com/document/d/************************/edit" # view DA BOOOK
  4. browse "https://docs.google.com/spreadsheets/d/************************/edit" # subscribers
  5. j journal; code .\nontechnicals.md; popd; # work on non-technicals
  6. browse "https://webmail.************************" # ****** inbox leon@evergreenskills.com
  7. browse "https://trello.com/b/********/evergreen" # Joseph's Trello for EV
  8. browse "https://www.reddit.com/r/EvergreenSkills/" # REDDIT for ev

This rag-tag bunch of commands acts as a kind of "control room" for all the different work-fronts of the book.

control center

I can re-generate the website by calling ok 1 (this uses a static html generator i wrote in powershell called clowncar, which uses pre, markdown and yaml)

I can rebuild and push the site live with ok 2

I can continue to edit or write the book itself with ok 3

I can see the list of people who have subscribed to be notified at ok 4

There's a bunch of research notes about non-technical skills I can edit at ok 5

I can send or receive email from the evergreenskills domain with ok 6

Joseph has a trello board where we share a lot of notes and ideas at ok 7

And there's a reddit site where we've been sharing links, at ok 8

(I've hidden a few guids, to try and slow the doomsday clock on the guid apocalypse)

Even a simple project like this one has an evolving set of different urls and commands and activities. Maintaining a little .ok file is the simplest way I've got to keep track of them.

Unlike a documentation site it contains "executable" commands. And since I use powershell for all navigation and automatically run ok everytime I enter a folder, it's always in front of my eyes, and doesn't "rot" the way documentation does.

(Image is from Pilotpriest ‎– Original Motion Picture Soundtrack... I've always loved that image since I saw it on archillect)