{"version":"0.3.0","body":"/**\n * CSV to JSON for Power Automate (by Marcel Lehmann, powerplatformtip.com)\n *\n * Turns CSV text into a JSON array of objects. The column names come from the header row,\n * so the script works for any CSV with any number of columns. It does not read or change the\n * workbook, so it runs on any (even empty) workbook.\n *\n * Handles: quoted values (RFC 4180) with delimiters, line breaks and doubled quotes inside,\n * CRLF / LF / CR line endings, a UTF-8 BOM, an Excel \"sep=;\" first line and empty lines.\n * Empty or duplicate column names are made unique (column3, Name_2). Rows without any value\n * (empty lines, \";;;\" lines at the end of an Excel export) are skipped.\n *\n * @param csvText The CSV content as text, for example string(body('Get_file_content')).\n * @param delimiter \"auto\" (default) detects comma, semicolon, tab or pipe. Or set \"comma\",\n *   \"semicolon\", \"tab\", \"pipe\", or any single character.\n * @param hasHeader true (default): the first row holds the column names.\n *   false: columns are named column1, column2, ...\n * @param detectTypes false (default): every value stays text, exactly as in the file.\n *   true: values like 42 or 3.14 become numbers and true/false become booleans.\n *   Values with leading zeros (007), a plus sign (+41...) or more than 15 digits stay text.\n *   Decimal commas (3,14) and thousands separators (1'250.50) stay text as well.\n * @returns json: the JSON array as text (use json(...) or Parse JSON in the flow),\n *   rowCount: number of data rows, delimiter: the delimiter that was used,\n *   columns: the final column names.\n */\nfunction main(\n  workbook: ExcelScript.Workbook,\n  csvText: string,\n  delimiter: string = \"auto\",\n  hasHeader: boolean = true,\n  detectTypes: boolean = false\n): CsvToJsonResult {\n  let text: string = typeof csvText === \"string\" ? csvText : \"\";\n\n  // Remove a UTF-8 byte order mark.\n  while (text.charAt(0) === \"\") {\n    text = text.substring(1);\n  }\n\n  // Excel can write \"sep=;\" as the first line to announce the delimiter.\n  let hintedDelimiter = \"\";\n  const sepMatch = /^sep=([^\"\\r\\n])(\\r\\n|\\n|\\r|$)/i.exec(text);\n  if (sepMatch) {\n    hintedDelimiter = sepMatch[1];\n    text = text.substring(sepMatch[0].length);\n  }\n\n  const delim = resolveDelimiter(delimiter, hintedDelimiter, text);\n  const rows = parseCsv(text, delim);\n\n  let columnCount = 0;\n  for (let r = 0; r < rows.length; r++) {\n    if (rows[r].length > columnCount) {\n      columnCount = rows[r].length;\n    }\n  }\n\n  const useHeader = hasHeader !== false;\n  const headerRow: string[] = useHeader && rows.length > 0 ? rows[0] : [];\n  const dataRows: string[][] = useHeader ? rows.slice(1) : rows;\n  const columns = buildColumnNames(headerRow, columnCount);\n  const convert = detectTypes === true;\n\n  const records: Array<Record<string, string | number | boolean>> = [];\n  for (let r = 0; r < dataRows.length; r++) {\n    const row = dataRows[r];\n    const record = Object.create(null) as Record<string, string | number | boolean>;\n    for (let c = 0; c < columnCount; c++) {\n      const raw = c < row.length ? row[c] : \"\";\n      record[columns[c]] = convert ? convertValue(raw) : raw;\n    }\n    records.push(record);\n  }\n\n  return {\n    json: JSON.stringify(records),\n    rowCount: records.length,\n    delimiter: delimiterName(delim),\n    columns: columns\n  };\n}\n\ninterface CsvToJsonResult {\n  json: string;\n  rowCount: number;\n  delimiter: string;\n  columns: string[];\n}\n\n/** Picks the delimiter: explicit parameter first, then a \"sep=\" hint, then auto-detection. */\nfunction resolveDelimiter(option: string, hint: string, text: string): string {\n  const raw = typeof option === \"string\" ? option : \"auto\";\n  if (raw.length === 1) {\n    if (raw === '\"' || raw === \"\\r\" || raw === \"\\n\") {\n      throw new Error(\"Delimiter must not be a quote or a line break.\");\n    }\n    return raw;\n  }\n  const name = raw.trim().toLowerCase();\n  if (name === \"\" || name === \"auto\") {\n    return hint !== \"\" ? hint : detectDelimiter(text);\n  }\n  if (name === \"comma\") return \",\";\n  if (name === \"semicolon\") return \";\";\n  if (name === \"tab\" || name === \"\\\\t\") return \"\\t\";\n  if (name === \"pipe\") return \"|\";\n  throw new Error(\n    \"Unknown delimiter '\" + raw + \"'. Use auto, comma, semicolon, tab, pipe or a single character.\"\n  );\n}\n\nfunction delimiterName(delim: string): string {\n  if (delim === \",\") return \"comma\";\n  if (delim === \";\") return \"semicolon\";\n  if (delim === \"\\t\") return \"tab\";\n  if (delim === \"|\") return \"pipe\";\n  return delim;\n}\n\n/**\n * Looks at the first 10 non-empty lines (outside quotes) and picks the candidate that appears\n * in the header line and is repeated equally often in the most lines. Falls back to a comma.\n */\nfunction detectDelimiter(text: string): string {\n  const candidates = [\",\", \";\", \"\\t\", \"|\"];\n  const maxLines = 10;\n  const lines: number[][] = [];\n  let counts = [0, 0, 0, 0];\n  let hasContent = false;\n  let inQuotes = false;\n  let atFieldStart = true;\n\n  for (let i = 0; i < text.length; i++) {\n    const ch = text.charAt(i);\n    if (inQuotes) {\n      if (ch === '\"') {\n        if (text.charAt(i + 1) === '\"') {\n          i++;\n        } else {\n          inQuotes = false;\n        }\n      }\n      continue;\n    }\n    if (ch === \"\\r\" || ch === \"\\n\") {\n      if (ch === \"\\r\" && text.charAt(i + 1) === \"\\n\") {\n        i++;\n      }\n      if (hasContent) {\n        lines.push(counts);\n        if (lines.length >= maxLines) {\n          break;\n        }\n      }\n      counts = [0, 0, 0, 0];\n      hasContent = false;\n      atFieldStart = true;\n      continue;\n    }\n    if (ch === '\"' && atFieldStart) {\n      inQuotes = true;\n      hasContent = true;\n      atFieldStart = false;\n      continue;\n    }\n    const index = candidates.indexOf(ch);\n    if (index >= 0) {\n      counts[index]++;\n      hasContent = true;\n      atFieldStart = true;\n      continue;\n    }\n    if (ch !== \" \") {\n      hasContent = true;\n    }\n    atFieldStart = false;\n  }\n  if (hasContent && lines.length < maxLines) {\n    lines.push(counts);\n  }\n  if (lines.length === 0) {\n    return \",\";\n  }\n\n  let best = \",\";\n  let bestScore = 0;\n  for (let k = 0; k < candidates.length; k++) {\n    const headerCount = lines[0][k];\n    if (headerCount === 0) {\n      continue;\n    }\n    let consistent = 0;\n    for (let l = 0; l < lines.length; l++) {\n      if (lines[l][k] === headerCount) {\n        consistent++;\n      }\n    }\n    const score = consistent * 1000 + headerCount;\n    if (score > bestScore) {\n      bestScore = score;\n      best = candidates[k];\n    }\n  }\n  return best;\n}\n\n/**\n * RFC 4180 parser. Returns the rows as arrays of text values and skips empty rows.\n * Quotes only start a quoted value at the beginning of a field; a quote inside an unquoted\n * value is kept as it is. An unclosed quote raises an error instead of returning wrong data.\n */\nfunction parseCsv(text: string, delim: string): string[][] {\n  const rows: string[][] = [];\n  const length = text.length;\n  let row: string[] = [];\n  let i = 0;\n\n  while (i < length) {\n    let field: string;\n\n    if (text.charAt(i) === '\"') {\n      const quoteStart = i;\n      let value = \"\";\n      let closed = false;\n      i++;\n      while (i < length) {\n        const q = text.indexOf('\"', i);\n        if (q === -1) {\n          break;\n        }\n        value += text.substring(i, q);\n        if (text.charAt(q + 1) === '\"') {\n          value += '\"';\n          i = q + 2;\n        } else {\n          i = q + 1;\n          closed = true;\n          break;\n        }\n      }\n      if (!closed) {\n        const lineNumber = text.substring(0, quoteStart).split(/\\r\\n|\\n|\\r/).length;\n        throw new Error(\"The CSV has a quoted value that is never closed (starts in line \" + lineNumber + \").\");\n      }\n      // Text after the closing quote (invalid CSV) is kept instead of dropped.\n      let end = i;\n      while (end < length && !isFieldEnd(text.charAt(end), delim)) {\n        end++;\n      }\n      value += text.substring(i, end);\n      i = end;\n      field = value;\n    } else {\n      let end = i;\n      while (end < length && !isFieldEnd(text.charAt(end), delim)) {\n        end++;\n      }\n      field = text.substring(i, end);\n      i = end;\n    }\n\n    row.push(field);\n\n    if (i >= length) {\n      break;\n    }\n    const next = text.charAt(i);\n    if (next === delim) {\n      i++;\n      if (i >= length) {\n        row.push(\"\");\n      } else {\n        continue;\n      }\n    } else if (next === \"\\r\" && text.charAt(i + 1) === \"\\n\") {\n      i += 2;\n    } else {\n      i++;\n    }\n    if (!isBlankRow(row)) {\n      rows.push(row);\n    }\n    row = [];\n  }\n\n  if (row.length > 0 && !isBlankRow(row)) {\n    rows.push(row);\n  }\n  return rows;\n}\n\nfunction isFieldEnd(ch: string, delim: string): boolean {\n  return ch === delim || ch === \"\\r\" || ch === \"\\n\";\n}\n\nfunction isBlankRow(row: string[]): boolean {\n  for (let i = 0; i < row.length; i++) {\n    if (row[i].trim() !== \"\") {\n      return false;\n    }\n  }\n  return true;\n}\n\n/** Trimmed header names; empty names become columnN, duplicates (ignoring case) get _2, _3, ... */\nfunction buildColumnNames(headerRow: string[], columnCount: number): string[] {\n  const names: string[] = [];\n  const used: Record<string, boolean> = Object.create(null) as Record<string, boolean>;\n  for (let c = 0; c < columnCount; c++) {\n    let base = c < headerRow.length ? headerRow[c].trim() : \"\";\n    if (base === \"\") {\n      base = \"column\" + (c + 1);\n    }\n    let name = base;\n    let suffix = 2;\n    while (used[name.toLowerCase()]) {\n      name = base + \"_\" + suffix;\n      suffix++;\n    }\n    used[name.toLowerCase()] = true;\n    names.push(name);\n  }\n  return names;\n}\n\n/** Only used when detectTypes is true: plain numbers and true/false, everything else stays text. */\nfunction convertValue(raw: string): string | number | boolean {\n  const t = raw.trim();\n  if (t === \"\") {\n    return raw;\n  }\n  const lower = t.toLowerCase();\n  if (lower === \"true\") return true;\n  if (lower === \"false\") return false;\n  if (/^-?(0|[1-9][0-9]*)(\\.[0-9]+)?$/.test(t) && t.replace(/[^0-9]/g, \"\").length <= 15) {\n    return Number(t);\n  }\n  return raw;\n}\n","description":"","noCodeMetadata":null,"parameterInfo":"{\"version\":1,\"originalParameterOrder\":[{\"name\":\"csvText\",\"index\":0},{\"name\":\"delimiter\",\"index\":1},{\"name\":\"hasHeader\",\"index\":2},{\"name\":\"detectTypes\",\"index\":3}],\"parameterSchema\":{\"type\":\"object\",\"required\":[\"csvText\",\"delimiter\",\"hasHeader\",\"detectTypes\"],\"properties\":{\"csvText\":{\"type\":\"string\"},\"delimiter\":{\"type\":\"string\",\"default\":\"auto\"},\"hasHeader\":{\"type\":\"boolean\",\"default\":true},\"detectTypes\":{\"type\":\"boolean\",\"default\":false}}},\"returnSchema\":{\"type\":\"object\",\"properties\":{\"result\":{\"type\":\"object\",\"properties\":{\"json\":{\"type\":\"string\"},\"rowCount\":{\"type\":\"number\"},\"delimiter\":{\"type\":\"string\"},\"columns\":{\"type\":\"array\",\"items\":{\"type\":\"string\"}}}}}},\"signature\":{\"comment\":\"Converts CSV text to a JSON array of objects. Column names come from the header row; works on any workbook.\",\"parameters\":[{\"name\":\"workbook\",\"comment\":\"\"},{\"name\":\"csvText\",\"comment\":\"The CSV content as text, for example string(body('Get_file_content')).\"},{\"name\":\"delimiter\",\"comment\":\"\\\"auto\\\" (default) detects comma, semicolon, tab or pipe. Or set \\\"comma\\\", \\\"semicolon\\\", \\\"tab\\\", \\\"pipe\\\", or any single character.\"},{\"name\":\"hasHeader\",\"comment\":\"true (default): the first row holds the column names. false: columns are named column1, column2, ...\"},{\"name\":\"detectTypes\",\"comment\":\"false (default): every value stays text, exactly as in the file. true: values like 42 or 3.14 become numbers and true/false become booleans. Values with leading zeros (007), a plus sign (+41...) or more than 15 digits stay text. Decimal commas (3,14) and thousands separators (1'250.50) stay text as well.\"}]}}","apiInfo":"{\"variant\":\"synchronous\",\"variantVersion\":2}"}