In cross-industry data processing scenarios, importing data from CSV and PDF files into Excel is one of the most common and error-prone tasks — finance teams reconcile CSV bank statements, e-commerce teams organize order files exported from multiple platforms, and administrative staff handle PDF statements from suppliers. These files come in all shapes and formats: inconsistent CSV delimiters, fields containing commas, dates appearing in various forms, phone numbers and ID numbers that start with 0 are treated as numbers and lose their leading zeros; PDF tables cannot be edited directly, and copying them into Excel misaligns rows, columns, and merged cells.

The traditional approach is to split columns manually, set formats column by column, and hunt for erroneous cells by eye. A CSV file with a few hundred rows often takes half an hour of repeated adjustment; PDF tables can only be copied and pasted row by row. Traditional methods are also prone to misaligned columns, misplaced dates, and numbers turning into text. As data volume grows, manual processing becomes nearly impossible.

Take a finance team reconciling bank statements, for example: after receiving a CSV, the usual routine is to confirm the encoding in a text editor first, split the columns in Excel, set date and amount formats column by column, and then hunt for anomalous values by eye. A field containing a comma shifts the whole row, accounts starting with 0 lose their leading zeros, and only after repeated adjustment does the table become usable. PDF statements can only be copied and pasted row by row — rows, columns, and merged cells are almost all misaligned, and reconstructing a single statement often eats up half a day.

Comparison with Traditional SDK API Processing

This article introduces how to use the Excel AI capabilities of Spire.Agent.Office to implement CSV smart column splitting import and PDF table import, automatically completing data type detection and highlighting erroneous formats in red, with just a single natural language instruction.

• CSV Smart Column Splitting Import
• PDF Table Import to Excel

For product installation and SpireToken configuration, please refer to Integrating Spire.Agent.Office in a .NET Project. The following examples assume Spire.Agent.Office is already installed and SpireToken is configured.

CSV Smart Column Splitting Import

CSV is the most common format for data exchange, yet also the least "controllable": the delimiter may be a comma, a tab, or a semicolon; fields may contain commas or line breaks wrapped in quotes; dates, numbers, and text are mixed in the same table; values starting with 0, such as phone numbers and codes, are treated as numbers by default and lose their leading zeros. Import quality directly determines the accuracy of subsequent analysis and reports.

The following example uses the Spire.Agent.Office agent to automatically import a CSV through natural language instructions, completing smart column splitting, data type detection, and highlighting erroneous formats in red:

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Xls;

// CSV source file to be imported (passed as an attachment)
string[] attachmentPaths = new string[] { @"C:\DataImport\employee_sales_data.csv" };
// Save path of the import result document
string savePath = @"C:\DataImport\ToXLSX.xlsx";
// SpireToken Key (apply on the official website)
string key = "sk-TF***************************r";
// Natural language instruction
string instruction = "Process as follows:\n" +
    "1. Convert the attached CSV file to an Excel document and apply appropriate formatting to improve readability\n" +
    "2. Unify the formats of dates/sales amounts/phone numbers in the file\n" +
    "3. Mark erroneous and missing data with a red background";

// AI generation
AIResult result = ImportCsvData(instruction, savePath, key, attachmentPaths);

// AI-assisted CSV import
static AIResult ImportCsvData(string instruction, string savePath, string key, string[] attachmentPaths)
{
    // Configure the AI processing options
    AIOptions options = new AIOptions();
    options.SpireToken = key;

    using (Workbook wb = new Workbook())
    {
        AIDocumentProcessor processor = wb.AI(options);
        return processor.ExecuteInstruction(wb, instruction, savePath, attachmentPaths);
    }
}

Original CSV data and smart column splitting import result

PDF Table Import to Excel

PDF is the universal format for distribution and archiving, but the table data inside it cannot be edited directly: copying it into Excel misaligns rows and columns, loses merged cells, and turns numbers and dates into text. When suppliers, banks, or government agencies deliver reports in PDF, accurately restoring the table data into editable Excel is an essential step in moving from fixed-layout documents to electronic processing.

The following example uses the Spire.Agent.Office agent to automatically extract table data from a PDF and write it into Excel through natural language instructions:

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Xls;

// PDF source file to be imported (passed as an attachment)
string[] attachmentPaths = new string[] { @"C:\DataImport\PurchaseOrder.pdf" };

// Save path of the import result document
string savePath = @"C:\DataImport\PurchaseOrderData.xlsx";
// SpireToken Key
string key = "sk-TF***************************r";
// Natural language instruction
string instruction =
    "Process as follows:\n" +
    "1. Convert the attached PDF file to an Excel document and apply appropriate formatting to improve readability\n" +
    "2. Unify the formats of dates/sales amounts/phone numbers in the file\n" +
    "3. Mark erroneous and missing data with a red background";
// AI generation
AIResult result = ImportPdfData(instruction, savePath, key, attachmentPaths);

// AI-assisted PDF import
static AIResult ImportPdfData(string instruction, string savePath, string key, string[] attachmentPaths)
{
    // Configure the AI processing options
    AIOptions options = new AIOptions();
    options.SpireToken = key;

    using (Workbook wb = new Workbook())
    {
        AIDocumentProcessor processor = wb.AI(options);
        return processor.ExecuteInstruction(wb, instruction, savePath, attachmentPaths);
    }
}

Original PDF data and table data extracted into Excel

Frequently Asked Questions

Inconsistent CSV delimiters / commas within fields cause column misalignment

Cause: The CSV delimiter may be a semicolon or a tab, or a field may contain a quoted comma or newline, which causes the whole row to shift when columns are split automatically.

Solution: Specify the delimiter in the instruction, or let the AI identify it automatically and correctly handle the quoted fields.

Numbers starting with 0 lose their leading zeros

Cause: Values starting with 0, such as phone numbers, ID numbers, and account numbers, are imported as numeric values, and the leading zeros are dropped.

Solution: Specify the relevant columns as text type in the instruction, such as "set the phone number and ID number columns to text format and preserve the leading zeros".

Obtaining a SpireToken Key

• Contact sales@e-iceblue.com or visit https://www.e-iceblue.com/TemLicense.html to obtain a trial/commercial API key

Configure it in code:

AIOptions options = new AIOptions();
options.SpireToken = key;

Reconciliation is one of the most frequent and tedious tasks in corporate finance, and the source data often comes in different forms: bank statements are CSV files exported from online banking, while system transaction records may be PDF detail reports. The two tables have different column names, inconsistent date and amount formats, and even stray spaces and missing values. This article shows how to use Spire.Agent.Office Excel AI capabilities to automatically read CSV and PDF data sources, identify and map column names, clean the data, and finally generate an Excel reconciliation detail report.

• Reconcile by Statement Number
• Reconcile by Date and Amount Combination

For product installation and SpireToken configuration, please refer to Integrate Spire.Agent.Office in a .NET Project. The following examples assume Spire.Agent.Office is installed and SpireToken is configured.

Reconcile by Statement Number

Reconcile and analyze the CSV-format bank statement with the PDF-format system transaction records by statement number.

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Xls;

// Data source files: bank statement (CSV) and system transaction records (PDF)
string[] attachmentPaths = new string[]
{
    @"bank-statement.csv",   
    @"system-records.pdf"    
};

// Excel processing configuration
string inputPath = "";  
string savePath = "out.xlsx";  
// SpireToken Key
string key = "**************************"; 
string instruction =
    "Reconcile the bank statement (CSV) with the system transaction records (PDF) in the attachments: " +
    "1. Establish the column mapping of the two tables by semantics: transaction date, amount, counterparty account, description, statement number; " +
    "2. Cleaning: strip leading/trailing and internal extra spaces from text; write dates as yyyy-MM-dd text; convert amounts to numbers by removing currency symbols and thousands separators; mark empty description or empty counterparty as 'Unknown', mark empty amount as 'Amount missing'; " +
    "3. Match row by row using the statement number as the unique key, and mark the status: 'Matched'/'Amount mismatch'/'Bank only'/'System only'; " +
    "4. Generate a 'Reconciliation Detail' worksheet: each record with bank amount, system amount, difference, status and remark; " +
    "5. Highlight difference rows: yellow for amount mismatch, orange for bank only, blue for system only; " +
    "Finally save the output as an Excel file";

// Call the Excel document processing function
AIResult result = ExecuteDemoExcel(instruction, inputPath, savePath, key,  attachmentPaths);


// Execute Excel document AI processing
static AIResult ExecuteDemoExcel(string instruction, string inputPath, string savePath, string key, string[] attachmentPaths)
{
    // Create an AIOptions configuration object
    AIOptions options = new AIOptions();
    options.SpireToken = key; 

    // Use the Workbook object to process the Excel document
    using (Workbook workbook = new Workbook())
    {
        // Load the Excel template from a file
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            workbook.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIWorkbookProcessor processor = workbook.AI(options);

        // Execute the AI instruction
        return processor.ExecuteInstruction(workbook, instruction, savePath, attachmentPaths);
    }
}

Original bank statement CSV Original system transaction PDF Reconciliation detail after Excel AI reconciliation

Reconcile by Date and Amount Combination

When the data source does not contain a unique statement number, you can use the "transaction date + amount" combination as the matching key for reconciliation: first group by date, then pair the records by amount within the same date.

using Spire.Agent.Office.AI;
using Spire.Agent.Office.Extensions;
using Spire.Xls;

// Data source files without statement numbers: bank statement (CSV) and system transaction records (PDF)
string[] attachmentPaths = new string[]
{
    @"bank-statement-noId.csv",   
    @"system-records-noId.pdf"   
};

// Excel processing configuration
string inputPath = "";  
string savePath = "out.xlsx";  
// SpireToken Key
string key = "**************************";  
string instruction =
    "Reconcile the bank statement (CSV) with the system transaction records (PDF) in the attachments: " +
    "1. Establish the column mapping of the two tables by semantics: transaction date, amount, counterparty account, description; " +
    "2. Cleaning: strip extra spaces from text; write dates as yyyy-MM-dd text; convert amounts to numbers; mark missing values as 'Unknown' or 'Amount missing'; " +
    "3. Use the 'transaction date + amount' combination as the matching key: first group by date, then pair the records by amount within the same date, and mark the status: 'Matched'/'Amount mismatch'/'Bank only'/'System only'; " +
    "4. Generate a 'Reconciliation Detail' worksheet (bank amount, system amount, difference, status); " +
    "5. Highlight difference rows: yellow for amount mismatch, orange for bank only, blue for system only; " +
    "Finally save the output as an Excel file";

// Call the Excel document processing function
AIResult result = ExecuteDemoExcel(instruction, inputPath, savePath, key,  attachmentPaths);


// Execute Excel document AI processing
static AIResult ExecuteDemoExcel(string instruction, string inputPath, string savePath, string key, string[] attachmentPaths)
{
    // Create an AIOptions configuration object
    AIOptions options = new AIOptions();
    options.SpireToken = key; 

    // Use the Workbook object to process the Excel document
    using (Workbook workbook = new Workbook())
    {
        // Load the Excel template from a file
        if (!string.IsNullOrEmpty(inputPath) && File.Exists(inputPath))
        {
            workbook.LoadFromFile(inputPath);
        }
        // Create the AI document processor
        AIWorkbookProcessor processor = workbook.AI(options);

        // Execute the AI instruction
        return processor.ExecuteInstruction(workbook, instruction, savePath, attachmentPaths);
    }
}

Original bank statement CSV Original system transaction PDF Reconciliation detail after Excel AI reconciliation

Comparison with Traditional SDK API Processing

Frequently Asked Questions

Inconsistent date and amount formats in the bank statement CSV

Cause: In the CSV exported from online banking, dates may be written as 2026-07-01, 2026/7/1, etc., and amounts may carry ￥, thousands separators, or leading/trailing spaces, leading to misjudgment during matching.

Solution: Explicitly require in the instruction "unify dates as yyyy-MM-dd and amounts as numeric formats and remove spaces", and AI will complete the standardization automatically before reconciliation.

The system transaction PDF table spans pages or has headers/footers

Cause: PDF detail reports may have pagination, repeated headers, or footer annotations, which affect AI's reading of the table data.

Solution: Add "ignore headers/footers and repeated header rows, only read the table data rows" to the instruction.

The same amount appears multiple times on the same day, causing mismatches

Cause: When reconciling by the "date + amount" combination, there may be multiple transactions with the same amount on the same day, making the exact correspondence impossible to determine.

Solution: Prefer precise reconciliation by statement number; if there is really no statement number, you can require in the instruction to "mark records that cannot be matched one-to-one on the same day as 'Amount mismatch'".

Get a SpireToken Key

• Contact sales@e-iceblue.com or visit https://www.e-iceblue.com/TemLicense.html to obtain a trial/commercial API key

Configure it in code:

AIOptions options = new AIOptions();
options.SpireToken = key;