Tabular Data

Partner exports often describe records as rows and fields as columns. A spreadsheet makes those records easy to inspect, but the receiving application needs an exact layout and an agreed interpretation of every value. CSV, workbooks, and fixed-width files represent that table differently, so converting between them requires decisions about both structure and meaning.

Delimiters and Quoting

A delimited text file separates fields with a character such as a comma, tab, semicolon, or pipe (|). CSV means comma-separated values; TSV means tab-separated values. Exchanges also use CSV-style quoting with other delimiters, so check the export settings for the actual separator.

RFC 4180 documents a common CSV convention: commas separate fields, CRLF separates records, and double quotes enclose fields containing commas, quotes, or line breaks. A double quote inside a quoted field is written twice:

CustomerID,Name,Note
00123,"Acme, Ltd","Say ""hello"""

The second record has three fields. The comma in Acme, Ltd belongs to the name, and the note's value is Say "hello". The enclosing quotes and doubled quote characters describe the representation; they are not extra characters in the parsed values.

A quoted field can span physical lines:

CustomerID,Address
00123,"10 Market Street
Suite 4"

This still contains one data record. Counting lines or splitting at every comma does not recover the records correctly. A different delimiter reduces some quoting needs, but the exchange still needs a rule for delimiters and line breaks occurring inside values.

Files.com's readers have their own format settings and bulk import requirements. Match the reader to the source convention and the writer to the destination convention, including encoding and line endings.

Headers and Record Shape

A header row names the columns; a headerless file identifies fields by position. The two layouts need different reading settings. Treating a data row as a header loses that record, while treating the header as data introduces values such as CustomerID into the dataset.

For header-based mapping, names must distinguish the fields the transformation uses. Duplicate, blank, misspelled, or unexpectedly spaced headers can make values ambiguous even when the file parses. For positional mapping, column order is part of the agreement. A field added in the middle changes the meaning of every subsequent position.

Rows with different field counts are sometimes called ragged rows. An omitted field needs its delimiter to preserve later positions: in 00123,,25.00, the second field is blank and the amount remains third. Removing that delimiter shifts the amount into the second position. Check field counts against the agreed layout even when the reader tolerates uneven rows.

A blank field also needs an agreed meaning. It might mean unknown, not applicable, an empty string, or a value to clear. CSV does not supply a universal distinction between those cases. Content Validation can check the header and record rules that matter to your exchange before the data is used.

Spreadsheets and CSV

An Excel workbook can contain multiple worksheets, typed cells, formulas, formatting, and charts. .xls is an older binary workbook format; .xlsx packages workbook content in a different, XML-based format. Both carry more information than a CSV, which represents one table of text fields.

Exporting a worksheet to CSV loses the workbook structure, formatting, and formula definitions. A displayed value can depend on formatting, such as a number shown with leading zeros or a date shown in a local convention. The exported text therefore needs inspection as data, even when the worksheet looked correct. Microsoft's supported-format guidance describes the workbook and text formats.

Files.com's Document Conversion changes spreadsheet formats. TransformScript's XLSX reader instead reads rows from a selected worksheet, using the first worksheet by default. It uses the first row as field names by default, and its XLSX writer produces a new workbook with one worksheet. Select the sheet explicitly when the exchange depends on a named data table rather than the workbook's first sheet.

Opening a CSV in Excel can itself reinterpret the data. Depending on its import settings, Excel can remove leading zeros, limit the precision of long numeric identifiers, or interpret a value as a date. Quotes in a CSV delimit a field; they do not declare that field's data type. Importing identifier columns as text preserves their intended meaning. Microsoft's guidance on leading zeros and large numbers explains the relevant import choices.

For inspection in Files.com, Text File Previews can show the source text instead of converting the file to a spreadsheet. Retain the received original while checking a conversion, and compare the output values as well as how they display.

Numbers, Dates, and Time Zones

A value's appearance is not enough to determine its type. 00123 may identify a customer, while 123 may count units. Converting both to numbers destroys the distinction. Preserve identifiers as text and apply numeric conversion only to fields defined as quantities or amounts.

Locale conventions affect numeric text. 1,25 can mean one and a quarter, while a comma elsewhere separates thousands or CSV columns. A semicolon-delimited file can carry decimal commas without quoting them, but the numeric reader still needs the decimal convention. Removing punctuation without that agreement can turn 1,25 into 125.

Dates need the same precision. 01/02/2026 can mean January 2 or February 1. An agreement using year-month-day, such as 2026-02-01, removes that ambiguity. Where an existing partner format uses another order, parse that order explicitly rather than relying on a computer's regional settings.

A calendar date and a timestamp answer different questions. A timestamp such as 2026-02-01T09:00:00Z identifies a UTC time; 2026-02-01T09:00:00-05:00 identifies a different instant. 2026-02-01T09:00:00 has no offset, so the sender must establish which time zone gives it meaning. Recurring local times can also depend on daylight saving rules.

These are values inside the file, separate from its upload time or the Automation's schedule. TransformScript provides explicit conversions and date and time operations. Apply the source's meaning before formatting the destination value; a transformation cannot infer the intended date order, currency, or time zone from an ambiguous sample alone.

Fixed-Width Records

A fixed-width file identifies fields by their positions and widths instead of delimiters. A layout might reserve the first five positions for a customer identifier, the next ten for a name, and the next eight for an amount. Padding keeps every field in its assigned position, so trimming the whole record can remove meaningful structure.

The layout must establish whether widths count bytes or characters. In UTF-8, some characters occupy several bytes, so a character-based slice can differ from a byte-based field. The agreement also needs to define padding, record endings, signs, and any implied decimal places: an amount written as 00001250 could mean 12.50 only if the specification says so.

Reading or writing this format requires logic for that layout, rather than a CSV delimiter setting. When a partner exchange relies on a dedicated converter or existing script, an Agent Compute node can run an approved program on your Agent host as part of the workflow. That program owns the position-based parsing and padding; TransformScript's format reference defines its native readers and writers.