Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
parse text from a file

Answered parse text from a file

  • Thursday, January 24, 2013 5:55 AM
     
     
    Sign In to Vote
    0

    I need to parse text from this file.  The text i need from it is customer info, sales info, product info , prices..etc...

    what is the best way to do this so i can load it in to a db - (ssis, script task)...i dont know how to approach this.

                                                                                                                                    
                                                       ****** BEER COMPANY ******                                                      
     RUN DATE  12/27/12  ( 5:26 PM)                     MONTHLY SALES BY CUSTOMER/ITEM 2012/01                                 PAGE    1
    ************************************************************************************************************************************
                 ITEM                   SALES         SALES         COST        GROSS      G/P      PREVIOUS      PREVIOUS    PREV     
                 NAME                  AMOUNT       QUANTITY       AMOUNT       PROFIT     PCT     YEAR SALES     YEAR G/P    PCT      
    ------------------------------------------------------------------------------------------------------------------------------------
    Salesman: 03     Customer: BOY'S CLUB                       60287                                                              
      300020  Acme Beer (S)                                                                                                       
                                        71.85          3.00         59.76        12.09  16.83 %                                        
      300022   Acme XXX(S)                                                                                                         
                                        47.90          2.00         39.84         8.06  16.83 %                                        
      390001    Empty Bottles-Pick up *Small* Btl                                                                                      

         ** CUSTOMER TOTAL **           119.75          5.00         99.60        20.15        %         0.00         0.00        %    
    Salesman: 03     Customer: MOM'S RESTAURANT                      60429                                                              
      300024  Acme Beer (L)                                                                                                       
                                       135.00          5.00        115.10        19.90  14.74 %                                        
      390001    Empty Bottles-Pick up *Small* Btl                                                                                      
                                                                                                                                       
      390002    Empty Bottles-Pick up *Large* Btl                                                                                      

         ** CUSTOMER TOTAL **           135.00          5.00        115.10        19.90        %         0.00         0.00        %    
    Salesman: 03     Customer: BUG'S STOP      60655                                                              
      300020  Acme Beer (S)                                                                                                       
                                       119.75          5.00         99.60        20.15  16.83 %                                        
      300022   Acme XXX(S)                                                                                                         
                                       119.75          5.00         99.60        20.15  16.83 %       119.75        25.20  21.04 %     
      390001    Empty Bottles-Pick up *Small* Btl                                                                                      
                                                                                                                                       
      390002    Empty Bottles-Pick up *Large* Btl                                                                                      

         ** CUSTOMER TOTAL **           239.50         10.00        199.20        40.30        %       119.75        25.20        %    
    Salesman: 03     Customer: YOSIMITE SAM'S LIQUOR STOP                     60663                                                              
      300022   Acme XXX(S)                                                                                                         
                                       119.75          5.00         99.60        20.15  16.83 %                                        
      390001    Empty Bottles-Pick up *Small* Btl                                                                                      

         ** CUSTOMER TOTAL **           119.75          5.00         99.60        20.15        %         0.00         0.00        %    
    Salesman: 03     Customer: WILEY COYOTE'S RESTAURANT(3)      60860                                                              
      300022   Acme XXX(S)                                                                                                         
                                       119.75          5.00         99.60        20.15  16.83 %       119.75        25.20  21.04 %     
      390001    Empty Bottles-Pick up *Small* Btl                                                                                      
                                         2.40-         2.00-          .58-        1.82- 75.83 %         6.00-        4.55- 75.83 %     

         ** CUSTOMER TOTAL **           117.35          3.00         99.02        18.33        %       113.75        20.65        %    
    Salesman: 03     Customer: BUBBA'S  REST.               60930                                                              
      300020  Acme Beer (S)                                                                                                       
                                        23.95          1.00         19.92         4.03  16.83 %        47.90         7.14  14.91 %     
      300022   Acme XXX(S)                                                                                                         
                                        23.95          1.00         19.92         4.03  16.83 %        23.95         5.04  21.04 %     
      300024  Acme Beer (L)                                                                                                       
                                        81.00          3.00         69.06        11.94  14.74 %        54.00         7.96  14.74 %     

    KDW

     

All Replies

  • Thursday, January 24, 2013 6:42 AM
     
     Answered Has Code
    Sign In to Vote
    0

    You can write you own "Script Component" as Source and parse your file to structure data:

    http://msdn.microsoft.com/en-us/library/ms136060.aspx

    using System.IO;
public class ScriptMain:
    UserComponent

{
    private StreamReader textReader;
    private string exportedAddressFile;

    public override void AcquireConnections(object Transaction)
    {

        IDTSConnectionManager100 connMgr = this.Connections.MyFlatFileSrcConnectionManager;
        exportedAddressFile = (string)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {
        base.PreExecute();
        textReader = new StreamReader(exportedAddressFile);
    }

    public override void CreateNewOutputRows()
    {

        string nextLine;
        string[] columns;

        char[] delimiters;
        delimiters = ",".ToCharArray();

        nextLine = textReader.ReadLine();
        while (nextLine != null)
        {
            columns = nextLine.Split(delimiters);
            {
                MyAddressOutputBuffer.AddRow();
                MyAddressOutputBuffer.AddressID = columns[0];
                MyAddressOutputBuffer.City = columns[3];
            }
            nextLine = textReader.ReadLine();
        }

    }

    public override void PostExecute()
    {

        base.PostExecute();
        textReader.Close();

    }

}

    Regards, Nighting Liu

     
  • Thursday, January 24, 2013 7:35 AM
     
     
    Sign In to Vote
    0
    I am not familiar with the scripting languages enough to perform the task

    KDW

     
  • Monday, January 28, 2013 8:42 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Hi Siera_gld,

    We can write the script by using either Microsoft Visual Basic or Microsoft Visual C#, depending on the script language selected for the ScriptLanguage property. If you are not familiar with C#, we can convert the VB .NET code posted by Nighting to C# using the following tool:
    Convert C# to VB.NET

    The result is as follows and I suggest that you give it a try:

    Imports System.IO
Public Class ScriptMain
 Inherits UserComponent

 Private textReader As StreamReader
 Private exportedAddressFile As String

 Public Overrides Sub AcquireConnections(Transaction As Object)

  Dim connMgr As IDTSConnectionManager100 = Me.Connections.MyFlatFileSrcConnectionManager
  exportedAddressFile = DirectCast(connMgr.AcquireConnection(Nothing), String)

 End Sub

 Public Overrides Sub PreExecute()
  MyBase.PreExecute()
  textReader = New StreamReader(exportedAddressFile)
 End Sub

 Public Overrides Sub CreateNewOutputRows()

  Dim nextLine As String
  Dim columns As String()

  Dim delimiters As Char()
  delimiters = ",".ToCharArray()

  nextLine = textReader.ReadLine()
  While nextLine IsNot Nothing
   columns = nextLine.Split(delimiters)
   If True Then
    MyAddressOutputBuffer.AddRow()
    MyAddressOutputBuffer.AddressID = columns(0)
    MyAddressOutputBuffer.City = columns(3)
   End If
   nextLine = textReader.ReadLine()
  End While

 End Sub

 Public Overrides Sub PostExecute()

  MyBase.PostExecute()
  textReader.Close()

 End Sub

End Class

    Hope this helps.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here

    Mike Yin
    TechNet Community Support