EN VI

Sql-server - Converting numbers with letters into negatives SSIS?

How to Sql-server - Converting numbers with letters into negatives SSIS

I'm importing flat files into a sql database. Said flat files are generated by an older piece of software that can't store negative numbers and thus uses letters instead. p=0, q=1, r=2, etc. and then since you found/replaced a letter you multiply by -1. So if I get 14q, that needs to be converted into -141. I feel like the type conversions will be the trickiest part of this, as I'd need to import them as strings but then multiply by -1 once I replace the letter.

Whatever number you replace with, you multiply by -1.

14p=-140
14q=-141
14r=-142
140=140

Solution:

I see you have 80 columns to apply this too. I would seriously consider a script component with your translation done in a method.

    private static int ConvertToInt(string val)
    {
        //split to translate then recombine
        string firstPart = val.Substring(0, val.Length - 1);
        string secondPart = val.Substring(val.Length - 1);

        switch (secondPart.ToLower())
        {
            case "p":
                return int.Parse(string.Concat("-", firstPart, "0"));
            case "q":
                return int.Parse(string.Concat("-", firstPart, "1"));
            case "r":
                return int.Parse(string.Concat("-", firstPart, "2"));
            case "s":
                return int.Parse(string.Concat("-", firstPart, "3"));
            case "t":
                return int.Parse(string.Concat("-", firstPart, "4"));
            case "u":
                return int.Parse(string.Concat("-", firstPart, "5"));
            case "v":
                return int.Parse(string.Concat("-", firstPart, "6"));
            case "w":
                return int.Parse(string.Concat("-", firstPart, "7"));
            case "x":
                return int.Parse(string.Concat("-", firstPart, "8"));
            case "y":
                return int.Parse(string.Concat("-", firstPart, "9"));
            default:
                return int.Parse(string.Concat(firstPart, secondPart));
        }

    }

//Simply call the method like this inside the standard row processing section.
int newColumn.Value = ConvertToInt(Row.ColumnName.Value);

Note: I added a ToLower() because I see you are mixing case. C# is strict case sensitive.