←back to thread

Ask HN: How are you cleaning and transforming data before imports/uploads?

36 points dataflowmapper | 2 comments | 14 May 25 16:27 UTC | HN request time: 0.645s | source

Hi all,

I’m curious how folks handle the prep work for data imports/uploads into systems like Salesforce, Workday, NetSuite, or really any app that uses template based import for data loading, migration, or implementation.

Specifically: - How do you manage conversions/transformations like formatting dates, getting everything aligned with the templates, mapping old codes to new ones, etc.

- Are you primarily using Excel, custom scripts, Power Query or something else?

- What are the most tedious/painful parts of this process and what have you found that works?

Really appreciate any insights and am curious to learn from everyone's experience.

Show context
PaulHoule ◴[14 May 25 16:34 UTC] No.43986506[source]▶
"Scripts" in Python, Java and other conventional programming languages (e.g. whatever it is you already use)

Not Bash, not Excel, not any special-purpose tool because the motto of those is "you can't get there from here". Maybe you can get 80% of the way there, which is really seductive, but that last 20% is like going to the moon. Specifically, real programming languages have the tools to format dates correctly with a few lines of code you can wrap into a function, fake programming languages don't. Mapping codes is straightforward, etc.

replies(3): >>43986637 #>>43987179 #>>43987624 #
1. dlachausse ◴[14 May 25 18:19 UTC] No.43987624[source]▶
It’s not sexy, but Perl is the purpose built tool for this job.
replies(1): >>43988222 #
2. PaulHoule ◴[14 May 25 19:18 UTC] No.43988222[source]▶
Got a lot of bustedness though. I was writing cgi-bin scripts for Perl and I remember the urldecode in Perl being busted fundamentally, like it would rewrite '%20'-> SP but not rewrite '+' -> SP so I had to write my own urldecode.

My impression is that people use Python to do most of the things that we used to do with Perl.

Circa 2001 I wrote a cookie-based authentication system for the web which had an "authentication module" of roughly 100 lines that I wound up rewriting in at least ten different languages such as PHP, Java, Cold Fusion, Tango, etc. The OO Perl version was the shortest and my favorite.