Extract and Transform Data

Reading a file is just the first step. ChatGPT can also extract structured data from unstructured documents, reformat it, cross-reference multiple sources, and export the results in your preferred format. This transformation capability makes it a genuine data processing tool, not just a document reader.

Extract Structured Data from Unstructured Sources

Structured extraction means taking information scattered throughout a document and organizing it into a usable format. For a batch of PDF invoices, the request is a single sentence: “Extract the following from each invoice: invoice number, date, vendor, amount excluding tax, VAT, and amount including tax. Present everything in a table.” The same principle applies to an annual report with figures buried in paragraphs: “Extract all financial figures mentioned in this report: revenue, net profit, margin, headcount. Organize them by year in a comparison table.” And for a purely text-based document: “This document mentions 15 contacts. Extract the names, companies, and job titles for each person into a structured table.” The common thread in these three requests is the explicit list of expected fields. This list transforms a stream of text into usable tabular data, representing one of ChatGPT’s most valuable capabilities.

Change Formats

Once you have the data, changing its format is just as simple. Make a raw CSV readable: “Create a readable table with clear headers. Translate the column names from English to French.” Turn a table into a visual: “Based on this data, generate a bar chart showing sales distribution by region.” Convert figures into a meeting deck: “Transform this quarterly data into 3 summary slides: overview, top performers, and alert points.” Or switch between technical structures: “This JSON file contains product data. Convert it to a CSV table with the columns: name, price, category, stock.”

Cross-Reference Two Sources

Upload two files to the same conversation, and cross-referencing becomes a single sentence. Imagine a customer list with purchase history in an Excel file and a satisfaction survey in a CSV: “Cross-reference these two files by customer name. For each customer, show the total purchase amount and satisfaction score. Sort by satisfaction score in descending order. Identify high-potential customers (high purchases + high satisfaction) and at-risk customers (high purchases + low satisfaction).”

Normally, this operation would require complex Excel formulas or a Python script; here, it happens via a conversational request. This is all the more reason to verify the result: matching relies on names, and homonyms or typos can silently skew the pairings. Manually check a few rows before basing business decisions on the output.

Clean and Normalize

Real-world data rarely arrives clean, and this is where ChatGPT excels. For heterogeneous formats: “In this phone number column, some entries use the format 06.12.34.56.78, others +33612345678, and others 06 12 34 56 78. Normalize them all to the international format +33 6 12 34 56 78.” For questionable dates: “Analyze this date column. Identify invalid dates, inconsistent formats, and outliers.” For fuzzy duplicates that no formula can catch: “This file contains probable duplicates (same names with slight spelling variations). Identify them and propose a consolidated version.” And for bulk categorization: “Here are 200 expense descriptions. Categorize each into one of these categories: Transport, Meals, Accommodation, Supplies, Services, Other.”

As with cross-referencing, reviewing a sample remains the rule for this last case: automated categorization is rarely wildly wrong, but it does make mistakes. For large files, do not run the full processing immediately. First, request an analysis on a sample, validate the method, and then proceed with the entire file.

Retrieve the Result

Without specific output instructions, ChatGPT will display results as text in the chat, leaving you to copy and paste. Explicitly request a file: “Generate a downloadable CSV file with these results,” “Create an Excel file with two tabs: raw data and summary,” or “Format this data as JSON for integration into our API.” The system produces the file, and a download link appears in the conversation.

Industrialize Recurring Workflows

If you receive the same type of file every month, write the prompt once and reuse it: “I will send you a bank statement PDF every month. For each statement, I want you to: (1) extract all transactions, (2) categorize them into 6 categories (Housing, Food, Transport, Leisure, Health, Other), (3) calculate the total per category, (4) compare with the previous month if available, (5) export the result as CSV.” Placed in the instructions of a dedicated project, this prompt ensures consistent processing from month to month, enabling meaningful comparisons. However, a bank statement is sensitive data: evaluate what your plan and obligations allow before uploading it.

Your Turn

Clean a real file. Take a file from your daily work—a CRM export, timesheet, contact list, or financial statement—and ask: “Analyze this file. Identify data quality issues (duplicates, missing values, inconsistent formats). Propose a cleaning plan. Then apply the cleaning and generate a corrected downloadable file.” Choose this file wisely: health data, social security numbers, and banking information require a risk assessment before upload.

Official sources: Data analysis with ChatGPT · Uploading files and audio to ChatGPT · Projects in ChatGPT (OpenAI Help Center)

Key Takeaways

• ChatGPT can extract structured data from unstructured documents in a single request
• File cross-referencing and data normalization happen conversationally
• Explicitly request an export (CSV, Excel, JSON) to retrieve processed data
• Create reusable prompts for monthly or recurring tasks
• Always verify results, especially for cross-referencing and automated categorization