Geek asks and answers

How to remove spaces when importing a flat file containing intermediate blank lines?

I have a very simple text file containing two values, separated by a comma, whose length is about 100 lines. This file is created by an automatic process (which I cannot control), and I import this file into SQL via SSIS.

My work works very well, unless there is an empty line in the file. By this I mean that it is completely empty - no commas or other characters. When this exists in the file, the record immediately after import will be imported with two spaces before the imported value.

For example, if a text string contains this “ABC, 123”, the imported SQL value will be “ABC” for the first column. I tried to remove this using a derived column with a TRIM statement, but this did not affect. The REPLACE function also did not work. The really strange part is that if I add a data viewer just before the data stream, the value will look normal. I even added asterisks so that I could “see” spaces if they exist, for example:

"*" + REPLACE([Column 0]," ","") + "*"

This is an extremely annoying problem, and I would really appreciate any suggestions. Thank!

+3
source share
3 answers

. Script Component .

  • , , . .

file with issue

  • OLE DB, , , .

invalid data

  • , script OLE DB. script, "".

transformation

  • .

data flow task

  • script, script. , . Name.

input columns

  • " " CleansedData string. , .

inputs and outputs

  • script Edit script, script.

script

  • script Input0_ProcessInputRow, . + , , .

Script :

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.CleansedData = Row.Name.Replace(@"\r\n", string.Empty).Trim();
}

script code

  • OLE DB CleansedData .

  • , . .

, .

clean data

+9

- - . CRLF, :

LTRIM(REPLACE(REPLACE([Column 0],"\n",""),"\r",""))
+1

It was very helpful! I did not need to clear the gaps, and sometimes to clear, and this post showed me exactly what I needed.

This may not be the right forum to post, but here is a snippet of my code:

    DateTime dt = DateTime.Now;
    string str_test = "";

    try
    {
        str_test = Row.SomeDate;

        if (str_test.Length == 8)
        {
            // date example: 20151231
            str_test = str_test.Insert(6, @"/");
            str_test = str_test.Insert(4, @"/");

            dt = Convert.ToDateTime(str_test);
            Row.CleansedDate = Row.SomeDate.;
        }
        else
        {
            // test for some other date
            dt = Convert.ToDateTime(str_test);
            Row.CleansedDate = Row.SomeDate;
        }
    }
    catch (Exception)
    {
        // this is not a date, return nothing
        Row.CleansedDate = null;
    }
0
source

More articles:


All Articles