ZCubes Wiki

Z functions with CSV and XLSX files

Z Functions with CSV and XLSX Files

Z Functions are specialized computational functions within the ZCubes platform that provide powerful data manipulation and analysis capabilities for structured file formats, particularly CSV (Comma-Separated Values) and XLSX (Excel) files. These functions enable seamless data import, processing, and analysis within ZCubes' multi-language computational environment.

Overview

Z Functions represent a core component of ZCubes' data processing capabilities, designed to handle the most common structured data formats used in business, research, and educational contexts. The integration supports both CSV and XLSX formats with comprehensive functionality for data import, manipulation, transformation, and analysis.

The Z Functions framework provides a unified interface for working with tabular data, regardless of the source format, enabling users to perform complex data operations without switching between different tools or environments.

Core Z Functions for File Operations

CSV File Functions

 
 

READCSV()

The primary function for importing CSV data into ZCubes: 

csv_data = READCSV("D:/ZAP_Testing/data.csv")

Parameters:

  • File path (string): Full path to the CSV file

Output Value: Structured data object with column and row.

XLSX File Functions

 
 
 

Method 1: SheetJS with File Input

Primary function for importing Excel files using SheetJS library: 

/const XLSX = require("xlsx");

function readExcelFile(filePath) {
    try {
        const workbook = XLSX.readFile(filePath);
        
        console.log("Available sheets:", workbook.SheetNames);
        
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];
        
        const jsonData = XLSX.utils.sheet_to_json(worksheet);
        
        return jsonData;
    } catch (error) {
        console.error("Error reading Excel file:", error);
        return null;
    }
}

const data = readExcelFile("D:/ZAP_Testing/data.xlsx");
console.log("Excel data:", data);
 

Method 2: Using xlsx library

Using xlsx library method: 

const XLSX = require('xlsx');
const workbook = XLSX.readFile('D:/ZAP_Testing/data.xlsx');
 

Method 3: Drag and Drop Implementation

Complete drag-and-drop Excel with Z: Just simplely drag the file and drop it into the ZAP Environemt.












Data Manipulation Functions

Column Operations

 

Column Access and Analysis

// Extract specific columns
salary_column = [csv_data.column(4);]
total_salary = SUM(csv_data.column(4))
PRINT(total_salary);
770000

Statistical Functions

1) 

COUNT(actual_ages)

Output: 

11

2)

  
AVG(actual_ages)

Output: 

77000

Data Manipulation operations using z^3 function

Salary Distribution Processing

  
c_suite_salaries = salary_column#[3,7,9]
mid_management = salary_column#[1,4,6]
entry_level = salary_column#[0,2,5]

salary_distribution = [
    ["Executive", SUM(c_suite_salaries), AVG(c_suite_salaries)],
    ["Management", SUM(mid_management), AVG(mid_management)],
    ["Entry Level", SUM(entry_level), AVG(entry_level)]
]

PRINT(salary_distribution);

Output: 

[["Executive",210000,70000],["Management",230000,76666.66666666667],["Entry Level",SUM(entry_level),AVG(entry_level)]]

CSV Data Sampling and Performance Analysis

  
CSV_data = 
READCSV("D:/ZAP_Testing/data.csv")

// Creating Salary samples for analysis
sample_indices = 5#[0,2]
sampled_salaries = 
salary_column#sample_indices
sampled_names = 
csv_data.column(0)#sample_indices

salary_bands = 4#[60000,10000]
performance_tiers = salary_column#[0,4,7,9]

Output: 

[[["salary"],["60000"],["65000"],["72000"]],[],[]]

Advanced Statistical Operations

// Filter employees by age criteria
people_below_30 = csv_data|x[1]<30|
actual_ages = people_below_30.column(1)
actual_names = people_below_30.column(0)

// Calculate comprehensive statistics
total_employees = COUNT(csv_data)
average_salary = AVG(salary_column)

PRINT("Total Employees: " + total_employees);
PRINT("Average Salary: " + average_salary);

Output: 

Total Employees: 12
Average Salary: 64166.67

XLSX File Processing with Z Functions Integration

// Z Functions compatible XLSX processing
function processXLSXWithZFunctions(xlsxData) {
    // Apply Z Functions-style operations
    const processed = {
        originalData: xlsxData,
        rowCount: xlsxData.length,
        columnCount: xlsxData.length > 0 ? Object.keys(xlsxData[0]).length : 0,
        summary: {}
    };
    
    // Statistical analysis similar to Z Functions
    if (xlsxData.length > 0) {
        const columns = Object.keys(xlsxData[0]);
        
        columns.forEach(column => {
            const values = xlsxData.map(row => row[column])
                .filter(val => !isNaN(val) && val !== null);
            
            if (values.length > 0) {
                const numericValues = values.map(Number);
                processed.summary[column] = {
                    count: numericValues.length,
                    sum: numericValues.reduce((a, b) => a + b, 0),
                    avg: numericValues.reduce((a, b) => a + b, 0) / numericValues.length,
                    min: Math.min(...numericValues),
                    max: Math.max(...numericValues)
                };
            }
        });
    }
    
    return processed;
}

See Also

Retrieved from "http://wiki.zcubes.com/index.php?title=Z_functions_with_CSV_and_XLSX_files&oldid=217793"