You can download CSVs and transfer the data into a database, copy batches of files and securely manage files with the utilities in this month’s toolbox.
During a recent ETL project, I needed a quick way to search a set of CSVs for particular values and change them to a known state for testing. I also had to download a set of more than 40 different CSVs, transform that input data into a useable form and then load it into a database for reuse. After all, the acronym ETL stands for Extraction, Transformation and Load.
One free, open source tool that can help you do just that is grepWin from Stefan Kueng (of TortoiseSVN fame). Like its namesake grep, grepWin lets you use regular expressions to search your file system. You can also replace matches with an alternate value. Unlike grep, grepWin wraps it up in a simple, easy to use GUI so you don’t have to memorize esoteric command-line options to get the job done.
You just have to determine the best regular expression (regex) to find what you’re looking for within the file set. To help in that regard, grepWin has a built-in regex tester. To use the tester, paste sample text into the text box, add your search regex and an optional replacement string, and you can immediately see if it matches what you want to match or replaces what you meant to replace.
The utility also supports general text search and replace within files. Once you have your search and replace patterns ready to go, pick a directory to search and click Search or Replace to set the utility into action. As the search progresses, your results show up in the bottom pane of the application window. Right-click on a search result for a standard Windows Explorer context menu along with options to open the containing folder, to copy the path to the clipboard and to copy the filename to the clipboard. You can also double-click to open a file with the default associated opener.
The grepWin utility has a number of other options to tailor your search, including case-sensitive searches, creating backup files for those files modified by a regex replacement, and treating files as UTF8 for international character sets. You can also add frequently used regular expressions to a preset list for reuse. To focus on your search target, you can limit the search to files of a particular size or name pattern, as well as excluding directories based on another regex. You can include or exclude system items, hidden items, subfolders and/or binary files.
The results pane has a toggle option to either show you the list of files (the default setting) or the actual content match and line number for every file and match that matched the pattern provided. All in all, grepWin is a solid, easy to use and speedy “grep”-like tool for Windows. Best of all, it’s free (donations are accepted). The next time you need to do a multi-file search and/or replace, check it out.
Figure 1 grepWin
Copying files is one of the most common activities for any computer user. With the increasing sizes of today’s backup, .ISO and .ZIP files, you don’t want to have to wait forever for files to get from here to there. One tool to help you get your files there faster is TeraCopy from Code Sector.
TeraCopy uses asynchronous dynamic buffering, and has built-in retries if it encounters a copy error. If it encounters too many errors, it will skip that file. It will still, however, copy the remaining items so you won’t have to restart a giant operation if there are issues with only one file.
TeraCopy integrates with Windows Explorer. It replaces the normal copy/move/paste operations with a call to itself. When a file or set of files is copying, you’ll see the applications interface. Click More for a view of the pending copy queue, as well as stats on the current copy operation. The context menu on the queue will let you retry failed items and remove items from the list, so you can adjust dynamically. This is a helpful feature for copying large sets of files.
You can also pause and resume copy operations with TeraCopy. This lets you get back some bandwidth if you need it for another operation. Another nice feature is the operations drop down, so you can see what operations have occurred. This saves a little time during those “Did I just copy that or move it?” moments.
TeraCopy has full Unicode support, as well as support for Windows 7 x64. TeraCopy is free for home users, but if you want to use the application in your office, you have to buy TeraCopy Pro. TeraCopy Pro costs €14.95 (about $20 U.S.). The commercial version provides a few more features that the home version, including free updates and priority support. If you’re looking for more performance during copy/paste operations, give TeraCopy a try.
Figure 2 TeraCopy
If you’re looking for an SSH2 or SFTP tool to help you securely manage your systems or transfer files, Bitvise Tunnelier might do the trick. In terms of SFTP, Tunnelier supports automatic resume, auto-text file awareness and full directory recursion for transferring deep folder structure automatically. You also get terminal access via VT-100 or xterm protocols.
The Tunnelier remote console feature is handled within a standard Windows console, using the same fonts and settings as your default console. This gives you a familiar and customizable view of your remote session. Besides the default graphical client for terminal access, SSH and SFTP, Tunnelier also includes a command-line component. This helps you script and schedule your SFTP or SSH operations.
Port forwarding is another of the product’s cool features. The application supports dynamic tunneling via proxy, so you can steer your applications to a desired port. The utility also touts “one-click” remote desktop tunneling, so you can limit the exposed ports on your servers, ensuring an extra layer of security. Tunnelier also supports single sign-on when connect to a GSSAPI-enabled SSH server, making connections to remote systems within the same or a trusted domain a no-brainer.
You won’t have to worry about managing passwords, keys and the like (though the application supports those options). Tunnelier also supports profiles, so you can store your favorite settings and not have to re-establish them every time you want to connect to, tunnel or remote access a different environment.
You also get “sexec” with the utility, which gives you a remote command-line execution client. This is great for remote deployment or file operations. You can redirect sexec output to capture the remote execution output and write it to a log file for later review. There’s a PortableApps format you can run from a thumb drive (see my PortableApps overview from 2008 here).
Tunnelier is free for individual use. For corporate use, Tunnelier sells for $44.95 for a single installation. A site license will cost $5,000. If you’re looking for a SSH server to pair with Tunnelier, take a look at their WinSSHD, which I reviewed in 2007.
Figure 3 Tunnerlier SSH Client