Tag Archives: CSV

JavaScript: In-browser export to CSV

This function will turn an array of data into a CSV file and get the browser to “download” it. It should also be wrapping and escaping values properly and, because of the BOM, be read correctly when opened in e.g. Excel.

It’s written in TypeScript ("lib": ["ES2017", "DOM"]), but should be easy to “downgrade” to regular JS if needed…

type GetValue <T> = <I extends T>(item: I) => any;
type FieldName <T> = keyof T;

export interface Columns<T> {
  [header: string]: FieldName<T> | GetValue<T>;
}

const COLUMN_SEPARATOR = ';';
const ROW_SEPARATOR = '\r\n';
const UNICODE_BOM = '\uFEFF';

const wrapValue = (value: string) => `"${value}"`;
const escapeValue = (value: string) => (value || '').replace(/"/, '""');

const makeHeaderLine = <T> (columns: Columns<T>) => Object
  .keys(columns)
  .map(escapeValue)
  .map(wrapValue)
  .join(COLUMN_SEPARATOR);

const makeItemLine = <T> (columns: Columns<T>, item: T) => Object
  .values(columns)
  .map(field => typeof field === 'function' ? field(item) : item[field])
  .map(String)
  .map(escapeValue)
  .map(wrapValue)
  .join(COLUMN_SEPARATOR);

export const exportToCsv = function <T> (data: T[], columns: Columns<T>, filename: string): void {
  const rows = [];

  rows.push(makeHeaderLine(columns));

  for (const item of data) {
    rows.push(makeItemLine(columns, item));
  }

  const csv = UNICODE_BOM + rows.join(ROW_SEPARATOR);
  const uri = `data:text/csv;charset=utf-8;header=present,${encodeURIComponent(csv)}`;

  const link = document.createElement('a');
  link.setAttribute('href', uri);
  link.setAttribute('download', filename);
  link.addEventListener('click', () => link.parentNode.removeChild(link));
  document.body.appendChild(link);

  link.click();
};

Usage

const users: User[] = [{id: 1, name: 'Alice', isCool: true}, ...];
const columns: Columns<User> = {
  'Id': 'id',
  'Name': 'name',
  'Is cool': user => user.isCool ? 'Yes' : 'No',
};
exportToCsv(users, columns, 'users.csv');

PowerShell: Parse, sort, filter and add to a CSV

Made an export from Splunk containing all IP addresses who had called a method on a companies ESB servers. This gave me a large CSV with two columns, Method and IP, which I then needed to filter to remove duplicate rows. Additionally I wanted to try a reverse lookup of the address to identify what system in our environment was actually doing the call us.

Import-Csv .\requests.csv |
sort Method,IP -Unique |
select *,@{Name="Host";Expression={[Net.Dns]::GetHostEntry($_.IP).HostName}}
Export-Csv .\requests-filtered.csv

Most lines should be self-explanatory, but the neat bit is the select statement. I use the * to select all existing properties, and then I use a nutty syntax I learned today to add a calculated property.

Pretty neat. Worked great, with the exception that every IP was resolvable, but’s the fault of the DNS server at the company… 🙂

XSLT: Generate comma-separated string from element values

This is one way to create a comma-separated list of values from a set of elements.

<xsl:for-each select="$orders/cm:Error">
    <xsl:value-of select="cm:errorCode"/>
    <xsl:if test="position() != last()">
        <xsl:text>,</xsl:text>
    </xsl:if>
</xsl:for-each>
<!--source http://stackoverflow.com/a/668850/39321 -->

This is another.

<xsl:for-each select="$elements/Error[1]">
    <xsl:value-of select="errorCode"/>
    <xsl:for-each select="following-sibling::Error">
        <xsl:value-of select="concat(',',errorCode)"/>
    </xsl:for-each>
</xsl:for-each>
<!--source http://stackoverflow.com/a/666600/39321 -->