At most one email a week. Unfollow anytime with one click.
Transcript
The full narration of this video. The current sentence is highlighted while the video plays; click any sentence to jump there.
Show transcript
Introduction
Hi, and welcome back. In the last two episodes we processed office Excel files. This time we move to a small business and a very common type of file: CSV. We'll combine Sunset Coffee's order data from three different sources into one clean table, while keeping customers' personal data safe.
Step 1: Getting to know CSV and its problems
Before we start, let's get to know CSV. It stands for comma separated values. The content is an ordinary table, but stored as simple text. One row means one record, such as one order. Columns are separated by commas or semicolons. Because it's so simple, almost every app can export CSV, from marketplaces to Google Forms. Laras, the owner of Sunset Coffee, receives orders from three places: a marketplace, an order form, and WhatsApp, logged by her admin, Dimas. Even the separators differ; Dimas's notes use semicolons because that Excel is set to Indonesian. Dates come in three styles: year first, day first, and a short month name. If they're mixed as they are, Excel can misread October third as March tenth. Product names are the messiest. The one-liter palm sugar iced coffee is written neatly on the marketplace, freely by customers on the form, and abbreviated as PSC by Dimas. Prices are written three ways too: a plain number, with Rp and separators, and with k for thousand. Finally, phone numbers. The marketplace already masks them, but the form and the WhatsApp notes keep full numbers. This is personal data we must protect.
Step 2: Claude reads and builds a name dictionary
Laras gets help from Raka, the student who built the Sunset Coffee website and is used to Claude Code. Raka types the request: read all three files and the official menu, change nothing, then report the differences. Claude reads the marketplace file: two hundred twelve orders separated by commas. The form file has one hundred forty-six orders. Dimas's WhatsApp notes have ninety-seven orders, and Claude recognizes the semicolon separator right away. The most interesting finding: there are thirty-one different ways of writing four products. Claude builds a product name dictionary based on the official menu, then asks Laras to check it, especially the abbreviations. Here's the dictionary. The customer spelling palm coffee one liter is mapped to the standard name, palm sugar iced coffee one liter. It was found thirty-eight times. Dimas's abbreviation, PSC one L, maps to the same product. Claude inferred that from the price, which is always eighty-five thousand. Cold brew is easy, because the menu has only one size. Then there's small hamper, which isn't on the official menu. Claude honestly marks it as not sure. Laras explains that it's an old hamper, now replaced by the gift hamper at one hundred fifty thousand. Finally, PSC cup means the palm sugar iced coffee cup, two hundred fifty milliliters. The dictionary is saved, so next month the same abbreviations are recognized right away.
Step 3: Merging and flagging duplicate orders
Laras has corrected the dictionary. Raka asks for the three files to be merged with a uniform format: day-first dates, prices as numbers, standard product names, and a Source column showing where each row came from. Raka adds one important rule. Orders that may have been recorded twice must not be deleted, only flagged. Deleting automatically risks throwing away a real order. Claude writes a merge script of about eighty-six lines. The permission box explains what will happen: read three files and write one new file; the originals are not changed. Raka reads it, then approves. Four hundred fifty-five rows are merged, and seven pairs might be duplicates. Claude explains the pattern: the name, date, product, and quantity are the same, but the sources differ. Customers probably filled in the form and then confirmed again on WhatsApp, and Dimas logged it too. Here's the merged result. Rina's marketplace order now uses a uniform date and product name. Budi's order from the form, three bottles of cold brew, is flagged as a possible duplicate. That's because on the same day there's an order from Budi on WhatsApp with exactly the same content. Laras checks with Dimas: yes, it's the same order. One row is deleted by hand. Sari's WhatsApp order, six cups, already uses the standard name even though Dimas wrote PSC cup. And Andi's hamper order from the form. Of the seven flagged pairs, two turned out to be different orders that just happened to look alike. Good thing they weren't deleted automatically.
Step 4: Matching prices and protecting personal data
One more check: are the prices right? Raka asks Claude to match the price of every order against the menu and only report the differences. Four hundred forty-five orders match the catalog price, and five differ. Claude doesn't assume they're wrong. It sees a pattern: all of them are eighty thousand for the one-liter coffee, from October tenth to twelfth. It asks whether there was a promo. Laras remembers: that was the Sunset Coffee anniversary promo. Then Laras adds an important request: mask customers' phone numbers and keep only the last four digits. Look at the diff. The full phone number column is removed from the output columns, replaced by a Phone column that holds only four asterisks and the last four digits. This table is safe to share for analysis. The script runs again. Phone numbers are masked, and the five promo orders are now clearly labeled. From this experience, Laras sets four customer data rules. First, analysis tables only need first names and the last four digits of phone numbers. Second, files with full numbers stay on the business computer and aren't shared. Third, ask Claude to read only the columns the job actually needs. The content of files Claude reads gets processed by Claude, so the less personal data, the better. Fourth, never upload customer number lists to groups or random services. Customer trust is worth far more than the time you save.
Step 5: The result and applying it yourself
Let's look at the final result. Four hundred fifty-five rows from three sources. After Laras deleted five duplicate orders by hand, four hundred fifty unique orders remain. Now Laras can answer a question that used to be hard: forty-seven percent of orders come from the marketplace. This helps Laras decide where to run the next promo. And there isn't a single full phone number in the analysis file. Tidy data, and customers stay protected. You can use this pattern with lots of data. Mr. Hendra at Cerdas Tutoring could merge sign-ups from the online form with in-person sign-ups at the office. Nadia, a social media freelancer, could standardize client contact lists from several exports. And a business owner could match bank statement exports with the order list to see which orders are paid. For financial data, use a copy and never put your bank password in a script. The screens in this video are simulations, so your Claude Code may look slightly different.
Done!
We combined three CSV sources into one clean order table: uniform formats, a product name dictionary, flagged duplicates, matched prices, and masked customer phone numbers. In the next episode, we make these scripts run on their own on a schedule, for example every Monday morning, without typing the command ourselves. See you there!
Practice exercises
Try these on your own after watching to lock in what you learned.
Ask Claude Code to compare the formats of two of your CSV files and explain the differences
Build a dictionary of standard names for products or categories that are often written in different ways
Ask for a merge script that flags duplicates without deleting them, then check the result
Ask the class material
Type a question or keyword; we find the part of this episode that explains it and jump the video there.
My notes
Notes are saved with the current video time and are only visible to you.
Questions & answers
Stuck on a step? Ask here; the instructor and other learners can answer.
No questions yet.
Your instructor
Cerdas Inovasi Digital Team
Software developers, BIM & 3D engineers, and AI practitioners
53 video classes1,278 episodes
We build software, BIM models, games and AI automations for clients every day, and turn that hands-on work into step-by-step classes: every step is shown on screen and comes with a PDF guide you can keep.
