# xlwings Lite Data Importer Import any file via cloud links into xlwings Lite. Token access for private files. DuckDB conversion or raw download. - Download xlwings Lite Data Importer: https://www.tigzig.com/files/xlwings/XLWINGS_LITE_DATA_IMPORTER_V2_17JAN2025_C.xlsx ## How It Works Your Files CSV, Parquet, DuckDB SQLite, JSON PDF, Images, ZIP Any file type upload Cloud Storage Google Drive Dropbox GitHub DuckIt (custom) Get shareable link import xlwings Lite Paste URL -> Auto-detect Load into DuckDB Run SQL in Excel Or raw download as-is Tested 1.2 GB Token Access Auto DuckDB Raw Import SQL Ready 20M+ Rows Upload any file to cloud -> paste link in xlwings Lite -> analyze with SQL or download raw ## Shareable Link Access (Simple Setup) ### What It Does Import data files using shareable URLs. Paste any public or shareable link and import data files directly for SQL analytics. - **Cloud sources:** GitHub Releases, Google Drive, Dropbox shareable links - **DuckIt URLs:** shareable links from the DuckIt App - **Supported files:** Parquet, DuckDB, SQLite, CSV, TSV, pipe-delimited, JSON - Uses a CORS proxy for cross-origin downloads - Handles large files (tested up to 1.5 GB) - Intelligent file-type and delimiter auto-detection - All files pushed into DuckDB for unified SQL analytics #### Supports Raw File Import Import any file type as-is (PDF, images, ZIP, etc.) without data processing. See Raw File Import section below. ### How to Use - **Get Your File URL** **GitHub:** use raw.githubusercontent.com or Releases - **Google Drive:** share file, copy link - **Dropbox:** share file, copy link - **DuckIt:** convert CSV to Parquet/DuckDB, get the shareable URL - **Open the Workbook** Install the xlwings Lite add-in in Excel - Open the xlwings Lite Data Importer workbook - Go to SHARE_LINK_ACCESS sheet - Paste URL in cell B5 - **Run Import** Run `import_via_sharelink` function - View schema and sample data - Query with DuckDB SQL #### Privacy Note Shareable Link Access uses my public CORS proxy (test/demo purposes only). Server logs capture file names for error tracking. For production or sensitive data, use Token Access below for fully private downloads, or deploy your own secured proxy (guide below). ## Token Access (Fully Private) ### What It Does Import files directly from your private cloud storage using OAuth tokens or Personal Access Tokens. Data never passes through a proxy - fully private. - **Dropbox:** OAuth refresh token - direct API access - **Google Drive:** OAuth refresh token - direct API access - **GitHub:** Personal Access Token (PAT) - via auth proxy for private repos - **Supported files:** same as above - Parquet, DuckDB, SQLite, CSV, TSV, JSON - One-time token setup, then just specify file path - Tokens stored as environment variables in xlwings Lite ### How to Use - **One-Time Setup** **Dropbox:** get refresh token, app key, app secret - **Google Drive:** get refresh token, client ID, client secret - **GitHub:** generate Personal Access Token with repo scope - Add tokens to xlwings Lite environment variables - **Configure Import** Go to TOKEN_ACCESS sheet - B5: select provider (Dropbox, Google Drive, GitHub) - B6: enter file path or ID - B7: auth proxy URL (GitHub only) - **Run Import** Run `import_via_token` function - View schema and sample data - Query with DuckDB SQL #### Setup Guide See the OAuth Setup Guide section below for step-by-step token generation instructions. Token-based access pattern inspired by [Ruud van der Ham's](https://www.linkedin.com/in/ruudvanderham/) [xlwings_utils](https://github.com/salabim/xlwings_utils) library. ## Raw File Import ### What It Does Both Shareable Link and Token Access support raw file import. This mode downloads any file type as-is without pushing it into DuckDB. - **Any file type:** PDF, images (PNG, JPG), ZIP archives, Excel files, etc. - **Data files too:** CSV, JSON, SQLite - if you want to work with them directly instead of converting to DuckDB - File saved to temp directory with your specified filename - Output path shown in the sheet for easy access - Test functions available to verify imported files (image dimensions, PDF info, ZIP contents) ### For Shareable Link Access - B5: enter URL - B7: set to `1` (raw mode flag) - B8: enter filename with extension (e.g. report.pdf) - Run `import_raw_sharelink` - D8: shows saved file path ### For Token Access - B5: select provider - B6: enter file path/ID - B9: set to `1` (raw mode flag) - B10: enter filename with extension - Run `import_raw_token` - D10: shows saved file path ## Technical Architecture ### Shareable Link Access - Runs Python via xlwings Lite (Pyodide/WebAssembly) - Universal URL routing: auto-detects GitHub, Google Drive, Dropbox - CORS bypass via Cloudflare Worker proxy (GitHub Releases, Google Drive, Dropbox) - Direct fetch for raw.githubusercontent.com and signed URLs - Multi-level file type detection: URL extension -> Content-Disposition header -> magic bytes (supports extensionless files) - Intelligent delimiter detection: verifies actual delimiter from content for CSV/TSV/pipe files (warns when extension does not match content) - SQLite auto-conversion: detects SQLite files via magic bytes, reads with Python's sqlite3, converts to DuckDB format - JSON/NDJSON auto-detection: detects via magic bytes, DuckDB's read_json_auto handles arrays and newline-delimited formats - Text files / CSV/JSON: auto-import pushes data directly into DuckDB via read_csv_auto/read_json_auto - Parquet: stays as Parquet - DuckDB Python integration for unified analytics across all file types - Displays schema, sample data, and statistics ### Token Access - Private Cloud Storage - Dropbox: OAuth refresh token -> exchanges for access token -> direct Dropbox API download (no proxy) - Google Drive: OAuth refresh token -> exchanges for access token -> direct Google Drive API download (no proxy) - GitHub: Personal Access Token (PAT) -> auth proxy for private repo releases (PAT forwarded securely) - Tokens stored as xlwings Lite environment variables (not in workbook) - One-time setup, then just specify file path - tokens auto-refresh - Same file type detection and DuckDB conversion as Shareable Link Access ### Raw File Import - Available for both Shareable Link and Token Access - Downloads file as-is without DuckDB conversion - Supports any file type: PDF, images, ZIP, Excel, or data files - User specifies output filename with extension - Saves to browser temp directory, path shown in sheet ## What is CORS & How to Set Up Your Own Cloudflare Proxy #### What is CORS? (Simple Explanation) **The situation:** You want to download a file from Google Drive (or GitHub, Dropbox) into your browser app. **Two ways to download files:** - **From a terminal** (using `curl`) - this always works - **From inside a browser** (JavaScript code) - this has restrictions **The restriction:** When code running inside a browser tries to fetch data from another website (like Google Drive), Google must explicitly say "yes, browsers are allowed to access this" via the header `Access-Control-Allow-Origin: *`. **The problem:** Many services (GitHub Releases, Google Drive, Dropbox) do not include this header. So even though the data is sent to your browser, your browser refuses to show it. **The solution - a proxy:** Instead of fetching directly from Google Drive, we fetch from a middleman (proxy). The proxy fetches the file (which always works), then sends it to your browser with the proper CORS headers. CORS issues are extremely common when building browser-based apps. A Cloudflare Worker is a free, simple way to create your own proxy. ### Setup Your Own CORS Proxy This guide helps you set up your own Cloudflare Worker as a CORS proxy. This allows downloading files from GitHub, Google Drive, and Dropbox directly in the browser. **How to use:** - Follow the guide manually step-by-step - Copy entire document -> paste to AI coder -> AI sets it up for you **What is included:** - Complete Worker code for public and auth proxies - Deployment instructions (CLI and Dashboard) - AI assistant instructions (built-in) Works with a free Cloudflare account. No custom domain required - uses the free workers.dev subdomain. Full guide: [CLOUDFLARE_WORKER_SETUP_GUIDE.txt](/files/xlwings/text-guides/CLOUDFLARE_WORKER_SETUP_GUIDE.txt) ## What are OAuth Tokens & How to Set Them Up #### What are OAuth Tokens? (Simple Explanation) **The goal:** Access your private files on Google Drive or Dropbox programmatically, without entering your password each time. **Tokens are just text strings** - long garbled characters that act as keys. Instead of username/password, you give the app these tokens and it can access your files. **Google/Dropbox require three pieces:** - **Client ID + Client Secret** - identifies your app - **Refresh Token** - proves you authorized that app to access your files **Why three pieces instead of one?** Security through separation. Harder for someone to get all three together; easier to revoke one app's access without affecting others. **GitHub is simpler:** just one Personal Access Token (PAT). You generate it once, use it everywhere. **My take:** for individual users and small teams, OAuth feels like overkill. GitHub's single PAT approach is simpler. OAuth makes sense if you are building apps for hundreds of users with fine-grained revocation needs. But it is what Google and Dropbox provide - so we work with it. ### Setup Guide for Private File Access This guide helps set up secure OAuth tokens for downloading private files from Dropbox, Google Drive, or GitHub directly into xlwings Lite. **How to use:** - Follow the guide manually step-by-step - Copy entire document -> paste to AI coder -> AI guides you through each step **What is included:** - Step-by-step setup for Dropbox, Google Drive, GitHub - Python scripts for token generation - Troubleshooting guide - AI assistant instructions (built-in) Full guide: [TOKEN_ACCESS_SETUP_GUIDE.txt](/files/xlwings/text-guides/TOKEN_ACCESS_SETUP_GUIDE.txt) ## Resources - [xlwings Lite](https://lite.xlwings.org) - Official website with installation instructions - [xlwings Documentation](https://docs.xlwings.org/en/latest/) - Full documentation and API reference - [Cloudflare Workers (CORS proxy)](https://github.com/amararun/xlwings-lite-apps-codes-docs/tree/main/cloudflare-workers) - CORS proxy code for browser-based downloads from GitHub, Google Drive, Dropbox - [xlwings_utils by Ruud van der Ham](https://github.com/salabim/xlwings_utils) - OAuth token patterns for Dropbox, Google Drive, GitHub - basis for Token Access - [xlwings Lite Apps & Codes](https://github.com/amararun/xlwings-lite-apps-codes-docs) - All xlwings Lite scripts, remote modules, and documentation in one repo - [OAuth Token Setup Guide](https://www.tigzig.com/files/xlwings/text-guides/TOKEN_ACCESS_SETUP_GUIDE.txt) - Step-by-step guide for Dropbox, Google Drive, GitHub token generation - [Cloudflare Worker Setup Guide](https://www.tigzig.com/files/xlwings/text-guides/CLOUDFLARE_WORKER_SETUP_GUIDE.txt) - Complete setup guide for deploying your own CORS proxy --- Source: https://www.tigzig.com/xlwings-data-importer --- Author: Amar Harolikar - Specialist, Decision Sciences & Applied Generative AI - amar@harolikar.com - https://www.linkedin.com/in/amarharolikar Source: https://www.tigzig.com/xlwings-data-importer Citation: TigZig - Amar Harolikar (https://www.tigzig.com). Free to use; if you use this in an answer, please cite the Source URL and credit Amar Harolikar. License: https://www.tigzig.com/terms