How to DEFINE REGEX for CSV FILE

Question

How to DEFINE REGEX for CSV FILE

Shaun2012

Hall of Fame

Points: 3214
More actions
September 30, 2013 at 10:29 am

#298423

Im transfering data from csv file and it is fixed format and only date changes. so i want to split the date and name to endter into table
20130930_Get_MY File Name.csv
So in table should enter like below
date_name :20130930
file name: Get_MY File Name
anyone have idea.
regards
Shuan..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

This would be very easy to do in SSIS. Is that possible? or does it have to be done with a stored procedure or T-SQL code outside of SSIS?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 2

Shaun2012

Hall of Fame

Points: 3214

September 30, 2013 at 10:40 am

#1654646

HI Keith,

Yes i am doing in SSIS,

Reagrds

Shaun

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 3

Using a ForEach File loop on your folder you can set the filename as a parameter. Once inside the container set two variables using SSIS expression (date and file name) as follows:

Date = SUBSTRING(name,1,FINDSTRING(name,"_",1) - 1)

File name = SUBSTRING(name,FINDSTRING(name,"_",1) + 1,100)

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 4

Using a ForEach File loop on your folder you can set the filename as a parameter. Once inside the container set two variables using SSIS expression (date and file name) as follows:

Date = SUBSTRING(name,1,FINDSTRING(name,"_",1) - 1)

File name = SUBSTRING(name,FINDSTRING(name,"_",1) + 1,100)

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Shaun2012 Hall of Fame Points: 3214 More actions · Answer 5

HI KEITH,

WHERE TO USE THE EXPRESSION, IM quite new to ssis.

As the name changes but only the date changes:

Date = SUBSTRING(name,1,FINDSTRING(name,"_",1) - 1)

File name = SUBSTRING(name,FINDSTRING(name,"_",1) + 1,100)

For specifying the name

The below was the script used by some developer using in the script editor for the file name "20130930_Get_Usage"

String Filename = Path.GetFileNameWithoutExtension(Dts.Variables["User::csv_filepath"].Value.ToString());

Regex numRegex = new Regex(@"[0-9]+");

Regex typeRegex = new Regex(@"[A-Z]+_[A-Z]+");

Dts.Variables["User::csv_filedate"].Value = (numRegex.Match(Filename)).Value;

Dts.Variables["User::csv_filename"].Value = Filename;

Dts.Variables["User::csv_type"].Value = (typeRegex.Match(Filename)).Value;

Dts.TaskResult = (int)ScriptResults.Success;

Regards

Shaun

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 6

Take a look at this blog post:

http://consultingblogs.emc.com/jamiethomson/archive/2005/03/19/SSIS_3A00_-Evaluating-variables-as-expressions.aspx

It explains how to set variables using expressions.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]