Extract number,Month name and year from file name

• p.shabbir

  SSCrazy

  Points: 2404

  More actions

  October 15, 2015 at 6:02 am

  #304355

  Hi guys,

  Please help me with below c# script to extract number,three letter month and year from file name.

  EX : 123_My_FileName_JUL15_SPM.dat

  Output as below

  File_Number = 123

  File_Date = JUL15

  I was able to extract number from file name and write it to variable, but not able to get month name & year.

  Here is my code

  String sFile_Path = Dts.Variables["User::Suppress_Desti_File_Path"].Value.ToString();

  MessageBox.Show(sFile_Path);

  Dts.Variables["User::Suppress_Desti_File_Path"].Value = sFile_Path;

  String sFile_Name = Path.GetFileNameWithoutExtension(sFile_Path);

  //MessageBox.Show(sFile_Name);

  //String sFile_Number = sFile_Name.Substring(Substring(Substring(Substring(Substring(0, sFile_Name.LastIndexOf("_"))))));

  Int32 sFile_Number = Convert.ToInt32(sFile_Name .Substring (0,sFile_Name .IndexOfAny("_".ToCharArray ())));

  Dts.Variables["User::File_Number"].Value = sFile_Number;

  String sSupp_File_Date = sFile_Name.Substring(0, sFile_Name.LastIndexOf("_SPM"));

  Last line getting whole file name before "_SPM"

  thanks in advnc.

• michal.lisinski

  SSCertifiable

  Points: 6933

  More actions

  October 15, 2015 at 8:32 am

  #1833523

  Hi

  You can use LastIndexOf() method to find the position of last "_" character in string,

  try in such way:

  //char to find

  String UndSc = "_";

  // (3 + 2) month & year len

  int StrLen = 5;

  //pos of last char -month & year len

  int i = sFile_Name.LastIndexOf(UndSc)-StrLen;

  String m = sFile_Name.Substring(i, 3);

  String y = sFile_Name.Substring(i+3, 2);

  //show three letter of month

  MessageBox.Show((m).ToString());

  //show two digits of month

  MessageBox.Show((y).ToString());

  Best regards

  Mike

• p.shabbir

  SSCrazy

  Points: 2404

  More actions

  October 15, 2015 at 9:36 am

  #1833552

  Thanks for quick response Michal. I got it to work in another way. Just in case if anybody needs I am posting here my whole code.

  String sFile_Path = Dts.Variables["User::Suppress_Desti_File_Path"].Value.ToString();

  MessageBox.Show(sFile_Path);

  Dts.Variables["User::Suppress_Desti_File_Path"].Value = sFile_Path;

  String sFile_Name = Path.GetFileNameWithoutExtension(sFile_Path);

  //MessageBox.Show(sFile_Name);

  //String sFile_Number = sFile_Name.Substring(Substring(Substring(Substring(Substring(0, sFile_Name.LastIndexOf("_"))))));

  Int32 sFile_Number = Convert.ToInt32(sFile_Name .Substring (0,sFile_Name .IndexOfAny("_".ToCharArray ())));

  Dts.Variables["User::File_Number"].Value = sFile_Number;

  MessageBox.Show(sFile_Number.ToString ());

  //String sSupp_File_Date = sFile_Name.Substring(0, sFile_Name.LastIndexOf("_PSFM"));

  //sFile_Name.Substring(0,sFile_Name.LastIndexOf("_PSFM"));

  //string strFilename = "11_RITZ_CARLTON_CONSUMER_OLE_MAR15_PSFM_{69462}.dat";

  if (sFile_Name.Contains("MARRIOTT")) //& (sFile_Name .Contains ("RITZ_CARLTON"))

  {

  string[] ls = sFile_Name.Split('_');

  string Month = ls[4];

  Dts.Variables["User::Supp_File_Date"].Value = Month;

  MessageBox.Show("Mariott :" + Dts.Variables["User::Supp_File_Date"].Value);

  }

  else

  {

  string[] ls = sFile_Name.Split('_');

  string Month = ls[5];

  Dts.Variables["User::Supp_File_Date"].Value = Month;

  MessageBox.Show("Ritz_Carlton :" + Dts.Variables["User::Supp_File_Date"].Value);

  }

• michal.lisinski

  SSCertifiable

  Points: 6933

  More actions

  October 16, 2015 at 1:05 am

  #1833671

  Hi

  I'm glad to see you find your own workaround of problem, but please think about error handling

  to this task. Sometimes the name of file can be totally screw up.

  I had a repository project where users stored pdf documents on the file server, SSIS package copied and indexed files

  into db. Although the naming rules were written up in DD, users made absolute mess. So the error handling implemented in

  package allowed send them back what doesn't meet the naming convention.

  Best regards

  Mike

• p.shabbir

  SSCrazy

  Points: 2404

  More actions

  October 16, 2015 at 4:45 am

  #1833739

  michal.lisinski (10/16/2015)

  ---

  Hi

  I'm glad to see you find your own workaround of problem, but please think about error handling

  to this task. Sometimes the name of file can be totally screw up.

  I had a repository project where users stored pdf documents on the file server, SSIS package copied and indexed files

  into db. Although the naming rules were written up in DD, users made absolute mess. So the error handling implemented in

  package allowed send them back what doesn't meet the naming convention.

  Best regards

  Mike

  Thanks for the suggestion Mike.

  Now the problem got bigger. I need Month Number instead of month name

  EX : Jan15 = 115

  FEB15 = 215

  MAR = 315 like this i need.

  Any help is appreciated much.

  Thnx in advnc.

• michal.lisinski

  SSCertifiable

  Points: 6933

  More actions

  October 16, 2015 at 6:33 am

  #1833773

  Hi

  Forgive me, I'm very busy today and the first impulse that comes to mind:

  //string Month = ls[5];

  string m = ls[5];

  //month name to int

  int NoM = DateTime.Parse("1." + m + " 2015").Month;

  Be careful - not tested.

  Br.

  Mike