How do I remove spaces while importing flat file containing intermediate blank lines?
sql, ssis
Solution
Here is one way to do it. You can use `Script Component` transformation inside the Data Flow Task to clean the data.
- Here is a sample file that represents the data similar to your issue. Notice the second line with two spaces and no actual data.
- When the file was imported directly into the table using a flat file source and OLE DB Destination, there was a blank issue before the second row imported into the file.
- To fix the issue a script component transformation has to be introduced between flat file and OLE DB destination. When you drag and drop a script component, select Transformation.
- Your data flow task would look something like this.
- Double-click on the script component to bring the Script Transformation Editor. On the Input columns, select the first column that is being read from your file. Here in this case, the column is `Name`.
- On the Inputs and Outputs section, create a new column named `CleansedData` of data type `string`. This new column will hold the clean output which is devoid of spaces.
- On the Script section, click on the Edit script button to bring the Script editor.
- Within the script editor, change the code in the method `Input0_ProcessInputRow` as shown below. this code replace the carriage return + line feed with blank text and then trims any spaces surrounding the text.
Script code:
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.CleansedData = Row.Name.Replace(@"\r\n", string.Empty).Trim();
}
Now, in your OLE DB destination, replace your old column with this new column `CleansedData` in the column mapping section.
After making the above changes, the package was executed against the same file containing blank spaces. this time no spaces were inserted before the second row.
Hope that helps.
Problem
I have a very simple text file that contains two comma separated values that is about 100 lines long. This file is created by an automated process (that I cannot control) and I import this file into SQL via SSIS. My job works very well except when there is a blank line within the file. By this, I mean it is completely blank - no commas or other characters. When this exists in the file, the record directly after it will be imported with two spaces before the imported value. For example, if the text line contains this "ABC,123", the imported SQL value will be " ABC" for the first column. I have tried to remove this by using a derived column with the TRIM statement, but that had no effect. The REPLACE function also did not work. The really strange part is that if I add a Data Viewer directly before the data flow Destination, the value looks fine. I even added asterisks so that I could "see" the spaces if they exist, like this: ``` "*" + REPLACE([Column 0]," ","") + "*" ``` This is an extremely annoying issue, and I would greatly appreciate any suggestions. Thank you!