DEV Community

Cover image for The Technology Behind SmoothCSV - The Ultimate CSV Editor
kohii
kohii

Posted on • Edited on

The Technology Behind SmoothCSV - The Ultimate CSV Editor

My self-developed CSV editor SmoothCSV (v3) has become Generally Available, so I'd like to share some technical insights and innovations behind it.

Also launching on Product Hunt on July 1st (PT) πŸš€

SmoothCSV: The ultimate CSV editor you've been waiting for | Product Hunt

SmoothCSV is a cross-platform CSV editor built for real work. Edit your files intuitively, exactly how you want. Lightning-fast performance, handles messy data, and powerful features like SQL queries. Just smooth CSV editing that works.

favicon producthunt.com

About Me

I work as a software engineer at a healthcare startup while doing personal development projects.

About SmoothCSV

SmoothCSV is a CSV editor for macOS and Windows. (Linux coming soon)
I've been developing the original SmoothCSV for 15 years, and started working on v3 last year.

SmoothCSV - The ultimate CSV editor for macOS & Windows

SmoothCSV is the ultimate CSV editor for macOS & Windows. It's fast, easy to use, and packed with powerful features.

favicon smoothcsv.com

Features:

  • Excel-like operation for intuitive use
  • Equipped with basic to advanced tools necessary for handling CSV
  • Supports various formats and character encodings. Can handle CSVs with different column counts
  • Fast! Handles large files smoothly

Slide1

Slide2

Slide3

Slide4

Technical Elements

Main Technology Stack

Technical Architecture

Technical Architecture

Being a desktop application, the technical architecture is quite simple. There's no external communication except for checking and downloading updates.

1. Technology for Building Complex Desktop Applications

SmoothCSV aims to be a feature-rich yet simple and highly extensible application.

Tauri is Great

Tauri is a desktop application framework that serves as an alternative to Electron. (From Tauri v2, it also supports mobile apps.)

While Electron uses Node.js for the backend and Chromium for the frontend, Tauri uses Rust for the backend and the OS's bundled web renderer for the frontend. This results in smaller distribution size and lower memory usage.

Tauri comes with comprehensive APIs and plugins that make it easy to implement desired features.

Separation of Concerns through Package Extraction

Managing as a monorepo with pnpm workspaces + Turborepo.

(root)
β”œβ”€ apps
β”‚  └─ desktop             # SmoothCSV desktop app main body (depends on packages/*)
└─ pakages
   β”œβ”€ csv                 # CSV parsing and stringification
   β”œβ”€ grid-editor         # React component library for tabular editor
   β”œβ”€ result              # Result type TypeScript implementation
   β”œβ”€ utils               # General utilities
   β”œβ”€ js-sql              # TypeScript SQL parser and evaluation engine
   β”œβ”€ framework           # General desktop app foundation. Command system, key bindings, settings, etc.
   β”œβ”€ context-expression  # VSCode-compatible conditional expression parser
   └─ l10n                # VSCode-compatible localization API
Enter fullscreen mode Exit fullscreen mode

Under packages, independent concerns that don't depend on SmoothCSV-specific context are extracted as libraries. This makes individual concerns easier to handle while reducing the complexity of the desktop main body.
Within desktop, code is also structured by independent concerns as much as possible. (package by feature, etc.)

Command-Centric System

In SmoothCSV, operations that users can execute on the application are defined as commands. While this was similar in the previous version of SmoothCSV, this time it's built as an API similar to VS Code.

Commands consist of handler functions and metadata (ID, title, availability conditions, etc.).

Image of a command to close a tab:

aCommand = {
  command: "view.closeEditor",
  title: "Close Editor",
  category: "View",
  handler: async () => {
    // Processing when command is invoked
  }
}
Enter fullscreen mode Exit fullscreen mode

Commands are mapped to UI elements such as keyboard shortcuts, context menus, and buttons, and are triggered. You can also search for and execute any command from the "Command Palette".

Example of assigning a keyboard shortcut to the close tab command:

{
  "keybindings": [{
    "command": "view.closeEditor",
    "key": "ctrl+w",
    "mac": "cmd+w"
  }]
}
Enter fullscreen mode Exit fullscreen mode

Example of executing from the Command Palette:

Command palette

For applications with a certain level of complexity, having command-centric extension points makes it easier to add features.

System for Declaring Command and Menu Enabling Conditions

This is also a system mimicking VS Code's when clause contexts.

Each command, menu, and key binding has situations where it cannot be used.
For example, the command to start cell editing requires the Grid editor to have focus and not be in cell editing mode.
SmoothCSV provides a notation to declaratively express such conditions.

{
  "command": "cellEditor.startEditingCell", // Command to start cell editing
  "enablement": "gridEditorCellFocus && inReadyMode", // Command is available when Grid editor is focused and not in cell editing mode
  ...
}
Enter fullscreen mode Exit fullscreen mode

While implementing parsing is somewhat difficult, having such a system allows requirements to be configured and facilitates future extensions and customizations.

Providing Extension APIs from the Initial Stage

As with commands, extension APIs are created first rather than immediately building individual features.

While this is partly for users to create extensions in the future, the main purpose is to make standard features easier to build. Having stable APIs makes development easier, and several features are actually built as system extensions.

Things built as extensions within SmoothCSV:

System extensions

Advantages

  • Separation of concerns
    • Concerns are contained within each extension's directory
    • Organized and easy to read
  • Easy to have AI create
    • Even if design and code structure are somewhat rough, it doesn't matter much as it's contained within the extension
    • If you don't like it anymore, you can just throw away the entire extension and rebuild
  • Lazy loading
    • Code loading is deferred until extension features are actually used β†’ Performance improvement

However, this approach may work well only in limited situations. It's likely to waste time as it's difficult to see what APIs are needed at the initial stage. (SmoothCSV was relatively predictable as it was a replacement development)

Adopting MobX for State Management

For frontend state management, after trying various options like a self-made simple Store β†’ Zustand β†’ self-made again... I settled on MobX.
In early development, I was particular about handling state as plain objects, but this resulted in more work to implement each use case and difficulty managing computed values.

These problems were well resolved by using MobX.
MobX manages state with class-based objects and reactively reflects them in React components.

// MobX object holding state
class FindWidget { // Model governing the widget for search/replace
  @observable mode: "find" | "replace" | "hidden";γ€€// @observable makes property changes observable
  @observable.ref findParams: FindParams;γ€€// @observable.ref observes only reference changes. (FindParams is readonly object, so it's replaced as a whole when updated)
  @observable.ref replaceParams: ReplaceParams;

  @computed // Derived property (value is cached)
  get isVisible() { return this.mode !== "hidden" }

  @action.bound // Add @action to change state
  hide() { this.mode = "hidden" }
}

// Using from React component (wrapped with observer to render reactively to state changes)
const FindWidgetComponent = observer(() => {
  const model: FindWidget = ...
  if (!model.isVisible) return null;
  return <FindWidgetForm findParams={model.findParams} onHide={model.hide} />; // Call pure component
});
Enter fullscreen mode Exit fullscreen mode

However, I try to minimize the scope of MobX dependencies.

  • Container/Presentational Pattern-like
    • Create core parts as pure and controllable function components (Presenter)
    • Only top-level components (Container) depend on MobX and map from MobX objects to Presenter component props
  • Important/complex logic is also created as independent pure functions
    • Creating as methods of MobX objects creates unnecessary property dependencies (most functions don't need the entire object)

Components and functions without unnecessary dependencies are highly stable and become assets.
SmoothCSV is particular about building up these assets.

Auto-update

SmoothCSV has a mechanism to automatically detect when a new version is released, notify users, and allow them to download and install it directly.
This mechanism is crucial for rapidly adding features and fixing bugs.

Tauri has an updater plugin that makes it easy to implement these processes.
SmoothCSV uploads JSON with update information, distribution files, and signatures to GitHub Releases, and checks for new versions at application startup.

2. Technology for Building Tabular Editors

SmoothCSV has an Excel-like tabular editor.
The core React component is built as a library independent from SmoothCSV.

Cell Rendering

CSV files can contain large amounts of data, such as millions of rows.
Rendering all cells directly is impractical, so SmoothCSV uses virtual scrolling. However, there are actually limitations to regular virtual scrolling, so I've built the scrolling mechanism from scratch.
Details here:

kohii

How to Implement Virtual Scrolling Beyond the Browser's Limit

kohii ・ Oct 8 '24

Cell Editing

In Excel and Google Sheets, typing any character immediately enters cell editing mode.

While this behavior is common in many tabular editors, few work correctly with Japanese input.

Common Implementation

  1. Monitor keydown to start cell editing
  2. Set the entered character as the initial value of the cell editor and display it

This causes "a" to be entered when trying to input "あ" in Japanese.

SmoothCSV's Implementation

  • Place an invisible textarea and keep it focused
  • When any character is entered, display this textarea as the cell editor

start editing cell

Dynamically Expanding/Contracting Cell Editor Based on Input

When text is entered into the cell editor, its width and height change according to the content.
The mechanism involves placing an invisible span with exactly the same style as the textarea, copying the content entered in the textarea into it. The width and height of this span are obtained and set as the style of the textarea.

auto resize cell editor

3. Technology for Building SQL Integration Features

SmoothCSV has two features that utilize SQL.

Filter

A feature that displays only rows matching specified conditions and hides others. While Excel and Google Sheets also have filter features, SmoothCSV allows specifying conditions with SQL WHERE clauses for more flexible filtering.

Filter

The main purpose is to express filter conditions as strings, borrowing the widely familiar SQL notation for this.

  • Filter conditions being strings makes them easy to save and reuse with copy & paste
  • Easy to have AI generate
  • Can be handled as structured data when parsed

Also, there's a feature to edit filter conditions with a GUI, making it easy for non-technical users to use.

Filter Condition Builder

Inside, it converts SQL ↔ AST ↔ GUI data model.

SQL Console

The SQL Console is a feature that issues SQL SELECT statements against files opened in SmoothCSV or local CSV files.
Using notation like "@file:/Path/To/File.csv", you can treat CSV files as tables.

SQL Console

Mechanism

Uses embedded SQLite. When SQL is entered and executed, table notation is extracted from the SQL, corresponding temporary tables are created, data is loaded, and then the actual SQL is executed.

CREATE TEMPORARY TABLE "@file:/Path/To/File.csv" ( -- SQLite allows any table name when enclosed in ""
  /* columns */
)
Enter fullscreen mode Exit fullscreen mode

Conclusion

Thank you for reading to the end!
There's so much I wanted to write about that the explanations became brief, so if you have questions about how something works, please feel free to ask.

If you have questions, requests, or feedback, please let me know through comments or GitHub Issues.

Upvotes and comments on Product Hunt would also be encouraging πŸ™

SmoothCSV: The ultimate CSV editor you've been waiting for | Product Hunt

SmoothCSV is a cross-platform CSV editor built for real work. Edit your files intuitively, exactly how you want. Lightning-fast performance, handles messy data, and powerful features like SQL queries. Just smooth CSV editing that works.

favicon producthunt.com

Bonus

Your support for development would be appreciated.

kohii is SmoothCSV, a powerful and intuitive tool for editing CSV files - Buymeacoffee

Hi thereπŸ‘‹I'm full-stack developer, architect, designer, and indie hacker. I'm building my own products while working at Henry Inc., a Japanese startup.SmoothCSV - An ultimate CSV editor for macOS and

favicon buymeacoffee.com

Top comments (8)

Collapse
 
ikx profile image
Ilya Kashnitsky •

Thank you for this amazing lightweight and blazing fast app! Just the one that I've been looking for from time to time to replace the ugly buggy sluggish excel.

I've added a page to AlterntiveTo for your app (hopefully approved soon)
alternativeto.net/software/smoothcsv/

Collapse
 
kohii profile image
kohii •

Thank you so much for the kind words! I'm really glad SmoothCSV is helping you.

And thanks for adding it to AlternativeTo. As a Japanese developer, I've been wondering how to reach English-speaking users, so this kind of listing is really appreciated!

Collapse
 
ikx profile image
Ilya Kashnitsky •

TBH, I have no idea how influential is AlternativeTo globally, I've been loving it for almost a decade. But apparently this blog is a good venue β€” I saw your post via Google home app

Thread Thread
 
kohii profile image
kohii •

That's really helpful insight! Thank you for sharing that.

Thread Thread
Collapse
 
naruaika profile image
Naufan R. Faikar •

Wow, this is a beautiful work! The article as well! I just learnt a lot. Thanks for sharing. Upvote this post. πŸ‘

Collapse
 
kohii profile image
kohii •

Thank you! So glad you found the technical details interesting. Really appreciate the support! πŸ™

Collapse
 
jacknop profile image
Jack •

Amazing tool, definitely getting it later if the pricing works.

Some comments may only be visible to logged-in visitors. Sign in to view all comments.