About Universal Electronic Component Data Search

Description

The Universal Electronic Component Data Search application is designed to streamline and automate working with Bill of Materials (BOM) Excel files. It integrates multiple functionalities to provide comprehensive component information, leveraging both external APIs and an internal database for optimized performance and cost-effectiveness.

Key Features:

  • Enrich Descriptions:
    • Utilizes advanced AI models (via OpenRouter) to search the web for component specifications based on Part Number and original description.
    • Formats the found specifications into a standardized description string following predefined rules for various component types (Resistors, Capacitors, Connectors, ICs, Mechanical parts, etc.).
    • Caches enriched descriptions and source URLs in the internal SQLite database to avoid redundant external searches for previously processed components.
  • Find Prices, Availability, Images & More:
    • Connects to the Nexar API (which aggregates data from sources like Octopart) to fetch pricing, stock levels, lead times, seller information, component images, and basic descriptions.
    • Prioritizes data from authorized distributors.
    • Stores fetched Nexar data (prices, stock, image URLs, availability details) in the internal database, acting as a cache to reduce API calls for subsequent requests of the same component.
  • Find Datasheets:
    • Performs targeted web searches using specialized search APIs (like Serper for direct PDF links) and general AI models (like Perplexity via OpenRouter) to locate datasheet URLs.
    • Checks the internal database first for previously found datasheet links.
    • Saves successfully found datasheet URLs and their source (API or Cache) into the database.
    • Downloads and stores PDF datasheets locally in the storage/datasheets directory.
  • Database Caching:
    • An integrated SQLite database (data/database.sqlite) stores component details, enriched descriptions, Nexar data, and datasheet URLs.
    • All search functions (Enrich, Prices, Datasheets) query the local database first before accessing external APIs for repeated searches.
  • User-Friendly Interface:
    • Allows uploading Excel files (.xls, .xlsx).
    • Provides selection for the specific sheet and relevant columns (Part Number, Description, Manufacturer).
    • Displays real-time processing progress and previews of found data (descriptions, prices, datasheets).
    • Offers download functionality for the processed Excel files and datasheets.
    • Includes a side panel showing previously processed files stored on the server, organized by types (Enrich, Price, Data, PDFs).

This application aims to enhance productivity by automating data collection and standardization for electronic components within BOMs.

Process Workflow for Each Function

1. Enrich Description (Main Page)

  1. Upload Excel File: Click "Choose File" and select your BOM Excel file.
  2. Select Sheet: Choose the appropriate sheet from your Excel file.
  3. Select Columns: Specify which columns contain Part Numbers and Descriptions (automatically detected in most cases).
  4. Start Processing: Click "Start Processing" button to begin enrichment.
  5. View Progress: Watch real-time updates as each component is processed:
    • The progress bar shows overall completion percentage.
    • The counter displays the number of processed rows.
    • The table shows original descriptions and enriched results with source links.
  6. Database Interaction: The system checks if the part number already exists in the database:
    • If found, it retrieves the cached description (marked as "From Cache").
    • If not found, it queries external AI services and saves the result to the database.
  7. Download Results: When processing completes, click "Download processed file" to get your enriched Excel file.

2. Find Prices

  1. Upload Excel File: Click "Choose File" and select your BOM Excel file.
  2. Select Sheet: Choose the appropriate sheet from your Excel file.
  3. Select Part Number Column: Specify which column contains Part Numbers (automatically detected in most cases).
  4. Start Processing: Click "Start Processing" button to begin price search.
  5. View Progress: Watch real-time updates as pricing data is retrieved:
    • The progress bar shows overall completion percentage.
    • The counter displays the number of processed parts.
    • The table shows part numbers, manufacturers, prices, sellers, stock availability and more.
  6. Database Interaction: The system checks if pricing data already exists in the database:
    • If found and recent, it uses cached data (marked as "From DB").
    • If not found or outdated, it queries Nexar API and saves the results to the database.
  7. Download Results: When processing completes, click "Download processed file" to get your Excel with pricing data.

3. Find Datasheets

  1. Upload Excel File: Click "Choose File" and select your BOM Excel file.
  2. Select Sheet: Choose the appropriate sheet from your Excel file.
  3. Select Part Number Column: Specify which column contains Part Numbers (automatically detected in most cases).
  4. Start Processing: Click "Start Processing" button to begin datasheet search.
  5. View Progress: Watch real-time updates as datasheets are found:
    • The progress bar shows overall completion percentage.
    • The counter displays the number of processed parts.
    • The table shows part numbers and datasheet links as they're found.
  6. Database Interaction: The system checks if datasheet URLs already exist in the database:
    • If found, it retrieves the cached URL (marked as "From Cache").
    • If not found, it queries search APIs to find datasheet links and saves them to the database.
  7. Download Options:
    • Download the results spreadsheet with all datasheet links.
    • Click on checkboxes next to datasheets you want to download, then click "Download Selected Datasheets" to get PDFs.
  8. PDF Storage: Downloaded datasheets are stored in the storage/datasheets directory and can be accessed through the PDFs tab while the host provides the required writable filesystem.

File Storage System

  • Processed Excel Files: Stored in the storage/results directory with timestamps.
  • PDF Datasheets: Stored in the storage/datasheets directory named by part number.
  • Side Panel Organization: The side panel shows files sorted into four tabs:
    • Enrich: Files from the description enrichment process.
    • Price: Files containing price lookup results.
    • Data: Files from datasheet search (contains links).
    • PDFs: Downloaded datasheet PDF files.
  • Automatic Cleanup: Files older than approximately 2 weeks are automatically cleaned up from storage/results to manage disk space.
  • Host Storage: Local cache, result files, and downloaded datasheets are available only while the host provides the required writable filesystem.

Database Structure

The SQLite database (data/database.sqlite) contains several key tables:

  • components: Stores basic component information including part numbers and manufacturers.
  • descriptions: Stores enriched descriptions with source URLs and timestamps.
  • prices: Stores pricing data, stock availability, and seller information from Nexar.
  • datasheets: Stores datasheet URLs, local file paths (if downloaded), and source information.

This database acts as a cache and reduces external API calls for previously processed components.

Practical Benefits for Users

  • Access Stored Files While Available:
    • Processed Excel files can be accessed through the side panel while the host provides the required writable filesystem.
    • Files in storage/results are subject to the approximately two-week cleanup described above.
    • Each file is timestamped, so you can track when the data was collected and decide if it needs updating.
  • Reuse Cached Results:
    • When processing a BOM with components searched before, the system may use cached data instead of making another external API lookup.
    • You'll see "From Cache" or "From DB" in the results table when this happens.
  • Local Component Records:
    • Processed component details, descriptions, prices, and datasheet URLs are stored in the local SQLite database at data/database.sqlite while the host provides the required writable filesystem.
    • External API lookups still require an internet connection.
  • Downloaded Datasheets:
    • Downloaded datasheets are stored in storage/datasheets and appear in the PDFs tab while the host provides the required writable filesystem.
    • Internet is required for external API lookups used to find new datasheet links.

Technical Requirements

  • Browser: Latest versions of Google Chrome, Mozilla Firefox, Microsoft Edge
  • Internet Connection: Stable connection required for API interaction and file uploads
  • Device: Computer or laptop with sufficient RAM for processing large files

Conclusion

The Universal Electronic Component Data Search provides a comprehensive suite of tools for working with electronic component BOMs. By combining external APIs with local database caching, it offers efficient and cost-effective solutions for standardizing descriptions, finding pricing data, and locating datasheets for electronic components.