Skip to main content

Managing Local Files

This guide explains how to use Coginiti's Local Files feature to manage and work with your local file directory within the application. The Local Files feature enables you to open, edit, and save queries and data files in one centralized location, streamlining your data analysis workflow.

Overview​

Coginiti's Local Files feature provides seamless integration between your local file system and the application, allowing you to:

  • Access local directories directly from within Coginiti
  • Open and edit SQL files in the integrated code editor
  • View CSV and Parquet files in the result grid
  • Query data files directly without importing
  • Save query results to local files
  • Manage file organization through the integrated file browser

Key Benefits​

Unified Workflow: Work with local files without leaving Coginiti Direct File Access: No need to import files for analysis Real-time Synchronization: Reflect local file system changes instantly Multiple File Format Support: Handle SQL, CSV, Parquet, and other formats Integrated Editing: Edit SQL files with full IDE features

Setting Up Local File Access​

Step 1: Locate the Files Tab​

  1. Find the Files tab on the left side of the Coginiti interface
  2. The tab appears in the main navigation panel alongside other features
  3. Click the Files tab to access the local files interface

Step 2: Add Your Local File Directory​

  1. Click "Open Folder" to add your local file directory
  2. A file browser dialog will appear allowing you to navigate your file system
  3. Select the folder you want to add and access from within Coginiti
  4. Click "Confirm" or "Select Folder" to add the directory

Step 3: Verify Directory Access​

  1. Your selected folder will now appear in the Files tab
  2. Expand the folder structure to see subdirectories and files
  3. Browse through your files using the integrated file explorer
  4. Verify file types are properly recognized and displayed
Directory Selection

Choose a directory that contains your frequently used SQL files, data files, and project folders for the most efficient workflow. You can add multiple directories if needed.

Working with Data Files​

Opening CSV Files​

CSV files can be opened directly in Coginiti's Result Grid for immediate viewing and analysis:

Step 1: Access CSV File​

  1. Navigate to your CSV file in the Files tab
  2. Double-click on the CSV file to open it
  3. The file opens automatically in the Result Grid

Step 2: View and Analyze Data​

  1. Data displays in a tabular format in the Result Grid
  2. Column headers are automatically detected and displayed
  3. Scroll through data to explore the dataset
  4. View data types and column information

Step 3: Query CSV Data​

  1. Write SQL queries against the CSV file directly
  2. Use the file name as the table reference in your queries
  3. Click "Reload Data" to execute queries against the file
  4. Make adjustments to your queries as needed

Example CSV Query:

SELECT 
    column1,
    column2,
    COUNT(*) as record_count
FROM your_file.csv
WHERE column1 IS NOT NULL
GROUP BY column1, column2
ORDER BY record_count DESC

Opening Parquet Files​

Parquet files are handled similarly to CSV files with optimized performance for columnar data:

Step 1: Access Parquet File​

  1. Navigate to your Parquet file in the Files tab
  2. Double-click on the Parquet file to open it
  3. The file loads in the Result Grid with column metadata

Step 2: Explore Parquet Data​

  1. Column types are automatically detected from Parquet metadata
  2. Data compression is handled transparently
  3. Complex data types (arrays, structs) are displayed appropriately
  4. Schema information is available for reference

Step 3: Query Parquet Data​

  1. Write queries using standard SQL syntax
  2. Leverage column pruning for efficient queries
  3. Use predicate pushdown for better performance
  4. Handle nested data structures with appropriate SQL functions

Example Parquet Query:

SELECT 
    customer_id,
    order_date,
    SUM(order_amount) as total_amount
FROM sales_data.parquet
WHERE order_date >= '2024-01-01'
GROUP BY customer_id, order_date
HAVING total_amount > 1000

Supported File Formats​

Data Files​

  • CSV: Comma-separated values with customizable delimiters
  • Parquet: Columnar storage format with schema preservation
  • JSON: JavaScript Object Notation files (where supported)
  • TSV: Tab-separated values files
  • Excel: Spreadsheet files (XLSX, XLS) in supported versions

Query Files​

  • SQL: Standard SQL query files with syntax highlighting
  • CoginitiScript: Coginiti-specific script files with macro support
  • Text: Plain text files for documentation and notes

Working with SQL Files​

Opening SQL Files in Code Editor​

SQL files integrate seamlessly with Coginiti's code editor:

Step 1: Open SQL File​

  1. Navigate to your SQL file in the Files tab
  2. Double-click on the SQL file to open it
  3. The file opens in the Code Editor with full IDE features

Step 2: Edit and Execute​

  1. Syntax highlighting is automatically applied
  2. Code completion and IntelliSense features are available
  3. Execute queries directly from the editor
  4. Save changes back to the local file

Step 3: File Management​

  1. Create new SQL files directly in the editor
  2. Save as different file names for version control
  3. Organize files in folders for project management
  4. Track file modifications with unsaved changes indicators

Code Editor Features​

Advanced Editing​

  • Syntax highlighting for SQL and CoginitiScript
  • Auto-completion for SQL keywords and functions
  • Bracket matching and code folding
  • Multi-cursor editing for bulk changes
  • Find and replace with regex support

Query Execution​

  • Execute selected text or entire file
  • View query results in integrated result grid
  • Export results to various formats
  • Query history and result caching

File Operations​

  • Auto-save functionality (if enabled)
  • File change detection and reload prompts
  • Version comparison and diff viewing
  • Integration with version control systems

Data Analysis Workflows​

Direct File Querying​

Query data files without importing them into a database:

CSV Analysis Workflow​

  1. Open CSV file in Result Grid
  2. Write exploratory queries to understand the data structure
  3. Apply filters and aggregations to analyze specific subsets
  4. Use statistical functions for data profiling
  5. Save transformed results to new files or tables

Parquet Processing Workflow​

  1. Load Parquet file with automatic schema detection
  2. Leverage columnar efficiency for analytical queries
  3. Handle complex nested data with appropriate SQL functions
  4. Process large datasets with optimized query execution
  5. Export processed results in various formats

Save to Table Feature​

Convert query results into persistent tables:

Step 1: Execute Query​

  1. Run your query against the local file
  2. Review the results in the Result Grid
  3. Ensure data quality and expected output

Step 2: Save Results​

  1. Click "Save to Table" in the Result Grid
  2. Choose destination database connection
  3. Specify table name and schema
  4. Configure data types and constraints as needed

Step 3: Table Configuration​

  1. Select primary keys if applicable
  2. Set up indexes for performance optimization
  3. Configure table permissions and access controls
  4. Verify table creation and data loading

File Export and Import​

Export Query Results​

  • CSV export: Comma-separated format for spreadsheet applications
  • Excel export: Native Excel format with formatting preservation
  • JSON export: Structured data format for API integration
  • Parquet export: Efficient columnar format for big data workflows

Import from External Sources​

  • Database export: Import query results from database connections
  • API data: Import data from REST API responses
  • Cloud storage: Import files from object store connections
  • Clipboard import: Paste data directly from clipboard

File Management and Organization​

Refreshing Local Files​

Keep your file view synchronized with local file system changes:

Manual Refresh​

  1. Click the refresh button (🔄) in the Files tab
  2. All folders and files are rescanned for changes
  3. New files appear immediately in the file browser
  4. Deleted files are removed from the view
  5. Modified files show updated timestamps

Automatic Refresh​

  • File system monitoring detects changes in real-time (where supported)
  • Auto-refresh intervals can be configured in settings
  • Change notifications alert you to external modifications
  • Conflict resolution for files modified both locally and in Coginiti

File Organization Best Practices​

Directory Structure​

Projects/
├── project-alpha/
│   ├── queries/
│   │   ├── analysis.sql
│   │   └── reports.sql
│   ├── data/
│   │   ├── source.csv
│   │   └── processed.parquet
│   └── results/
│       └── output.xlsx
├── project-beta/
│   ├── scripts/
│   └── datasets/
└── shared/
    ├── common-queries/
    └── reference-data/

Naming Conventions​

  • Use descriptive names: customer_analysis_2024.sql instead of query1.sql
  • Include dates: sales_report_2024_01.csv for time-based data
  • Version control: analysis_v1.sql, analysis_v2.sql for iterations
  • Consistent formatting: Use underscores or hyphens consistently

File Type Organization​

  • Separate query files from data files for clarity
  • Group related files in project-specific directories
  • Maintain result archives in dedicated output folders
  • Use staging areas for temporary or work-in-progress files

Advanced Features​

Multi-Directory Support​

Add and manage multiple local directories:

Adding Multiple Directories​

  1. Click "Open Folder" multiple times to add additional directories
  2. Each directory appears as a separate root in the Files tab
  3. Switch between directories by expanding/collapsing them
  4. Organize by purpose: projects, data sources, archives

Directory Management​

  • Remove directories that are no longer needed
  • Rename directory labels for better organization
  • Set directory priorities for default access
  • Configure directory-specific settings

Integration with Database Connections​

Combine local files with database queries:

File-to-Database Workflows​

  1. Analyze local files to understand data structure
  2. Create database tables based on file schemas
  3. Import file data into database tables
  4. Join file data with existing database tables
  5. Export combined results back to local files

Database-to-File Workflows​

  1. Execute database queries to extract data
  2. Export results to local files for offline analysis
  3. Share results through local file system
  4. Archive historical data in local file formats
  5. Backup critical datasets to local storage

Collaboration Features​

Share and collaborate using local files:

Version Control Integration​

  • Git integration for SQL file version control
  • Change tracking for collaborative development
  • Merge conflict resolution for concurrent edits
  • Branch management for feature development

File Sharing​

  • Network drive support for team file sharing
  • Cloud storage synchronization with services like Dropbox, Google Drive
  • Export sharing through standardized file formats
  • Documentation generation from query comments and metadata

Troubleshooting​

Common Issues​

Files Not Appearing​

Symptoms: Selected directory doesn't show files or shows empty

Solutions:

  1. Check directory permissions: Ensure Coginiti has read access to the directory
  2. Verify directory path: Confirm the selected directory exists and is accessible
  3. Refresh the view: Click the refresh button to rescan the directory
  4. Check file filters: Ensure file type filters aren't hiding your files

Cannot Open Files​

Symptoms: Double-clicking files doesn't open them

Solutions:

  1. Verify file format support: Ensure the file type is supported by Coginiti
  2. Check file permissions: Confirm the file isn't locked or read-only
  3. Validate file integrity: Ensure the file isn't corrupted
  4. Restart application: Close and reopen Coginiti to reset file handlers

Query Execution Errors​

Symptoms: Cannot query CSV or Parquet files

Solutions:

  1. Check file format: Ensure CSV has proper headers and delimiter formatting
  2. Validate data types: Verify data types are compatible with SQL operations
  3. Review file size: Large files may require different handling approaches
  4. Check file encoding: Ensure files use UTF-8 or compatible encoding

Performance Optimization​

Large File Handling​

  • Use Parquet format for large datasets to improve query performance
  • Implement file chunking for processing very large CSV files
  • Consider database import for frequently queried large files
  • Use column selection to reduce memory usage

Query Optimization​

  • Add WHERE clauses to filter data early in processing
  • Use appropriate indexes when saving to database tables
  • Leverage column pruning in Parquet files
  • Implement query result caching for repeated operations

Security Considerations​

File Access Security​

Local File Permissions​

  • Set appropriate file permissions on directories containing sensitive data
  • Use operating system access controls to restrict directory access
  • Implement file encryption for sensitive data files
  • Monitor file access through system audit logs

Data Protection​

  • Avoid storing credentials in plain text SQL files
  • Use parameterized queries instead of hardcoded sensitive values
  • Implement data masking for development and testing files
  • Regular security audits of file access patterns

Compliance Considerations​

Data Governance​

  • Document data sources and file lineage
  • Implement retention policies for temporary and working files
  • Track data modifications and version history
  • Maintain audit trails for compliance reporting

Privacy Protection​

  • Handle PII data according to privacy regulations
  • Implement data anonymization for shared files
  • Use secure file deletion for sensitive data removal
  • Regular compliance reviews of file handling practices

Best Practices​

File Management​

Organization​

  • Use consistent naming conventions across all projects
  • Maintain clean directory structures with logical hierarchies
  • Regular file cleanup to remove obsolete files
  • Document file purposes and data sources

Version Control​

  • Use version numbers or dates in file names
  • Maintain change logs for important query files
  • Backup critical files regularly
  • Test restored files to ensure integrity

Workflow Efficiency​

Query Development​

  • Start with small data samples for query development
  • Use comments to document complex query logic
  • Modularize queries into reusable components
  • Test queries thoroughly before production use

Data Processing​

  • Validate data quality before analysis
  • Document data transformations and business logic
  • Use staging files for intermediate processing steps
  • Implement error handling for data processing workflows

Summary​

You have successfully configured Local Files management in Coginiti! Key achievements:

✅ Directory Integration: Seamless access to local file directories within Coginiti ✅ Multi-Format Support: Direct querying of CSV, Parquet, and other data files ✅ SQL File Management: Integrated editing and execution of SQL query files ✅ Data Analysis Workflows: Efficient file-to-database and database-to-file processes ✅ File Organization: Best practices for directory structure and file management ✅ Performance Optimization: Techniques for handling large files and complex queries

Your Coginiti instance now provides comprehensive local file integration, enabling efficient data analysis workflows that bridge local file systems with cloud databases and object storage.