How to DEFINE REGEX for CSV FILE

  • 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..

  • 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]

  • HI Keith,

    Yes i am doing in SSIS,

    Reagrds

    Shaun

  • 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]

  • 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]

  • 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

  • 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]

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

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