Import Fixed Width Text File, with a twist 1

[bud31047]

Hi All,
I need to import a fixed width text file (actually a report run from a seperate program and saved as a text file), which is not a problem, but I also need to add a couple of fields and populate them with information from the report header. Here is a sample of the what the text file looks like;

Site: PRRY Production Tracking Page 1 of 32

Date: Mon Nov 26, 2007 13:28:26
From: Sun Nov 25, 2007 00:00:00 To: Sun Nov 25, 2007 23:59:59
Requestor:JAMES
Shift: 3 Finished and Non-Finished Goods

PKG Creation Creation
Product Description Location Date Time
----------------------------------------------------------
101567 2.99 Reg Flavor BP-104F 11/25 04:25
245689 Vend Barbeque PAL~02 11/25 04:26
987123 .99 Onion Rings BK~101 11/25 05:01

I only need to import the rows that meet my criteria (i.e. contains a product code), and at the same time I need to capture the date, Nov 25, 2007 from the row;
From: Sun Nov 25, 2007 00:00:00 To: Sun Nov 25, 2007 23:59:59
and add it to each record, as well as the shift, which would be shift 3 in this example.

Let me know if I need to clarify my problem some more.
Any suggestions?
Thanks in advance,
James

 

[AHJ1]

Hi,
See if this helps:

Sub OpenFile(strFileName)
Dim strA As String
Dim strB As String
Dim strC As String
Open strFileName For Input As #1
'You might want to load these into an array for processing. This is a simple example
    Input #1, strA
    Input #1, strB
    Input #1, strC
    Debug.Print strA
    Debug.Print strB
    Debug.Print strC
    'Parse the string and use an appropriate IF statement
    
Close #1


End Sub

 

[bud31047]

Hi AHJ1,
Thanks for the reply. I should have mentioned that I am VBA challenged, but I really need to learn. Now would be as good a time as any.

Would it be possible for you, or any other expert, to write a module that would import my sample data into a Table? That way I can walk through the process and learn how it works.

Thanks again,
James

 

[CautionMP]

bud31047,
Here is an expanded version of what AHJ1 posted. Basically this opens the input file and cycles through it creating a new output file that has the From date and Shift added to each file.

Once you run the routine go through the Link Table wizard and see if the new file is in a format you can work with. If it is you could use a simple macro to call the routine...

Option Explicit
Option Compare Database

'Note the asterisk to support the Like Comparison
Private Const mstrcHeaderFrom As String = "From:*"
Private Const mstrcHeaderShift As String = "Shift:*"

Public Sub Test_ConverFile()
  Const strcFileIn As String = "C:\Report.txt"
  Const strcFileOut As String = "C:\Out.txt"
  ConvertFile strcFileIn, strcFileOut
End Sub

Public Sub ConvertFile(InputFilename As String, OutputFilename As String)
  Dim intFileIn As Integer, intFileOut As Integer
  Dim intHeaderFromOffset As Integer, intHeaderFromLength As Integer
  Dim intHeaderShiftOffset As Integer, intHeaderShiftLength As Integer
  Dim strHeaderFrom As String, strHeaderShift As String
  Dim strBuffer As String
  
  'Gather some information about the file
  intHeaderFromOffset = Len(mstrcHeaderFrom) + Len("Sun ")
  intHeaderFromLength = Len("Nov 25, 2007")
  intHeaderShiftOffset = Len(mstrcHeaderShift)
  intHeaderShiftLength = 2
  
  'Open the input file
  intFileIn = FreeFile
  Open InputFilename For Input As #intFileIn
  
  'Open the output file
  intFileOut = FreeFile
  Open OutputFilename For Output As #intFileOut
  
  'Now loop through the file one line at a time
  Do
    Line Input #intFileIn, strBuffer
    'Test the current line to see how to react
    If strBuffer Like mstrcHeaderFrom Then
      'it's the 'From: * To: *' line so grab the date
      strHeaderFrom = Mid$(strBuffer, intHeaderFromOffset, intHeaderFromLength)
    ElseIf strBuffer Like mstrcHeaderShift Then
      'it's the 'Shift: *' line so grab the shift
      strHeaderShift = Mid$(strBuffer, intHeaderShiftOffset, intHeaderShiftLength)
    ElseIf Trim$(strBuffer) <> "" Then
      'all other lines, just make sure it's not blank
      'and Print the output
      Print #intFileOut, strHeaderFrom, strHeaderShift, strBuffer
    End If
  Loop Until EOF(intFileIn)
  
  Close #intFileIn
  Close #intFileOut
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[bud31047]

Thanks CMP. I will work on this today and post my results for the forum.

James

 

[AHJ1]

Hello,

I just opened the notification email and was about to reply when I noticed CMP's response. CMP did a great job. I'm awarding him a star.

Alan

 

[bud31047]

CMP,

It works like a charm. I gave you a star also.

Thanks,
James

 

[CautionMP]

bud31047,
Glad to hear it worked for you and welcome to Tek-Tips.

CMP

 

[bud31047]

I have one additional request, if someone can help.
Would it be possible to format the date as mm/dd/yyyy, instead of Nov 25, 2007, in the output file?

Thanks,
James

 

[AHJ1]

I've got to run, but something like this might work:
Add this declaration

Dim strDateInput as string
Dim datToShow as date

strDateInput = <Parse the string to show the date>
Convert the string representation of the date to a date variant, using the function DateValue

strDateInput = <Parse the string to show the date>
datToShow=DateValue(strDateInput)
datToShow = Format(varDate, "mm/dd/yyyy")

 

[CautionMP]

[tt]CDate("Nov 25, 2007") = 39411 11/25/2007[/tt]

[Code Date]...
Print #intFileOut, CDate(strHeaderFrom), strHeaderShift, strBuffer
...[/code]

[Code Text]...
Print #intFileOut, Format$(CDate(strHeaderFrom), "mm/dd/yyyy"), strHeaderShift, strBuffer
...[/code]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[bud31047]

You lost me CMP. But I used your code to figure out a solution. Here is what I did;

strHeaderFrom = Format$(CDate(Mid$(strBuffer, intHeaderFromOffset, intHeaderFromLength)), "mm/DD/YY")

Can you think of any potential issues with my solution?

Thanks,
James