News

Tweets












September 2011 Entries


How to Remove Quotes and Replace the Delimiter in a File using PowerShell Preserving the Commas

Recently I was tasked with a file cleansing exercise. The endeavor looked simple, but the solution was not, I needed to prepare a file for processing, but wanted to first proof concept my approach so I opted to utilizing this opportunity to learn PoweShell. More about the task: I was supplied with a series of CSV files in which the data looked like 123,ABC,XYZ "1,2,3", ABC, "XY,Z" 123,"A,BC", XYZ As you can see this is something I think we may call alternating double-quotes comma delimited file with ......

SSIS: How to Remove Occasional Quotes and Replace The Column Delimiter

Recently I was assigned a task of helping a developer to cope with an unusual input file processing. The file we needed to load into SQL Server had double quotes surrounding those fields that contained comma(s). So a short excerpt from it would resemble something like: 123,ABC 456,"D,E,F" "7,89",GHS Since SSIS’s Flat File Connection does not support alternating text qualifiers, the chief difficulty stems from the fact if we even only remove the quotes, the file structure becomes broken because the ......

How to Run a PowerShell Script From Command Line Both With Spaces in Path And Input Parameter

This post is going to be the shortest of my all. Recently, to my surprise, I found myself developing quite a lot with PowerShell, it is not that I have never written scripts, but those mostly were VBScripts. To start off I can tell that there is a ton of examples on the World Wide Web, and I know of nine free PowerShell books, alas I had no time to read any due to very short project deadlines and when I needed a specific example the search results returned were exhaustive too often, yet incomplete. ......