3

I'm importing a large set (126M rows) of file data from CSVs. Import works fine, except I'm trying to derive a column as I import rather than trying to script it out afterward (due to the number of rows). SSIS is not terribly helpful with string manipulation functions, and the possibilities I have are pretty complicated as well.

The derived column needs to reflect the file extensions. File extensions range from 1-10 characters (anything longer can be truncated at 10), and are (of course) separated from the file name and path by a period. They don't correspond to a concise list of file types (something like "docx, xlsx, accdb, msg" are Office types). The file path may also have periods in it. And in some cases it will not have files at the end of the path, just a folder name itself. Some examples:

\\Server\Share\named resource
\\Server\Share\this.looks.like.a.file_but.it.isnt\its_a_directory
\\Server\Share\Group\Year.Month.Day.Subfolder\File.ext
\\Server\Share\Team Folder 1.404\another.file.here.extension

So I need to be able to extract the extensions up to 10 characters. I tried TOKEN() but with folder names possibly having periods, that didn't work well - adding \ as a token delimiter helped some, but I still got odd extensions from folder names. I can't find a combination of SUBSTRING(), RIGHT(), FINDSTRING(), and/or TOKEN() that will meet the rules and the derived column definition doesn't allow logic like IF or IIF.

Some false positives are expected and I plan to sort them out after importing. If it helps here, I have a second column in the CSVs which is extracted by SSIS, it's the size of the file (for folders it populates a 0). I haven't gotten this to matter either, because of the lack of IF or IIF in derived column definition.

2

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator

? Conditional

One way to implement the Derived Column would be to parse the string like this

 TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1
 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) )

In this case, "name" is the column that holds the string to be parsed.

What we are doing here is:

  1. Remove any trailing white-space with RTRIM( name )
  2. Reverse the string so we are dealing with the end as the beginning with REVERSE()
  3. Grab the first "token" delimited by a backslash using TOKEN()
  4. Count how many "." delimiters are in the result using TOKENCOUNT()
  5. Test the count of "." delimited tokens is equal to 1 using ?
  6. If the count is 1, return an empty string - no extension.
  7. Otherwise return the first token before the "." using TOKEN()
  8. Reverse the extension to get back to "normal" using REVERSE()
1

This isn't in the expression language (it's T-SQL) but you should be able to use this as either a pattern or to pull the data from SQL (if that's where it is).

CREATE TABLE #test (Col1 varchar(100))

INSERT INTO #test VALUES 
    ('\\Server\Share\named resource'),
    ('\\Server\Share\this.looks.like.a.file_but.it.isnt\its_a_directory'),
    ('\\Server\Share\Group\Year.Month.Day.Subfolder\File.ext'),
    ('\\Server\Share\Team Folder 1.404\another.file.here.extension')

SELECT CASE WHEN CHARINDEX('.',REVERSE(Ending)) > 0 THEN 
    RIGHT(Ending,CHARINDEX('.',REVERSE(Ending))-1) ELSE '' END
FROM #test
CROSS APPLY (VALUES  (RIGHT(Col1,CHARINDEX('\',REVERSE(Col1))-1))) x(Ending)

If you are using this as a pattern you can create a derived column for ending then apply the rest of it to that column. You could really do it in one pass but it's much more complicated that way.

  • Thanks, I figured it would be a bit easier in TSQL, if only because I'm more familiar with that. But I was trying to avoid it because of the size of the data; prior to creating a couple indexes it takes several minutes to run a simple query like Count(*). – Fred Shope Feb 9 '16 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.