CSVed to the Rescue
If you work with large text input files in your BI process, a good CSV/Text editor is a must-have addition to your toolkit. In my ETL workflow, there are always text/CSV files to load, error files to view or one-time files I need to modify. The biggest feature you need is to page through large files as some will not fit into memory. I found a great CSV editor that I think will fit your data workflow also.
This is especially important for quick turnaround demos or projects where you cannot get revisions or corrected input files.
I stumbled across CSVed, located on Sam Francke’s site, after having many issues with trying to edit larger files. This may be one of the best large file tools I have ever used. It is packed with great features, such as;
- Double click a row, and a view/Edit form pops up, making examining, even editing long files a snap.
- Once the file is loaded, you can change settings and refresh your view, which saves the step of re-opening the file, great for finding a pipe-delimited file that you thought was a comma
- Full edit to add, insert or delete rows
- Great search and replace that can be specified by column
- Split and join columns and save as a new file
- Export to XML files
- If your file has column headings, you can set the column number as a prefix that debugging far easier when looking at error files in SSIS or files that do not have the headers.
My Use Case
I have an ETL process that imports flat files from a source system which can be a couple of hundred meg. I have SSIS generate error files out of this process which is saved as text files. However, these are comma-delimited text files that correspond to the input files that I am loading. However, they do not have headers. Having both the error and source files open with column numbers makes debugging a lot easier. I have highlighted some of the key feature points listed above in this snapshot. (Click for larger image)
Where To Get It
The latest version is 2.5.6 published in September 2020. http://csved.sjfrancke.nl/index.html
Check it out. Always glad to share out a new find. Let me know your opinion or any tools you use in your workflow down in the comments.