1

I'm doing a lot of data merge printing and would like to be able to output, say, 10 samples from a large database. My goal would be to extract the 10 rows spread evenly from the supplied .csv (including the first and last row) and output this as a new .csv file.

  • 1
    share your sample input and expected output set – bluefoggy Feb 11 '15 at 5:01
  • In a CSV a row can span multiple lines because (quoted) cells can have newlines. grep is not going to deal with that correctly. If you are dealing with some restricted subset data without newlines, describe that exactly (and don't call it CSV) – Anthon Feb 11 '15 at 5:44
  • You should not use "spread evenly" rows for a sample. That makes your sample vulnerable to repeated patterns (eg, you may pull the same time each day). Pre-generate 10 random numbers with the range [1, num_rows] and then select those records. Throw an error if any row numbers are the same, as then the population is too small. – vk5tu Feb 11 '15 at 12:12
2

This answer assumes that your csv file has one line per row, meaning that there are no continued lines. If the file is called db.csv, then you can get 10 lines, including the first and the last by using:

awk 'FNR==NR{next} FNR==1{n=NR-1} FNR>x || FNR==n{x+=n/9;print}' db.csv db.csv

How it works

Because the csv file is listed twice on the command line, awk will read through it twice. The first is used to get the total number of lines. The second time is used to print the 10 selected lines.

  • FNR==NR{next}

    NR is the total number of records (lines) read so far.The file record number, FNR, is equal to the total number of records (lines) read so far from this file. So, when FNR==NR, we are still reading the first file. If so, we just jump to the next record.

  • FNR==1{n=NR-1}

    If we reach this command, that means that we are on the first line of the second read through. In that case, we know that the total number of records in the file is NR-1. We save this number as n.

  • FNR>x || FNR==n{x+=n/9;print}

    One the first line of the second read through, FNR==1 and x=0. Thus, FNR>x and we print that line. We then increment x by n/9 and print a line the next time that FNR>x and so on.

    The condition FNR==n assures that the last line in the file will be printed.

Example

Let us create a file with 101 lines:

$ seq 101 >db.csv

Now, we can use our awk command to print 10 lines from the file, including the first and last:

$ awk 'FNR==NR{next} FNR==1{n=NR-1} FNR>x || FNR==n{x+=n/9;print}' db.csv db.csv
1
12
23
34
45
57
68
79
90
101
0

Try the following

SELECT your_column1, your_column2
FROM your_table_name
INTO OUTFILE '/tmp/ouput.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

That works for linux on windows replace

INTO OUTFILE '/tmp/output.csv'

with

INTO OUTFILE 'c:\output.csv'
0

If your CSV files are not restricted and contain newlines, and commas within field values, you should use a real CSV parsing library e.g. the one available standard in python:

import csv

def select_evenly(file_name, nr):
    rows = []
    with open(file_name) as fp:
        for row in csv.reader(fp):
            rows.append(row)
    nr_rows = len(rows)
    step = (nr_rows - 2.0) / (nr - 1)
    yield rows[0]
    for x in range(1, nr-1):
        yield rows[int(x * step + 0.5)]
    yield rows[-1]

for line in select_evenly("test.csv", 10):
    print line

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.