fread for huge fixed width files with smart handing of trailing spaces

[mikedolanfliss]

Hi there, big fan of DT and fread.

I've got large (>4gig) files dumped out as flat, undelimited, fixed-ish* width files from SQL server. I say "-ish*" because, and I guess this is standard, the files have regular start positions but no true fixed width to the lines. That is, there may be trailing spaces or not on the final element in the record - so I know where every field starts, but not where every record ends. Of course, this is dumb, but SQL server is likely not to change its ways, and I don't have control of the output (to get in CSV, etc.)

Could fread be extended for true fixed width and fixed-widthISH files like this? Existing fwf readers (LaF, read.fwf) can't handle this problem yet either. Stack Exchange pros have suggested a pretty complicated awk work around... but it'd be nice to have it push-button in fread.

Thanks for considering!
mike

Sidenote: SQL also dumps a format file, something like below, that I can use to easily suck out start positions for the fields:
https://msdn.microsoft.com/en-us/library/ms191516.aspx

[jangorecki]

You should provide head of your file - it would allows to see if such format would be difficult to handle or not.
The awk seems to be the best for such cases, not sure if fread should evolve into direction of handling tons various extracts, each by a single application. It could, but it would be a cost of fewer other features. If you can dump your sql server data into SQL insert script then there is a awk described for those in #878.

[arunsrinivasan]

@mikedolanfliss thanks for filing. To answer your Q on SO, I don't see a reason for why not to. Perhaps it'll not be done immediately, as there are other FRs we'd like to get to first. But definitely nice to have it on the list. Thanks again.

However, it'd be nice if you could provide a link to the file (if big, zipped). There's not much to go with without it.

[MichaelChirico]

I, too think it would be great for fread to be able to handle fixed-width reading directly and concisely...

That said, there are some excellent options out there which weren't mentioned, e.g. iotools' dstrfw, as well as jerry-rigging fread itself to work (in combination with stringr). See here:

http://stackoverflow.com/a/34190156/3576984

[MichaelChirico]

I don't think adding FWF support to {data.table} is on the horizon...