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). 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:

// Builds the sample workbook for the Excel recipe.
import XLSX from 'xlsx';
import * as fs from 'node:fs';
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

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 and the converter 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, 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).

Script

Node 18 or later, with npm install papaparse xlsx.

// Read an Excel export directly (no CSV resave), convert Excel dates, and write review rows.
// Usage: node excel-reviews.mjs reviews.xlsx reviews.csv
import fs from 'node:fs';
import XLSX from 'xlsx';
import Papa from 'papaparse';

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`);

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.

# Read an Excel export directly, convert its dates, and write review rows.
# Usage: python3 excel_reviews.py reviews.xlsx reviews.csv   (pip install openpyxl)
import csv, sys
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.
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.
The toolkit reads .xlsx with "input": { "sheet": "Reviews", "headerRow": 2 } and converts Excel dates with timestamp.timeZone.