Importation of data and delivering it to relevant files. 1

[pitbull]

I receive a data file that includes HEADER/DATA/FOOTER etc.,
ie.

HEAD19072001BATCH4
01John Smith
0212 Street, Manchester
03100720011420Water Burst Paul Jackson
TRAILREC003

The first 2 characters of each DATA line are the type of data and as such require inserting into relevant file, ie. Contact, Address File, Call Details. The file(s) are fixed format for each type of DATA line etc., Anybody any ideas.

 

[MichaelRed]

'Looks Like' a simplistic case of select(ion). P;acing your sample in a text file( as shown in the results):

Public Function basParseRec(FilName As String)


    Dim MyFil As Integer
    Dim MyLine As String

    MyFil = FreeFile
    Open FilName For Input As MyFil

    While Not EOF(MyFil)
        Line Input #MyFil, MyLine
        Select Case UCase(left(MyLine, 2))
            Case Is = "HE"      'Header Line
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 4)
            Case Is = "01"      'Name
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 2)
            Case Is = "02"      'Address
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 2)
            Case Is = "03"      'Code / Problem
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 2)
            Case Is = "TR"      'Trailer Line
                'Process as ...
                Debug.Print right(MyLine, Len(MyLine) - 5)
        End Select
    Wend

End Function

Results:
? basParseRec("C:\My Documents\MyBatch.Txt&quot
19072001BATCH4
John Smith
12 Street, Manchester
100720011420Water Burst Paul Jackson
REC003


Without some detail on where to place what, this is about as far as I can go - it processes each line acording to te 'ID' at the start of the line.

It is left as an exercise .... to complete ...



MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[pitbull]

Thanks for the information that is the start of what I need. Cheers Mate.

 

[MichaelRed]

I do not know wht it doesn't work for you. I would generally NOT re-key code from these forums, as you can easily copy and paste from the message window into a VB / VBA module.

If you are using NON-U.S. date / Language settings, these MAY have an impact - I do not use the non-U.S. settings, so I do not necessarily recall the difference - but then this only applies IF you are using the settings.

The following is a table generated by a test program to demonstrate that a few of the routines offered produce the same results set. You will notice that the MAJORITY of the results are fo five (5) weeks, so you evaluation may want to select a month where there are other than the norm (five) weeks.

Mnth WeeksInMonth nRptPer nWeeks basWksInMnth WeeksInMnth_L Flag Date DaysInMnth Date1st DateLast
5 5 5 5 5 No 1/1/01 31 Monday, January 01, 2001 Wednesday, January 31, 2001
5 5 5 5 5 No 2/1/01 28 Thursday, February 01, 2001 Wednesday, February 28, 2001
5 5 5 5 5 No 3/1/01 31 Thursday, March 01, 2001 Saturday, March 31, 2001
5 5 5 5 5 No 4/1/01 30 Sunday, April 01, 2001 Monday, April 30, 2001
5 5 5 5 5 No 5/1/01 31 Tuesday, May 01, 2001 Thursday, May 31, 2001
5 5 5 5 5 No 6/1/01 30 Friday, June 01, 2001 Saturday, June 30, 2001
5 5 5 5 5 No 7/1/01 31 Sunday, July 01, 2001 Tuesday, July 31, 2001
5 5 5 5 5 No 8/1/01 31 Wednesday, August 01, 2001 Friday, August 31, 2001
6 6 6 6 6 No 9/1/01 30 Saturday, September 01, 2001 Sunday, September 30, 2001
5 5 5 5 5 No 10/1/01 31 Monday, October 01, 2001 Wednesday, October 31, 2001
5 5 5 5 5 No 11/1/01 30 Thursday, November 01, 2001 Friday, November 30, 2001
6 6 6 6 6 No 12/1/01 31 Saturday, December 01, 2001 Monday, December 31, 2001
5 5 5 5 5 No 1/1/02 31 Tuesday, January 01, 2002 Thursday, January 31, 2002
5 5 5 5 5 No 2/1/02 28 Friday, February 01, 2002 Thursday, February 28, 2002
6 6 6 6 6 No 3/1/02 31 Friday, March 01, 2002 Sunday, March 31, 2002
5 5 5 5 5 No 4/1/02 30 Monday, April 01, 2002 Tuesday, April 30, 2002
5 5 5 5 5 No 5/1/02 31 Wednesday, May 01, 2002 Friday, May 31, 2002
6 6 6 6 6 No 6/1/02 30 Saturday, June 01, 2002 Sunday, June 30, 2002
5 5 5 5 5 No 7/1/02 31 Monday, July 01, 2002 Wednesday, July 31, 2002
5 5 5 5 5 No 8/1/02 31 Thursday, August 01, 2002 Saturday, August 31, 2002
5 5 5 5 5 No 9/1/02 30 Sunday, September 01, 2002 Monday, September 30, 2002
5 5 5 5 5 No 10/1/02 31 Tuesday, October 01, 2002 Thursday, October 31, 2002
5 5 5 5 5 No 11/1/02 30 Friday, November 01, 2002 Saturday, November 30, 2002
5 5 5 5 5 No 12/1/02 31 Sunday, December 01, 2002 Tuesday, December 31, 2002
5 5 5 5 5 No 1/1/03 31 Wednesday, January 01, 2003 Friday, January 31, 2003
5 5 5 5 5 No 2/1/03 28 Saturday, February 01, 2003 Friday, February 28, 2003
6 6 6 6 6 No 3/1/03 31 Saturday, March 01, 2003 Monday, March 31, 2003
5 5 5 5 5 No 4/1/03 30 Tuesday, April 01, 2003 Wednesday, April 30, 2003
5 5 5 5 5 No 5/1/03 31 Thursday, May 01, 2003 Saturday, May 31, 2003
5 5 5 5 5 No 6/1/03 30 Sunday, June 01, 2003 Monday, June 30, 2003
5 5 5 5 5 No 7/1/03 31 Tuesday, July 01, 2003 Thursday, July 31, 2003
6 6 6 6 6 No 8/1/03 31 Friday, August 01, 2003 Sunday, August 31, 2003
5 5 5 5 5 No 9/1/03 30 Monday, September 01, 2003 Tuesday, September 30, 2003
5 5 5 5 5 No 10/1/03 31 Wednesday, October 01, 2003 Friday, October 31, 2003
6 6 6 6 6 No 11/1/03 30 Saturday, November 01, 2003 Sunday, November 30, 2003
5 5 5 5 5 No 12/1/03 31 Monday, December 01, 2003 Wednesday, December 31, 2003
5 5 5 5 5 No 1/1/04 31 Thursday, January 01, 2004 Saturday, January 31, 2004
5 5 5 5 5 No 2/1/04 29 Sunday, February 01, 2004 Sunday, February 29, 2004
5 5 5 5 5 No 3/1/04 31 Monday, March 01, 2004 Wednesday, March 31, 2004
5 5 5 5 5 No 4/1/04 30 Thursday, April 01, 2004 Friday, April 30, 2004
6 6 6 6 6 No 5/1/04 31 Saturday, May 01, 2004 Monday, May 31, 2004
5 5 5 5 5 No 6/1/04 30 Tuesday, June 01, 2004 Wednesday, June 30, 2004
5 5 5 5 5 No 7/1/04 31 Thursday, July 01, 2004 Saturday, July 31, 2004
5 5 5 5 5 No 8/1/04 31 Sunday, August 01, 2004 Tuesday, August 31, 2004
5 5 5 5 5 No 9/1/04 30 Wednesday, September 01, 2004 Thursday, September 30, 2004
6 6 6 6 6 No 10/1/04 31 Friday, October 01, 2004 Sunday, October 31, 2004
5 5 5 5 5 No 11/1/04 30 Monday, November 01, 2004 Tuesday, November 30, 2004
5 5 5 5 5 No 12/1/04 31 Wednesday, December 01, 2004 Friday, December 31, 2004
6 6 6 6 6 No 1/1/05 31 Saturday, January 01, 2005 Monday, January 31, 2005
5 5 5 5 5 No 2/1/05 28 Tuesday, February 01, 2005 Monday, February 28, 2005
5 5 5 5 5 No 3/1/05 31 Tuesday, March 01, 2005 Thursday, March 31, 2005
5 5 5 5 5 No 4/1/05 30 Friday, April 01, 2005 Saturday, April 30, 2005
5 5 5 5 5 No 5/1/05 31 Sunday, May 01, 2005 Tuesday, May 31, 2005
5 5 5 5 5 No 6/1/05 30 Wednesday, June 01, 2005 Thursday, June 30, 2005
6 6 6 6 6 No 7/1/05 31 Friday, July 01, 2005 Sunday, July 31, 2005
5 5 5 5 5 No 8/1/05 31 Monday, August 01, 2005 Wednesday, August 31, 2005
5 5 5 5 5 No 9/1/05 30 Thursday, September 01, 2005 Friday, September 30, 2005
6 6 6 6 6 No 10/1/05 31 Saturday, October 01, 2005 Monday, October 31, 2005
5 5 5 5 5 No 11/1/05 30 Tuesday, November 01, 2005 Wednesday, November 30, 2005
5 5 5 5 5 No 12/1/05 31 Thursday, December 01, 2005 Saturday, December 31, 2005
5 5 5 5 5 No 1/1/06 31 Sunday, January 01, 2006 Tuesday, January 31, 2006
5 5 5 5 5 No 2/1/06 28 Wednesday, February 01, 2006 Tuesday, February 28, 2006
5 5 5 5 5 No 3/1/06 31 Wednesday, March 01, 2006 Friday, March 31, 2006
6 6 6 6 6 No 4/1/06 30 Saturday, April 01, 2006 Sunday, April 30, 2006
5 5 5 5 5 No 5/1/06 31 Monday, May 01, 2006 Wednesday, May 31, 2006
5 5 5 5 5 No 6/1/06 30 Thursday, June 01, 2006 Friday, June 30, 2006
6 6 6 6 6 No 7/1/06 31 Saturday, July 01, 2006 Monday, July 31, 2006
5 5 5 5 5 No 8/1/06 31 Tuesday, August 01, 2006 Thursday, August 31, 2006
5 5 5 5 5 No 9/1/06 30 Friday, September 01, 2006 Saturday, September 30, 2006
5 5 5 5 5 No 10/1/06 31 Sunday, October 01, 2006 Tuesday, October 31, 2006
5 5 5 5 5 No 11/1/06 30 Wednesday, November 01, 2006 Thursday, November 30, 2006
6 6 6 6 6 No 12/1/06 31 Friday, December 01, 2006 Sunday, December 31, 2006
5 5 5 5 5 No 1/1/07 31 Monday, January 01, 2007 Wednesday, January 31, 2007
5 5 5 5 5 No 2/1/07 28 Thursday, February 01, 2007 Wednesday, February 28, 2007
5 5 5 5 5 No 3/1/07 31 Thursday, March 01, 2007 Saturday, March 31, 2007
5 5 5 5 5 No 4/1/07 30 Sunday, April 01, 2007 Monday, April 30, 2007
5 5 5 5 5 No 5/1/07 31 Tuesday, May 01, 2007 Thursday, May 31, 2007
5 5 5 5 5 No 6/1/07 30 Friday, June 01, 2007 Saturday, June 30, 2007
5 5 5 5 5 No 7/1/07 31 Sunday, July 01, 2007 Tuesday, July 31, 2007
5 5 5 5 5 No 8/1/07 31 Wednesday, August 01, 2007 Friday, August 31, 2007
6 6 6 6 6 No 9/1/07 30 Saturday, September 01, 2007 Sunday, September 30, 2007
5 5 5 5 5 No 10/1/07 31 Monday, October 01, 2007 Wednesday, October 31, 2007
5 5 5 5 5 No 11/1/07 30 Thursday, November 01, 2007 Friday, November 30, 2007
6 6 6 6 6 No 12/1/07 31 Saturday, December 01, 2007 Monday, December 31, 2007
5 5 5 5 5 No 1/1/08 31 Tuesday, January 01, 2008 Thursday, January 31, 2008
5 5 5 5 5 No 2/1/08 29 Friday, February 01, 2008 Friday, February 29, 2008
6 6 6 6 6 No 3/1/08 31 Saturday, March 01, 2008 Monday, March 31, 2008
5 5 5 5 5 No 4/1/08 30 Tuesday, April 01, 2008 Wednesday, April 30, 2008
5 5 5 5 5 No 5/1/08 31 Thursday, May 01, 2008 Saturday, May 31, 2008
5 5 5 5 5 No 6/1/08 30 Sunday, June 01, 2008 Monday, June 30, 2008
5 5 5 5 5 No 7/1/08 31 Tuesday, July 01, 2008 Thursday, July 31, 2008
6 6 6 6 6 No 8/1/08 31 Friday, August 01, 2008 Sunday, August 31, 2008
5 5 5 5 5 No 9/1/08 30 Monday, September 01, 2008 Tuesday, September 30, 2008
5 5 5 5 5 No 10/1/08 31 Wednesday, October 01, 2008 Friday, October 31, 2008
6 6 6 6 6 No 11/1/08 30 Saturday, November 01, 2008 Sunday, November 30, 2008
5 5 5 5 5 No 12/1/08 31 Monday, December 01, 2008 Wednesday, December 31, 2008
5 5 5 5 5 No 1/1/09 31 Thursday, January 01, 2009 Saturday, January 31, 2009
4 4 4 4 4 No 2/1/09 28 Sunday, February 01, 2009 Saturday, February 28, 2009
5 5 5 5 5 No 3/1/09 31 Sunday, March 01, 2009 Tuesday, March 31, 2009
5 5 5 5 5 No 4/1/09 30 Wednesday, April 01, 2009 Thursday, April 30, 2009
6 6 6 6 6 No 5/1/09 31 Friday, May 01, 2009 Sunday, May 31, 2009
5 5 5 5 5 No 6/1/09 30 Monday, June 01, 2009 Tuesday, June 30, 2009
5 5 5 5 5 No 7/1/09 31 Wednesday, July 01, 2009 Friday, July 31, 2009
6 6 6 6 6 No 8/1/09 31 Saturday, August 01, 2009 Monday, August 31, 2009
5 5 5 5 5 No 9/1/09 30 Tuesday, September 01, 2009 Wednesday, September 30, 2009
5 5 5 5 5 No 10/1/09 31 Thursday, October 01, 2009 Saturday, October 31, 2009
5 5 5 5 5 No 11/1/09 30 Sunday, November 01, 2009 Monday, November 30, 2009
5 5 5 5 5 No 12/1/09 31 Tuesday, December 01, 2009 Thursday, December 31, 2009

You will need to copy and paste the table in to a text processor ans set the printer orientation and margins to view this is a 'human' readable format.

The only other clues I can offer are:

[tab] Place a breakpoint on the last line of the procedure (End Function). Using the immediate window, call the function with a date for a month with other than 5 "weeks". When the function STOPS, the "End Function" lline will be highlighted in YELLOW. At that point, retrieve the values of each variable (including the input date). Record them and post the results. I will review the function with your info and attempt to tell you what is awry.
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over