# Excel and Excel dates > Recipe: read an .xlsx export directly, pick the right sheet and header row, convert Excel dates and keep long ids intact. ## When you need it Your export is an Excel workbook. Resaving it as CSV from Excel is the most common way data gets damaged: long numbers such as ids and timestamps are written in scientific notation (`4.4E+12`) and their last digits are gone for good ([upload errors F11 and T03](/data/upload-errors)). Read the workbook with a library instead. Workbooks bring their own surprises: - the data is on the second sheet, and the first is a cover page; - a title row sits above the header; - dates look like dates but are stored as numbers of days since 30 December 1899, with no time zone; - ids are stored as numbers, so a naive conversion writes `4400123456789.0` or `4.400123456789E+12`; - error cells such as `#N/A` appear where a lookup failed. ## Input Example input: a review export, `reviews.xlsx`, sheet **Reviews** (the first sheet, **About**, is a cover note). Row 1 is a title. This is what the sheet shows: | Review ID | Posted | Location | Stars | Review | Owner reply | Replied | |---|---|---|---|---|---|---| | 4400123456789 | 1/3/2026 9:12 | Austin Eastside | 2 | Long wait.
Cold croissant. | Sorry! Next pastry is on us. | 1/3/2026 18:00 | | 4400123456790 | 1/4/2026 12:00 | Portland Pearl | #N/A | Best cortado in town | | | | 4400123456791 | 1/5/2026 6:00 | Austin Eastside | | | | | What is stored: `Posted` for the first review is the number `46025.38333333333`, and the times are store-local (Central time) with no zone attached. To try the recipe on this sample, build the workbook with this script: ```js title="make-reviews-xlsx.mjs" // Builds the sample workbook for the Excel recipe. XLSX.set_fs(fs); const rows = [ ['Lumen Coffee reviews export'], ['Review ID', 'Posted', 'Location', 'Stars', 'Review', 'Owner reply', 'Replied'], [4400123456789, 46025.38333333333, 'Austin Eastside', 2, 'Long wait.\nCold croissant.', 'Sorry! Next pastry is on us.', 46025.75], [4400123456790, 46026.5, 'Portland Pearl', null, 'Best cortado in town', '', ''], [4400123456791, 46027.25, 'Austin Eastside', null, '', '', ''], ]; const ws = XLSX.utils.aoa_to_sheet(rows); for (const a of ['B3', 'B4', 'B5', 'G3']) ws[a].z = 'm/d/yyyy h:mm'; ws.D4 = { t: 'e', v: 0x2a, w: '#N/A' }; const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([['Export notes']]), 'About'); XLSX.utils.book_append_sheet(wb, ws, 'Reviews'); XLSX.writeFile(wb, 'reviews.xlsx'); ``` ## Output ```csv title="reviews.csv" sessionId,userId,incoming,text,dimensionlabs_timestamp,conversationId,location,stars review-4400123456789,review-4400123456789,true,Long wait. Cold croissant.,1767453120000,review-4400123456789,Austin Eastside,2 review-4400123456789,review-4400123456789,false,Sorry! Next pastry is on us.,1767484800000,review-4400123456789,Austin Eastside,2 review-4400123456790,review-4400123456790,true,Best cortado in town,1767549600000,review-4400123456790,Portland Pearl, ``` ## Rules - **Read the `.xlsx`, never a CSV that Excel saved.** The [file checker](/data/check-a-file) and the [converter](/data/convert-a-file) also read `.xlsx` files directly, Excel date cells included, but only the first sheet, with the column names in row 1. For a workbook like this one, use the script below or the [toolkit](/transform/toolkit), or move the data sheet to the front and delete the title row before you use the browser tools. - **Name the sheet and the header row.** Don't rely on "the first sheet": exports add cover sheets. - **Convert Excel dates with the time zone they were recorded in.** An Excel date number is a wall-clock time. `(serial - 25569)` gives days since 1 January 1970 as if the time were UTC; then apply the zone the times were recorded in. Here that's `America/Chicago`, so 9:12 becomes 15:12 UTC in January. - **Write ids as whole numbers.** `toFixed(0)` in JavaScript, `str(int(v))` in Python. - **Treat error cells as empty.** `#N/A` in a score column becomes an empty cell, not the text `#N/A`. - **A review is its own session; a reply is the second row.** The review is `incoming: true`, the owner reply `false` with its own time. Reviews with no text are dropped ([reviews](/data/reviews-surveys-tickets#reviews)). ## Script Node 18 or later, with `npm install papaparse xlsx`. ```js title="excel-reviews.mjs" // Read an Excel export directly (no CSV resave), convert Excel dates, and write review rows. // Usage: node excel-reviews.mjs reviews.xlsx reviews.csv const [input, output] = process.argv.slice(2); const SHEET = 'Reviews'; const HEADER_ROW = 2; // row 1 is a title const TIME_ZONE = 'America/Chicago'; // the times in the sheet are store-local wall times // Excel serial date (days since 1899-12-30, no time zone) -> Unix ms in TIME_ZONE. function excelToMillis(serial) { const wall = Math.round((serial - 25569) * 86400000); // the wall time, as if it were UTC const offset = (utc) => { const p = Object.fromEntries(new Intl.DateTimeFormat('en-US', { timeZone: TIME_ZONE, hourCycle: 'h23', year: 'numeric', month: 'numeric', day: 'numeric', hour: 'numeric', minute: 'numeric', second: 'numeric' }) .formatToParts(new Date(utc)).map((x) => [x.type, x.value])); return Date.UTC(p.year, p.month - 1, p.day, p.hour, p.minute, p.second) - utc; }; const first = wall - offset(wall); return wall - offset(first); // second pass handles DST edges } const wb = XLSX.read(fs.readFileSync(input), { type: 'buffer' }); // raw: true keeps dates as serial numbers and long ids as full numbers. const grid = XLSX.utils.sheet_to_json(wb.Sheets[SHEET], { header: 1, raw: true, defval: '' }); const header = grid[HEADER_ROW - 1].map((h) => String(h).trim()); const records = grid.slice(HEADER_ROW).map((r) => Object.fromEntries(header.map((h, i) => [h, r[i]]))); const isError = (v) => typeof v === 'string' && v.startsWith('#'); // #N/A, #VALUE! ... const text = (v) => (isError(v) ? '' : String(v ?? '').replace(/\s+/g, ' ').trim()); const id = (v) => (typeof v === 'number' ? v.toFixed(0) : text(v)); // never 4.4E+12 const rows = []; for (const r of records) { const review = text(r.Review); if (!review) continue; // no text, nothing to enrich const base = { sessionId: `review-${id(r['Review ID'])}`, userId: `review-${id(r['Review ID'])}`, conversationId: `review-${id(r['Review ID'])}`, location: text(r.Location), stars: text(r.Stars) }; rows.push({ ...base, incoming: 'true', text: review, dimensionlabs_timestamp: excelToMillis(r.Posted) }); const reply = text(r['Owner reply']); if (reply) rows.push({ ...base, incoming: 'false', text: reply, dimensionlabs_timestamp: excelToMillis(r.Replied) }); } const fields = ['sessionId', 'userId', 'incoming', 'text', 'dimensionlabs_timestamp', 'conversationId', 'location', 'stars']; fs.writeFileSync(output, Papa.unparse({ fields, data: rows.map((r) => fields.map((f) => r[f])) }, { newline: '\n' }) + '\n'); console.log(`${records.length} reviews -> ${rows.length} rows`); ``` ```bash node excel-reviews.mjs reviews.xlsx reviews.csv # 3 reviews -> 3 rows ``` ## In Python With `pip install openpyxl`. openpyxl returns Excel dates as `datetime` values without a zone, so you attach the zone yourself. It writes the same file. ```python title="excel_reviews.py" # Read an Excel export directly, convert its dates, and write review rows. # Usage: python3 excel_reviews.py reviews.xlsx reviews.csv (pip install openpyxl) from zoneinfo import ZoneInfo from openpyxl import load_workbook SHEET, HEADER_ROW = "Reviews", 2 TZ = ZoneInfo("America/Chicago") # the sheet holds store-local wall times def millis(dt): # openpyxl returns Excel dates as naive datetimes return int(dt.replace(tzinfo=TZ).timestamp() * 1000) def text(v): if v is None or (isinstance(v, str) and v.startswith("#")): # empty or #N/A return "" return " ".join(str(v).split()) def rid(v): return str(int(v)) if isinstance(v, (int, float)) else text(v) src, dst = sys.argv[1], sys.argv[2] ws = load_workbook(src, data_only=True)[SHEET] rows_in = list(ws.iter_rows(min_row=HEADER_ROW, values_only=True)) header = [str(h).strip() for h in rows_in[0]] out = [] for values in rows_in[1:]: r = dict(zip(header, values)) review = text(r["Review"]) if not review: continue key = f"review-{rid(r['Review ID'])}" base = {"sessionId": key, "userId": key, "conversationId": key, "location": text(r["Location"]), "stars": rid(r["Stars"])} out.append({**base, "incoming": "true", "text": review, "dimensionlabs_timestamp": millis(r["Posted"])}) if text(r["Owner reply"]): out.append({**base, "incoming": "false", "text": text(r["Owner reply"]), "dimensionlabs_timestamp": millis(r["Replied"])}) fields = ["sessionId", "userId", "incoming", "text", "dimensionlabs_timestamp", "conversationId", "location", "stars"] with open(dst, "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=fields, lineterminator="\n") w.writeheader() w.writerows(out) print(f"{len(out)} rows written") ``` ## Variations - **Dates stored as text** (`03/01/2026`) instead of date cells: parse them as text, deciding whether the day or the month comes first. See [Fix timestamps](/transform/recipe-fix-timestamps). - **Workbooks from a Mac set to the 1904 date system** start counting from 1 January 1904. If every date comes out four years early, that's why: SheetJS exposes the setting as `wb.Workbook.WBProps.date1904`. - **Several sheets with the same columns** (one per month or per store): read each, add a column with the sheet name, and combine them as in [Combine several exports](/transform/recipe-combine-exports). - **The [toolkit](/transform/toolkit)** reads `.xlsx` with `"input": { "sheet": "Reviews", "headerRow": 2 }` and converts Excel dates with `timestamp.timeZone`.