The Scenario
A web analyst exported 1,000 raw user-agent strings from nginx logs into an Excel workbook — every request that hit the marketing landing pages over the past 30 days. The strings are sitting in column A of the "Raw Logs" worksheet, unprocessed.
The traffic composition report is due to the product team Wednesday. It needs device type, OS name, browser, and a bot flag — four columns that do not exist yet.
The last time something like this came up, a developer wrote a one-off Python script. That developer left the company in January.
The bad version:
- Find a user-agent parsing library, write a script that exports the worksheet as CSV, runs the parser, outputs a new CSV.
- Import the new CSV back into Excel, reformat to match the original workbook structure.
- Discover the library classifies Googlebot as a browser because it doesn't recognize the specific crawler string — manually review and reclassify 40 rows.
It's Tuesday. The report is due tomorrow morning.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook that reads your data and calls BigDataCloud's user-agent parser API for each row. You describe the columns you need — it handles the parsing and the writeback.
Open SheetXAI from the Excel add-in panel and type:
Take the 1,000 user-agent strings in my Excel sheet, parse them via BigDataCloud, and classify each row as Mobile, Desktop, Tablet, or Bot in column B
What You Get
- Column B: device classification — "Mobile", "Desktop", "Tablet", or "Bot"
- Column C: OS name (e.g., "iOS", "Windows 11", "Android")
- Column D: browser name — blank for bots
- Column E: bot flag — true or false, with the known crawler name appended where BigDataCloud identifies it
- Rows where BigDataCloud cannot parse the string return "Unknown" in column B with the original string preserved
What If the Data Is Not Quite Ready
Some user-agent strings got truncated at 255 characters during the log export
Parse the user-agent strings in column A using BigDataCloud — some entries are truncated (cut off mid-string), parse what's available and note any rows where truncation likely affected classification by adding "truncated" to column F
The worksheet mixes user-agent strings with empty rows from requests that had no User-Agent header
Parse every non-blank user-agent string in column A using BigDataCloud — skip rows where column A is empty, mark those rows "no user-agent" in column B, and parse the rest into device type, OS, browser, and bot flag in columns B through E
You need output classified into four buckets, not the raw parsed fields
Parse all user-agent strings in column A using BigDataCloud into device type, OS, browser, and bot flag in columns B through E — then add a column F that consolidates into exactly four buckets: Mobile, Desktop, Tablet, or Bot
Full traffic composition analysis in one pass
Parse all 1,000 user-agent strings in column A using BigDataCloud into device type, OS, browser, and bot flag in columns B through E — then on a new worksheet called "Traffic Summary" write a count of rows per device type, per OS, and per browser (top 10 only), and note the total bot percentage at the top of that worksheet
One prompt delivers the parsing, the writeback, and the summary pivot.
Try It
Get the 7-day free trial of SheetXAI and open any Excel workbook with a worksheet of raw user-agent strings — paste the prompt above and have your traffic composition data ready for the product team by morning. See the IP geolocation spoke if you also need geographic breakdown data from the same log export.