How to Use the OfficeCLI Import Command to Bring CSV Data into Excel Sheets

The OfficeCLI import command bulk-loads CSV or TSV data into existing Excel workbooks by parsing command-line options, validating dimensions against Excel's native limits, and delegating to specialized handlers that preserve data types and structure.

The OfficeCLI tool from the iOfficeAI/OfficeCLI repository provides a dedicated import sub-command for efficiently transferring delimited data into .xlsx files. This command handles everything from automatic delimiter detection to Excel's maximum row and column constraints, offering a robust pipeline for data integration workflows.

Command Syntax and CLI Options

The import functionality is defined in src/officecli/CommandBuilder.Import.cs, which registers the command syntax officecli import <file> <parent-path> and supports several critical options:

• --file: Specifies the path to the CSV or TSV file to import
• --stdin: Reads delimited data from standard input instead of a file
• --format: Forces a specific format (csv or tsv) to override automatic detection
• --header: Treats the first row as headers, enabling AutoFilter and freeze panes
• --start-cell: Defines the target cell (e.g., B2) where data insertion begins

The Import Pipeline Architecture

When executed, the import command follows a rigorous ten-step process that ensures data integrity and Excel compatibility.

Input Validation and Source Reading

The command first verifies that the target workbook exists and carries a valid .xlsx extension. It then reads input either from the filesystem via --file or from stdin when the --stdin flag is provided.

Delimiter Detection Strategy

If the --format option is explicitly provided, it overrides automatic detection. Otherwise, the system inspects the file extension: .tsv or .tab files default to tab delimiters, while all other files default to comma separation.

Core Processing in ExcelHandler.Import

The parsed arguments are forwarded to ExcelHandler.Import in src/officecli/Handlers/Excel/ExcelHandler.Import.cs. This handler orchestrates the remaining workflow, beginning with the ParseCsv method that correctly handles quoted fields, escaped quotes, embedded delimiters, and line breaks to produce a List<List<string>> structure.

Dimension Safety Checks

Before modifying the workbook, the handler validates that the import will not exceed Excel's hard limits of 1,048,576 rows or 16,384 columns (XFD). If the data would exceed these boundaries, the operation throws a clear ArgumentException and terminates without corrupting the target file.

Row Upsert and Duplicate Prevention

The implementation performs an efficient row upsert operation. Existing rows are indexed once and reused, while new rows are inserted in order. This approach avoids duplicate <row> elements, addressing the specific edge case noted in the "BUG-R11-import-dup-row" comment within the source.

Cell Value Type Detection

Each cell value is processed through SetCellValueWithTypeDetection, which automatically identifies:

• Numeric values
• ISO date strings (formatted as yyyy-mm-dd)
• Boolean values
• Formulas (strings prefixed with =)
• Plain text strings

Header Handling and Worksheet Formatting

When the --header flag is active, the command adds an AutoFilter covering the imported range and creates a freeze pane below the header row, preserving header visibility during scrolling.

Practical Usage Examples

Import a local CSV file into Sheet1 starting at cell A1:

officecli import book.xlsx /Sheet1 --file data.csv

Pipe TSV data from stdin with header recognition and custom start cell:

cat data.tsv | officecli import book.xlsx /Sheet1 \
    --stdin --format tsv --header --start-cell B2

Force CSV format despite file extension and output JSON for scripting:

officecli import book.xlsx /Sheet1 --file data.csv --format csv --json

Key Source Files

Understanding the implementation requires familiarity with these specific files:

• src/officecli/CommandBuilder.Import.cs: Defines CLI syntax, parses options, and routes input to the appropriate handler
• src/officecli/Handlers/Excel/ExcelHandler.Import.cs: Contains the core import logic including CSV parsing, dimension validation, row management, and cell type detection
• src/officecli/CommandBuilder.cs: Integrates the import command with the generic command dispatcher
• src/officecli/Core/ParseHelpers.cs: Provides utilities for interpreting boolean-like strings used by the --header flag

Summary

• The OfficeCLI import command provides a complete pipeline from delimited text to Excel worksheets
• Automatic delimiter detection defaults to tabs for .tsv/.tab files and commas for all others, overrideable via --format
• Dimension validation prevents imports that would exceed Excel's 1,048,576 row or 16,384 column limits
• Type detection preserves numeric, date, boolean, and formula data rather than coercing everything to strings
• Header support adds AutoFilter and freeze panes for improved usability
• Row upsert logic prevents duplicate XML elements while efficiently reusing existing worksheet rows

Frequently Asked Questions

Can I import data from standard input instead of a file?

Yes. Use the --stdin flag to read CSV or TSV data from standard input. This is particularly useful for piping output from other commands or processing streams in automated scripts.

How does OfficeCLI handle different CSV delimiters automatically?

The tool checks the source file extension. Files ending in .tsv or .tab automatically use tab delimiters, while all other files default to commas. You can override this behavior explicitly using the --format option followed by either csv or tsv.

What happens if my CSV file has more rows than Excel allows?

The ExcelHandler.Import method validates dimensions before writing any data. If your import would exceed Excel's maximum of 1,048,576 rows or 16,384 columns (XFD), the command throws an ArgumentException and exits without modifying the workbook.

Does the import command preserve data types like dates and numbers?

Yes. The SetCellValueWithTypeDetection function analyzes each cell value to detect numbers, ISO-formatted dates, booleans, and formulas (entries starting with =). Dates receive the numeric format yyyy-mm-dd, ensuring they behave correctly in Excel rather than appearing as text strings.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}