TypeScript serverUtil.csvRow函数代码示例

async function dataExport() {
    await db.connect()

    const slugs = (await fs.readFile("/Users/mispy/tmp/urls.txt", "utf8")).split("\n").filter(s => s.trim())
    const slugToId = await Chart.mapSlugsToIds()
    const idsToGet = slugs.map(slug => slugToId[slug])

    const variables = await db.query("SELECT v.name, v.id FROM variables v JOIN chart_dimensions cd ON cd.variableId=v.id WHERE cd.chartId IN (?)", [idsToGet])
    const variableIds = variables.map((v: any) => v.id)
    const stream = fs.createWriteStream("/Users/mispy/tmp/sdgs.csv")

    // From dataset CSV export
    const csvHeader = ["Entity", "Year"]
    for (const variable of variables) {
        csvHeader.push(variable.name)
    }

    const columnIndexByVariableId: {[key: number]: number} = {}
    for (const variable of variables) {
        columnIndexByVariableId[variable.id] = csvHeader.indexOf(variable.name)
    }

    stream.write(csvRow(csvHeader))

    const data = await db.query(`
        SELECT e.name AS entity, dv.year, dv.value, dv.variableId FROM data_values dv
        JOIN variables v ON v.id=dv.variableId
        JOIN entities e ON dv.entityId=e.id
        WHERE v.id IN (?)
        ORDER BY e.name ASC, dv.year ASC, dv.variableId ASC`, [variableIds])

    let row: string[] = []
    for (const datum of data) {
        if (datum.entity !== row[0] || datum.year !== row[1]) {
            // New row
            if (row.length) {
                stream.write(csvRow(row))
            }
            row = [datum.entity, datum.year]
            for (const variable of variables) {
                row.push("")
            }
        }

        row[columnIndexByVariableId[datum.variableId]] = datum.value
    }

    // Final row
    stream.write(csvRow(row))

    stream.end()

    await db.end()
}

    // Export dataset variables to CSV (not including metadata)
    static async writeCSV(datasetId: number, stream: Writable) {
        const csvHeader = ["Entity", "Year"]
        const variables = await db.query(`SELECT name, id FROM variables v WHERE v.datasetId=? ORDER BY v.columnOrder ASC, v.id ASC`, [datasetId])
        for (const variable of variables) {
            csvHeader.push(variable.name)
        }

        const columnIndexByVariableId: {[key: number]: number} = {}
        for (const variable of variables) {
            columnIndexByVariableId[variable.id] = csvHeader.indexOf(variable.name)
        }

        stream.write(csvRow(csvHeader))

        const data = await db.query(`
            SELECT e.name AS entity, dv.year, dv.value, dv.variableId FROM data_values dv
            JOIN variables v ON v.id=dv.variableId
            JOIN datasets d ON v.datasetId=d.id
            JOIN entities e ON dv.entityId=e.id
            WHERE d.id=?
            ORDER BY e.name ASC, dv.year ASC, v.columnOrder ASC, dv.variableId ASC`, [datasetId])

        let row: string[] = []
        for (const datum of data) {
            if (datum.entity !== row[0] || datum.year !== row[1]) {
                // New row
                if (row.length) {
                    stream.write(csvRow(row))
                }
                row = [datum.entity, datum.year]
                for (const variable of variables) {
                    row.push("")
                }
            }

            row[columnIndexByVariableId[datum.variableId]] = datum.value
        }

        // Final row
        stream.write(csvRow(row))

        stream.end()
    }

async function main() {
    const trendsPath = "/tmp/trends.json"

    let data: {[key: string]: any} = {}

    // Only fetch for queries we don't already have
    if (fs.pathExistsSync(trendsPath)) {
        data = JSON.parse(await fs.readFile("/tmp/trends.json", "utf8"))
    }

    const titles = await getTitles()
    for (const title of titles) {
        if (title in data)
            continue

        console.log(title)

        try {
            const related = await googleTrends.relatedQueries({ keyword: title })
            data[title] = related
        } catch (err) {
            data[title] = JSON.stringify({})
            console.error(err)
        }

        await fs.writeFile('/tmp/trends.json', JSON.stringify(data))
    }

    console.log("DONE")

    const rows: string[][] = [[]]
    let i = 0
    for (const title in data) {
        const datum = JSON.parse(data[title])
        if (datum && datum.default && datum.default.rankedList[0].rankedKeyword.length) {
            rows[0][i] = title
            // const ranked = datum.default.rankedList[0].rankedKeyword.map((v: any) => `${v.value} ${v.query}`)
            let j = 1
            for (const keyword of datum.default.rankedList[0].rankedKeyword) {
                if (!rows[j])
                    rows[j] = []
                rows[j][i] = `${keyword.value} ${keyword.query}`
                j += 1
            }
            i += 1
        }
    }

    let csv = ""
    for (const row of rows) {
        csv += csvRow(row)
    }

    await fs.writeFile('/tmp/trends.csv', csv)
}

 ...data.map(json => csvRow(headers.map((header) => json[header])))

function toCSV(headers: string[], data: any[]) {
    return [
        csvRow(headers),
        ...data.map(json => csvRow(headers.map((header) => json[header])))
    ].join('') // csvRow() already adds newlines
}