Why this matters
As a Data Analyst, you will constantly pull data from CSVs, spreadsheets, exports from CRMs/marketing tools, and sometimes web tables. Getting the import right saves hours of cleanup and avoids silent errors like misread dates or broken characters. Typical tasks include:
- Loading monthly CSV exports and combining them into one clean table.
- Importing data with unusual delimiters (semicolon or tab), different encodings (UTF-8, Latin-1), and locale-specific formats (comma decimals).
- Refreshing from the same source each week without rebuilding your steps.
- Pulling ranges from another spreadsheet while preserving data types.
Investing 30 minutes here can prevent days of downstream fixing.
Concept explained simply
Importing is the process of bringing external data into your spreadsheet so it looks correct and acts like real data (numbers are numbers, dates are dates). Think of it as guiding the spreadsheet on how to read a file: what separates columns, what characters are used, whether the first row is a header, and how to treat dates and decimals.
Mental model
Pipeline: Source → Connector → Parser → Schema → Sheet → Refresh
- Source: File or URL (CSV, TSV, XLSX, another Sheet).
- Connector: The feature you use (e.g., Excel Get Data, Google Sheets Import).
- Parser: Settings for delimiter, text qualifier, encoding, decimal/thousand separators.
- Schema: Column names and data types (text, number, date, datetime).
- Sheet: Where the data lands (new sheet, replace, or append).
- Refresh: Re-run the import with the same steps.
Quick setup checklist
- Know the delimiter: comma, semicolon, or tab.
- Check encoding: UTF-8 preferred; else try Latin-1/Windows-1252 if accents look broken.
- Identify header rows: Does the first row contain column names?
- Decide number/date formats: dot or comma decimals; day-month or month-day dates.
- Choose destination: replace sheet, append to table, or load to a new tab.
- Plan refresh: keep the file in a consistent location or use a stable URL.
Platform tips
Excel (Windows/Mac) — reliable for recurring imports
- Use Data → Get Data → From Text/CSV (Power Query) for flexible control.
- In the preview, set File Origin (encoding), Delimiter, and Data Type detection.
- Transform: split columns, change types, fix locale (e.g., comma decimals).
- Load as a table; then use Refresh to re-run the same steps on new files.
Google Sheets — quick imports and live connections
- File → Import for local files; choose Separator and destination.
- Functions for live pulls: IMPORTDATA (CSV/TSV URL), IMPORTHTML (table/list), IMPORTRANGE (another Google Sheet).
- Use ARRAYFORMULA/QUERY to combine sheets; ensure consistent headers and types.
Worked examples
1) CSV with semicolons and comma decimals
Sample file to copy into a text file named sales_eu.csv (encoded as Latin-1):
OrderID;Date;Country;Product;Qty;Price
1001;31/01/2024;DE;Keyboard;3;19,99
1002;01/02/2024;FR;Mouse;2;12,50
1003;02/02/2024;ES;Headset;1;45,00
Goal: correct columns, numeric Qty/Price, real dates.
Excel steps
- Data → Get Data → From Text/CSV → select sales_eu.csv.
- Set File Origin to Western European (Windows) or 1252 if accents look wrong.
- Set Delimiter to Semicolon.
- Transform Data → select Price → Transform → Replace Values: replace comma with dot (or use Locale in Change Type).
- Change Type: Qty to Whole Number, Price to Decimal, Date to Date (dd/mm/yyyy).
- Close & Load to a table.
Google Sheets steps
- File → Import → Upload sales_eu.csv.
- Separator: Detect or Custom → ; (semicolon).
- After import, select Price column → Find/replace comma with dot, or use a helper column:
=VALUE(SUBSTITUTE(E2, ",", ".")). - Format → Number for Qty and Price; Format → Number → Date for Date.
2) Append monthly CSVs and add a Month column
Create three CSV files: jan.csv, feb.csv, mar.csv
# jan.csv
OrderID,Customer,Amount
J-001,Acme,120.5
J-002,Zenit,98.0
# feb.csv
OrderID,Customer,Amount
F-010,Acme,75
F-011,Nova,210.2
# mar.csv
OrderID,Customer,Amount
M-100,Zenit,50
M-101,Aster,180
Excel (Power Query) steps
- Data → Get Data → From Text/CSV → import jan.csv → Transform Data.
- Home → New Source → Text/CSV → import feb.csv; repeat for mar.csv.
- Home → Append Queries → append all three.
- Add Column → Custom Column → name: Month; formula: enter
"2024-01"for jan query before append; do similarly in feb/mar queries or add from filename with a function if stored in one folder. - Ensure types (Amount as Decimal). Close & Load to a single table.
Google Sheets steps
- File → Import each CSV into its own sheet: Jan, Feb, Mar (use Replace data in selected cell of new sheets).
- In a new sheet Combined, place this formula in A1:
={"Month","OrderID","Customer","Amount";
{ARRAYFORMULA(IF(LEN(Jan!A2:A), DATE(2024,1,1), )), Jan!A2:C};
{ARRAYFORMULA(IF(LEN(Feb!A2:A), DATE(2024,2,1), )), Feb!A2:C};
{ARRAYFORMULA(IF(LEN(Mar!A2:A), DATE(2024,3,1), )), Mar!A2:C}}
- Format the first column as Date, Amount as Number.
- Optional: Wrap with QUERY to remove blank rows.
3) Import a range from another Google Sheet
Goal: pull the range A1:D into your current file.
- In a destination sheet cell, use:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID", "Sheet1!A1:D") - Authorize once when prompted.
- Keep header rows consistent to avoid type mismatches.
Tips
- If you see #REF! with an authorization message, click Allow access.
- Use named ranges in the source for stability.
Exercises (do them!)
These mirror the exercises below. After completing them, take the Quick Test. The test is available to everyone; sign in to save your progress.
Exercise 1 — Fix a tricky CSV import
Create a text file named orders_semicolon.csv with this content:
Order;Date;City;Total
101;12-02-2024;München;1.234,50
102;13-02-2024;Zürich;299,99
103;14-02-2024;Genève;85,00
Tasks:
- Import using the correct delimiter and encoding (accents must display correctly).
- Convert Total to a numeric field.
- Parse Date as a real date.
Expected: 4 columns; City accents are correct; Total is numeric; Date is a date type.
Exercise 2 — Append and label months
Create two files and combine them:
# apr.csv
OrderID,Channel,Revenue
A-01,Email,320
A-02,Ads,190
# may.csv
OrderID,Channel,Revenue
M-01,Email,210
M-02,Social,155
Tasks:
- Append both into one table.
- Add a Month column with April or May.
- Ensure Revenue is numeric.
Expected: One table with 4 columns and 4 rows (plus header), correct types.
Common mistakes and self-checks
- Wrong delimiter: Columns merge together. Self-check: Do you see fewer columns than expected? Try semicolon or tab.
- Encoding issues: Characters like München. Self-check: Switch file origin/encoding to UTF-8 or 1252.
- Comma decimals treated as text: Self-check: Right-align/format as number; convert comma to dot or set locale-aware type.
- Header row imported as data: Self-check: Does the first row repeat labels? Enable header detection or remove the first row.
- Date misread (MM/DD vs DD/MM): Self-check: Sort by date; if February 13 becomes January 2, fix locale/date format.
- Silent type drift after refresh: Self-check: Lock data types in your import steps (Power Query Change Type, or post-import coercion).
Practical projects
Project 1 — Weekly sales rollup
- Store weekly CSVs in one folder (consistent headers).
- Excel: Folder connector → Combine & Transform → append automatically.
- Add a column from file name to capture Week.
- Load to a table and build a pivot chart.
Project 2 — Marketing spend dashboard
- Import ad platform CSVs (one per channel).
- Standardize column names and types during import.
- Append into a fact table with a Channel column.
- Create a summary sheet with totals by Channel and Week.
Project 3 — Live reference data
- Maintain one master sheet of products.
- Use IMPORTRANGE or Power Query to pull it into analysis files.
- Ensure ID is text everywhere to avoid dropping leading zeros.
Who this is for
- Aspiring and junior Data Analysts who work primarily in spreadsheets.
- Professionals in operations, marketing, or finance who need reliable imports.
- Anyone preparing for analyst interviews with a practical spreadsheet focus.
Prerequisites
- Basic spreadsheet navigation (rows/columns, formatting).
- Comfort with CSV/TSV concepts (plain text tables).
- Optional: Light familiarity with formulas (ARRAYFORMULA/QUERY or simple functions).
Learning path
- Importing Data (this lesson): delimiters, encoding, types, append.
- Cleaning and Transforming: trimming, splitting, find/replace, type coercion.
- Structuring Tables: headers, data types, named ranges, consistent schemas.
- Formulas for Shaping: QUERY, FILTER, VLOOKUP/XLOOKUP, ARRAY formulas.
- Aggregation: pivot tables and summaries.
- Automation: refresh schedules, consistent file locations, stable URLs.
Next steps
- Practice importing diverse files (UTF-8 vs Latin-1, semicolon vs tab) until you can predict issues.
- Build a repeatable import pipeline using Power Query or spreadsheet formulas.
- Move on to Cleaning and Transforming to harden your workflow.
Mini challenge
Copy this data into a file named inventory.tsv (tab-separated):
SKU Name Qty UnitPrice
00123 Cable USB-C 25 7,90
00007 Adapter HDMI 10 12,50
Import it so that:
- Leading zeros in SKU are preserved as text.
- Qty and UnitPrice are numeric (handle comma decimals).
- Headers are recognized.
Hint
Set SKU type to Text during import; convert comma to dot for UnitPrice; verify delimiter is Tab.
When you're ready, take the quick test below. The test is available to everyone; sign in to save your progress.