3

I have a .csv file which is 7+ GB and has 30M rows. When I load the file into a database table in SQL Server, it reports the position of a row with some format problem, probably due to some fields missing.

I can see the row with the position (i.e. row number), by using less

less +24923786 -N huge.csv

I try to open it in Emacs, but it is too big for Emacs to open. Is it normal? (I am actually using less in git bash, and portable Emacs in Windows. So I don't know if full fledged Emacs can open it and allow me to edit the row.)

How can I edit the row in the csv file? (I am interested in a tool in Linux, or some editor available in git bash, or having a portable version for Windows)

Thanks.

8
  • 2
    Depends what you mean by "edit". Do you mean open in a text editor, or will applying a regexp replacement (with say sed) suffice?
    – spinkus
    Jan 12, 2023 at 21:23
  • I just want to have ,, for each missing field in that row. If you have a sed command to do that, that will be great. I hope it is available in git bash or has some portable version in Windows.
    – Tim
    Jan 12, 2023 at 21:24
  • Emacs has to my understanding some assumptions on its data structures that makes it slow for large files. If you have a 64-bit vim it should be able to edit it (but might need ekstra disk space). Jan 12, 2023 at 21:44
  • 2
    I feel like this is a good illustration of why ambiguously parseable formats like CSV aren't sensible for exchange of large datasets. Anyway, wouldn't it be more sensible to set up your CSV parser in a way that definitely understands all rows correctly? If the thing goes visibly wrong on line 24923786, how much trust do you really have that there's no data ending up in the wrong columns on the other lines? This question reads like you have a big red flashing warning light and are trying to find the most elegant way of draping a blanket over it, so that it doesn't distract you as much. Jan 12, 2023 at 23:42
  • 2
    Regarding I just want to have ,, for each missing field in that row. - edit your question to describe how a tool could know which field(s) are missing and provide sample input/output. Once you can describe the edit, you should be able to just tell sed or awk to make that change to that line. You will need an extra 7+G of storage of course.
    – Ed Morton
    Jan 13, 2023 at 1:18

2 Answers 2

4

ed could edit a single line very easily. c is the command to change (replace) a line; the period on a line by itself is the signal that you're done adding text; wq writes the changed file back to disk and quits ed:

$ ed huge.csv
24923786c
new,data,goes,here
.
wq

Or as a bash "one"-liner:

$ ed -s huge.csv <<< $'24923786c
new,data,goes,here
.
wq
'
1
  • 1
    The problem with this is that ed reads the whole file into memory. It would be better if the file could be edited in sed with e.g. sed '24923786 s/something/other/' or with a c command or what's needed.
    – Kusalananda
    Feb 2, 2023 at 16:26
3

If you want to actually edit the singular line in an editor you could use sed like this to extract line, edit then splice back in (supposing a compatible version or sed is in git-bash - it should be):

sed '24923786,24923786p' -n huge.csv  > line.txt
edit line.txt
sed '24923786,24923786{s/.*/cat line.txt/e;}' -i huge.csv

Please test first and take a backup (you can use suffix argument to i option like -i.bak to have sed automatically make a backup of the original file to huge.csv.bak).


Aside: I'd be surprised if there wasn't some editor out there that had specific support for this - opening and allowing editing of a range of lines in huge text file - but I'm not aware of it. All I know is gedit (my goto editor) crashes badly with slightly largish files and vscode seems to do a lot better, but haven't had to deal with it beyond that lately.

7
  • Thanks. Do you have a gawk command equivalent to that?
    – Tim
    Jan 12, 2023 at 21:48
  • Nah I hate awk. sed isn't in git-bash?
    – spinkus
    Jan 12, 2023 at 21:51
  • both are in git bash. I used gawk more than sed.
    – Tim
    Jan 12, 2023 at 21:54
  • sed -i makes a copy of the file, then shuffles the name. So whatever you do will need another 7GB and a full copy. I would use head and tail | head to split the file into 3 files A B C, lines 1..n-1, line n, and lines n+1..end. Fix the single bad line by hand, then append files B and C to A. As your n is 25M out of 30M, B and C are relatively small. Jan 12, 2023 at 22:07
  • A thought about the size of the A section of the file. You could use tail +n to slice off copies of the B and C parts first. Then get their sizes from ls -l, and shorten file A with truncate --size=-nnn. Then append the fixed B, and C, to A. That avoids copying the first 25M lines of the original. Jan 12, 2023 at 22:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .