View source for Z functions with CSV and XLSX files

= Z Functions with CSV and XLSX Files =

'''Z Functions''' are specialized computational functions within the [[Z3|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 ===
[[File:Csv file.png|thumb]]
[[File:Return.png|thumb]]
==== READCSV() ====
The primary function for importing CSV data into ZCubes:

<syntaxhighlight lang="z^3">
csv_data = READCSV("D:/ZAP_Testing/data.csv")
</syntaxhighlight>

'''Parameters:'''
* File path (string): Full path to the CSV file

'''Output Value:''' Structured data object with column and row.

=== XLSX File Functions ===
[[File:Xslx file.png|thumb]]
[[File:Sheetjs 1.png|thumb]]
[[File:Sheet js 2.png|thumb]]
==== Method 1: SheetJS with File Input ====
Primary function for importing Excel files using SheetJS library:

<syntaxhighlight lang="javascript">
/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);
</syntaxhighlight>

[[File:Xlsx.png|thumb]]
==== Method 2: Using xlsx library ====
Using xlsx library method:

<syntaxhighlight lang="javascript">
const XLSX = require('xlsx');
const workbook = XLSX.readFile('D:/ZAP_Testing/data.xlsx');
</syntaxhighlight>
[[File:Drag and drop.png|thumb]]
==== 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 ===
[[File:P4.png|thumb]]
==== Column Access and Analysis ====
<syntaxhighlight lang="z^3">
// Extract specific columns
salary_column = [csv_data.column(4);]
total_salary = SUM(csv_data.column(4))
PRINT(total_salary);
</syntaxhighlight>

<pre>
770000
</pre>

==== Statistical Functions ====
1)
<syntaxhighlight lang="z^3">
COUNT(actual_ages)
</syntaxhighlight>
Output:
<pre>
11
</pre>
2)
[[File:P5.png|thumb]]
<syntaxhighlight lang="z^3">
AVG(actual_ages)
</syntaxhighlight>
Output:
<pre>
77000
</pre>

== Data Manipulation operations using z^3 function ==

=== Salary Distribution Processing ===
[[File:2.png|thumb]]
<syntaxhighlight lang="z^3">
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);
</syntaxhighlight>

Output:
<pre>
[["Executive",210000,70000],["Management",230000,76666.66666666667],["Entry Level",SUM(entry_level),AVG(entry_level)]]
</pre>

=== CSV Data Sampling and Performance Analysis ===
[[File:89.png|thumb]]
<syntaxhighlight lang="z^3">
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]
</syntaxhighlight>

Output:
<pre>
[[["salary"],["60000"],["65000"],["72000"]],[],[]]
</pre>

=== Advanced Statistical Operations ===
<syntaxhighlight lang="z^3">
// 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);
</syntaxhighlight>

Output:
<pre>
Total Employees: 12
Average Salary: 64166.67
</pre>

=== XLSX File Processing with Z Functions Integration ===
<syntaxhighlight lang="javascript">
// 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;
}
</syntaxhighlight>

== See Also ==

* [[Z3]]
* [[# Operator]]
* [[File Formats]]
* [[Data Processing]]
* [[JavaScript]]
* [[SheetJS]]

[[Category:Z3]]
[[Category:Data processing]]
[[Category:File formats]]
[[Category:Programming functions]]
[[Category:Statistical computing]]
[[Category:JavaScript libraries]]