Member Avatar for new_2_java

Hello everyone,

I am trying to merge large number of CSV files into one, using the following "awk" command, but if I increase the number of files more than 10,000, it then gives me "argument too long" error.

Can someone suggest how I can manage to go beyond the limit? I am looking to merge some ~100,000 csv files together.

awk '{print $0"\t"FILENAME}' *.csv >  merged.csv

Thanks.

  • 4 Contributors
  • 5 Replies
  • 1K Views
  • 17 Hours Discussion Span
  • Latest Post Latest Post by Gribouillis

Dani AI

Generated

The error comes from the shell expanding *.csv into a very large argument list; that list is handed to exec and can exceed the system limit (E2BIG / ARG_MAX). Check your limit with getconf ARG_MAX and read the execve(2) note on E2BIG for the exact failure mode. (See execve(2) and getconf documentation.)

Two reliable approaches that avoid that problem: (A) feed filenames through find + xargs so the command is run in batches (safe, portable, and avoids the glob limit), or (B) iterate the directory from a program that opens each file one at a time (no giant argv). Option B also makes it easy to append the source filename as a new column and to skip repeated headers.

A compact, memory‑safe Python example (streaming, optional header-skip):

#!/usr/bin/env python3
import os, sys, csv

srcdir, out = sys.argv[1], sys.argv[2]
skip_header = '--skip-header' in sys.argv
out_abspath = os.path.abspath(out)

with open(out, 'w', newline='', encoding='utf-8') as fout:
    writer = csv.writer(fout)
    for entry in os.scandir(srcdir):
        if not entry.is_file() or not entry.name.lower().endswith('.csv'):
            continue
        if os.path.abspath(entry.path) == out_abspath:
            continue
        with open(entry.path, newline='', encoding='utf-8') as fin:
            reader = csv.reader(fin)
            first = True
            for row in reader:
                if first and skip_header:
                    first = False
                    continue
                writer.writerow(row + [entry.name])
                first = False

Run like: python3 merge_csvs.py /path/to/csvdir /path/to/merged.csv --skip-header. Put the output outside the input folder if possible (or let the script exclude it). This follows 's advice to avoid globbing and expands on and by offering both a batching and a streaming solution. References: execve(2) and ARG_MAX info (see execve(2) and getconf), GNU findutils docs for -print0 | xargs -0, and Python's csv/os.scandir docs for streaming IO.

Links: execve(2) (https://man7.org/linux/man-pages/man2/execve.2.html), getconf (https://man7.org/linux/man-pages/man1/getconf.1p.html), GNU findutils (https://www.gnu.org/software/findutils/manual/html_mono/find.html), Python csv (https://docs.python.org/3/library/csv.html).

Recommended Answers

All 5 Replies

Member Avatar for rproffitt

To me this sounds like our old question of how to merge that many files. While I won't ask why not cat it, I would break it up a little with say 26, 36 or 62 commands in your script. Support I did a..z (a.csv, b.csv...z.csv) and then 0 to 9 and so on.

Remember that this will take a long time since you remember what happens to folder/file speed with you put tens of thousands of files in a folder. You can help it along by putting the merged.csv in another folder.

Member Avatar for Gribouillis

Use python

Member Avatar for rubberman

This is most likely a command line length problem. I'd use a bash shell for loop and as rproffitt didn't suggest I would use cat to merge each candidate file into the merged.csv file.

Member Avatar for new_2_java

Thanks for the suggestions.

The reason why I didn't (or maybe I could?) use 'cat' is because I want to also append the name of the file as a collumn within my "merged.csv" file. i.e. if we look at these 2 CSV files:

file_1.csv
1, 2, 3
2, 3, 4

file_2.csv
a, b, c
b, c, d

After merge, I would want something like this:

merged.csv
1, 2, 3, file_1.csv
2, 3, 4, file_1.csv
a, b, c, file_2.csv
b, c, d, file_2.csv

Reason is, I need to keep track of each record where (which file) they came from.

Member Avatar for Gribouillis

You could start with

find /absolute/path/to/csv/directory -maxdepth 1 -name "*.csv" > ~/csvlist.txt

this would create a file with the list of all csv files to process.

Edit: Then you would ask a script to traverse csvlist.txt and call a command

awk '{print $0"\t"FILENAME}' fubar.csv >>  ~/merged.csv

for each filename met. Note that your command seems to insert a tab instead
of a comma in the csv file. Check the csv dialect and encoding.

Edit: changed command to avoid * in arguments

Edited by Gribouillis
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.