File Types: Excel vs CSV

-


They open in the same app. That’s where the similarities end.


As someone who works with large amounts of data (and has made a lot of mistakes), I'm writing this for anyone still fuzzy on the fundamental differences between Excel (.xlsx) and Comma-Separated Values (.csv) files.


If you’re expected to automate data flows, please don’t ignore this one. This post is for you!

On the surface, these two look like identical twins:

Tomayto.xlsx
Tomahto.csv

But if you look closely, you’ll notice the first difference.


If you noticed that Tomayto.xlsx is 7× bigger in Size than Tomahto.csv —you’re right!

And here’s the key detail: both files are EMPTY.

So, what's going on? They both look the same to us, but the computer sees it differently. 



What the Computer Really Sees?


A CSV file is basically: 
name,age,city\nKairon,17,Mars.

An Excel file, on the other hand, is a ZIP archive containing multiple XML files describing sheets, cells, styles, formulas, metadata, timestamps, fonts, colors…

You can literally rename file.xlsx → file.zip and unzip it:

XML for Excel File




Encoding: Why Text Isn’t as Simple as It Looks

Encoding is how computers translate human text (letters, accents, symbols) into 1s and 0s.

CSV files use your system’s default text encoding (usually UTF-8), but Excel often guesses the encoding—and sometimes guesses wrong, which can break special characters.
​
A classic example: names like Müller suddenly turn into Müller the moment you open a CSV in Excel.


A Lesson I've Learnt the Hard Way

A CSV file can only hold one sheet. 

When you click “Save as CSV” in Excel, only the active sheet gets saved. All other sheets are silently dropped. No warning. No mercy.

Why? Because CSV doesn’t understand sheets. It’s just rows and columns in a straight line—

no tabs, no formulas, no metadata. Excel is a workbook. CSV is a single table.

If you need to export an Excel file with multiple sheets to CSV, don’t use “Save As.”

Instead:

  • Export each sheet as its own CSV file, or

  • Combine the sheets into one table first (with a column like sheet_name), then export



Use Cases

Understand the structure, and the choice makes itself!

Excel for ad-hoc analysis.

Think: Finance reports with conditional formatting and macros
Primary User: Humans

 CSV for everything scalable.


Think: Database imports, Machine learning datasets, ETL jobs, APIs
Primary User: Machines

Final Thought

Excel and CSV look similar, but they live in completely different layers of the data stack.

One is a user interface.
The other is a data transport format.

Once you internalize that, a lot of data problems suddenly make sense.

And you’ll break fewer pipelines. 

Next time you're tempted to "just save as Excel," ask: Do I need the Ferrari or the bicycle?


Resources




 

Comments

Post a Comment

Popular posts from this blog

Understanding Outliers

-
Image
You can clearly see which one stands out   Today's post is inspired by a question one of my colleagues had around outliers: Are there different types? And to that question, the simple answer is yes! As data professionals, we often encounter data points that stand out like a sore thumb during analysis, leaving us wondering what to do with them. In most cases, deletion seems like the obvious choice.  However, I believe we need to pause and take a closer look at these points before removing them. You see, during Exploratory Data Analysis (EDA for short), asking the right questions about the dataset is crucial to uncovering its full story.  Deleting an outlier just because it stands out can create a narrow, myopic view of the data, limiting the depth and value of the insights you can gain. Rather than rushing to remove outliers, consider what they might reveal. Could they signal an emerging trend, an unusual but important event, or even a data collection issue?  With t...
Read more

Wait! Before You Throw Away Your Laptop...

-
Image
“Our whole economy is based on planned obsolescence.” — Brooks Stevens   My Lenovo IdeaPad 350 carried me through my Computer Science degree and even hosted the beginnings of this blog series.  It witnessed late-night coding marathons, countless essays, and the very first drafts of ideas I’m still exploring today. I’ve since upgraded to an ASUS Zenbook S16 (which I’m very happy with) , but my old laptop still sits quietly on a shelf, waiting for its final chapter. If you didn’t know, the average lifespan of a laptop is about five years. When I asked a few friends what they do with their old machines, most admitted they just… keep them.  I guess I know a lot of hoarders... But honestly, that’s still better than those who throw them away like an old pair of shoes. Here’s something to think about: the world generates over 50 million metric tons of e-waste each year , and only about 20% is recycled properly . That means millions of tons of toxic metals—like lead, mercury, an...
Read more

October Reflections

-
Image
The name October comes from the Latin “octo,” meaning eight . This morning, my dad asked about my blog. I winced — I’ve been dodging it since September! But that question was the nudge I needed. Because when I stopped to think, I realized:  even without posting, I’d been living. I hiked (and fell multiple times). Tried lobster for the first time. Met my coworkers in Barbados. And impulsively bought this... (I only had 1/4) All that living gave me plenty to reflect on — and two moments in particular stood out. 1. Women in Data Science (WiDS) TT Spotlight Talk Dr. Letetia Addison invited me to speak at the Women in Data Science TT Spotlight Talk — for the second time!  She actually found me through this very blog. I wasn’t publishing much here, but I was active on LinkedIn.  When it came time to prepare my WiDS talk, I had weeks, but I started only days before. Procrastination kept winning. Then came a call with my friend Carson White, we brainstormed ways in whic...
Read more

Learning Something New: EDA on Guitars

-
Image
This is an acoustic guitar Today’s post is inspired by my childhood dream of learning to play the guitar. For years, I’d daydream about strumming chords and creating melodies, but it always felt like something I’d do “someday.”  That day finally arrived when I jokingly shared this dream aloud, and my dad decided it was time to make it a reality. Yesterday, he surprised me with my first guitar—a sleek, acoustic guitar with nylon strings. The moment I picked it up, I was both excited and intimidated. I immediately started tuning it and spent an hour practicing E-minor and A-minor chords.  My fingers quickly grew sore, and faint ridges were left behind from pressing on the strings—but I didn’t mind.  There was something unexpectedly soothing about the vibration of the strings resonating through the guitar, as if the instrument itself was encouraging me to keep going. Learning a new skill is a reminder of how persistence pays off. I know that mastering the guitar will take ti...
Read more

The Human Side of Tech: Emotional Intelligence

-
Image
  The only way to deal with emotions is to find a way to express them. The term gained prominence with the publication of Daniel Goleman's book "Emotional Intelligence" in 1995, but the underlying ideas have been studied and discussed by psychologists for decades. At its core,  Emotional Intelligence means having a deep awareness of emotions—both your own and those of others.  This heightened sensitivity serves as a foundation for conflict resolution and relationship cultivation. Simply asking someone how their day is going and observing their facial expressions and tone of voice makes a difference.  In life, change is the only constant; hence, we can't always expect our coworkers to be equally responsive and cheerful every day. It's crucial to recognize that they might be facing challenges we're unaware of, such as dealing with the recent loss of a family member or an undisclosed injury. Our ability to detect signs of distress not only enables us to offer bet...
Read more

Is it OK to kick a robot dog?

-
Image
Boston Dynamics created a robot dog named Spot that can dance Is it okay to kick a robot dog? It’s a funny question on the surface. It’s not alive . It doesn’t feel pain. So… who cares? Lately I’ve been reading a book on AI Ethics by philosopher Mark Coeckelberg, the book is filled with tricky questions that make you stop and rethink our relationship with machines—especially the smart ones. Let me walk you through a few of them, with a few everyday examples to help make sense of it. Q1: Should We Hold Robots Responsible for Their Actions? Imagine this: a self-driving car runs a red light and causes an accident. Who do we blame? The car? (It was the one driving!) The engineers who built it? The company that sold it? Mark brings up a similar question in the book: “We don’t hold very young children responsible for what they do because they don’t know better—should the same be done for AI?”  Q2: What are the things that should only be done by humans? Let’s say AI gets really, really...
Read more

Tableau, Power BI and My Preference

-
Image
Power BI is one of the fastest-growing skills overall according to Coursera's Skill Report 2024  Today’s post is inspired by my decision to learn more about Power BI over Tableau. My bias stems from the fact that Power BI was my first data visualization love, and you never forget your first! Now, when you're in the dashboard creation game, you're likely to be familiar with Tableau and Power BI (PBI for short). If not, think of these tools as canvases for placing your data visualizations such as donut charts and histograms to tell a cohesive story around a specific problem you are trying to solve.  I want to explore the key differences between the two to form a more balanced argument when discussing with a Tableau enthusiast who claims that  "Tableau is better" (very funny). Just so you know, I don’t outright hate Tableau. In fact, I use it for most of the dashboards I create at work. The Tableau community is fantastic, and the tool allows for highly customized vis...
Read more

Women In STEM : Challenges and Advantages

-
Image
  Women make up only 27% of the STEM workforce. Introduction Being a female in Technology, I can also identify as being a "Woman in STEM" and I can say being part of this group is much different from our male counterparts. When you think of an engineer, the immediate image that comes to mind is often that of a man earning a six-figure salary. Before directing any resentment towards men, it is crucial to pose this question:  "What caused this gender gap in the first place?"  Common Challenges Two terms frequently used to answer this question are stereotypes and gender roles. The stereotypical woman is caring, nurturing, and supportive. She is also expected to stay at home, to cook, clean, and raise children. However, in 2024, being a stay at home mom is rare, with more women actively participating in the workforce, some even serving as the primary breadwinners of their households. But, they are still expected to take on the responsibilities of a stay at home mom, whi...
Read more

No Laptop November

-
Image
"Life is hard, but that's what makes it interesting" - Dad It's time to break the silence once more! Things did not go as I planned, but it makes for a good story.  So you're probably wondering... " Toni, what happened? I thought you were coming back? " and to that I would say, I thought the same. I thought that I would get back in my groove and post blog 109 weeks ago! My simple hardware failure quickly escalated into a complex and costly international shipping disaster. It all started on November 7th at about 5 AM while I was clacking away on my keyboard, the display for my newly purchased Zenbook S16 decided that it was time to say goodbye. I made every attempt to reilluminate it by doing the famous "turn-it-off-and turn-it-back-on" trick and confirmed that it was a hardware issue with Asus Helpdesk. I still held a warranty , which was a relief. However, I had to send the laptop to the States so the manufacturer could fix it and send it ba...
Read more

Work-Life Balance: Be Intentional About It

-
Image
Balance is not something you find, it's something you create.   As I get older, I realize the importance of the little things: deep conversations with my parents, laughs shared with my siblings and friends, and the chance to experience new things. I am about to turn 26 (almost 30, as I've told my mom), and I want to make an effort to travel. Why? Because I feel like I have experienced the majority of things that Trinidad and Tobago has to offer.  From beef roti to steelpan, it's truly amazing to be here, but the world is a big place with so much to learn! Anyway, that's enough of my soliloquies.... Today's post is inspired by all my sleep-deprived friends, the ones who keep on going! We often forget that we are more than just our jobs. We neglect the other roles we play in life, from daughter or son to father or mother, and friend. Mid-year reviews are coming up and while we should all strive to be a good employee sometimes we need to stop and ask ourselves, "W...
Read more