Posts Tagged ‘csv’

Extract Rows from Files using QGrep

Whether you work with enterprise Data Warehouses, departmental Data Marts or live operational OLTP systems there’s a good chance that at some point you’ll have a need to import or export data using flat files.  Flat files are great to read because they’re typically in common easy to understand formats such as CSV, the problem comes when you’ve got extremely large volumes of data that exceed your ability to handle them in Excel (e.g. over 65,536 rows for Excel 2003) or when you absolutely must preserve the initial file format.

I had a requirement recently to extract all rows relating to just one customer from an input file containing tens of thousands of rows and I had to leave the structure intact so that the existing SSIS loader would recognise the file.  Sadly the standard Windows command-line utilities are a bit lacking in this regard but you can download the Windows Server 2003 Resource Kit which among other things includes the QGrep tool.

QGrep is simply a Windows equivalent of the Grep command available in Linux and Unix, there is no GUI but if you’re doing something simple the syntax is fairly straight-forward (see SS64.com for more info), in my case I needed to extract all records from “input.csv” containing the CustomerID “476226235076″ and place them in “output.csv” so the command was…

qgrep “476226235076″ inputs.csv > output.csv