Re: help please! reading text re post

Hi
I have done this literally hundreds of time for customers when converting their existing datafiles for use in a database for my applications.

If the file you are trying to extract the info from is a true text file, you should be able to open it with a program like notepad, wordpad, or any text editor, preferably one which displays column numbers. You should see that the fields are shown in fixed positions. by using an editor with column indicator it is simply a case of position the curser over the starting column and ending column then using code such as below it is easy to extract the fields.

The code below reads one text record at a time. Because there are 4 text records to make up one Customer record, the program determines which fields are shown in a given text record by monitoring what is shown in columns 63 thru 70 with the statement: Txt = Trim(Mid(Buffer, 63, 7))
The program then gets the fields based on the Case value of Txt.


Dim Buffer As String
Dim Txt As String
Dim ACnt As Integer
Dim ECnt As Integer

Dim Name As String
Dim Address1 As String
Dim Address2 As String
Dim City As String
Dim PostalCd As String
Dim AreaCd As String
Dim Phone As String
Dim Contact As String
Dim Costar As String

Set APMast = Db.OpenRecordset("APMaster")
Set OXDesc = Db.OpenRecordset("OXDescription")
OXDesc.Index = "KeyType"

Open Label1.Caption For Input As #1

Do Until EOF(1) = True
Line Input #1, Buffer
Txt = Trim(Mid(Buffer, 63, 7))
Select Case Txt
Case "NORMAL"
Name = Trim(Mid(Buffer, 8, 31))
Address1 = Trim(Mid(Buffer, 39, 24))
Costar = Trim(Mid(Buffer, 1, 6))
Case "Contact"
City = Trim(Mid(Buffer, 39, 24))
Contact = Trim(Mid(Buffer, 73, 24))
Case "Phone"
PostalCd = Trim(Mid(Buffer, 39, 24))
Phone = Trim(Mid(Buffer, 73, 8))
Case ""
If Trim(Mid(Buffer, 39, 24)) <> "" Then
Address2 = City
City = PostalCd
PostalCd = Trim(Mid(Buffer, 39, 24))
Else
If Name <> "" Then
APMast.AddNew
APMast!Costar = Costar
APMast!Name = Capitalize(Name, 0, 0)
APMast!Address1 = Capitalize(Address1, 0, 0)
APMast!Address2 = Capitalize(Address2, 0, 0)
APMast!City = Capitalize(City, 0, 0)
If APMast!City <> "" Then
OXDesc.Seek "=", 99, APMast!City
If OXDesc.NoMatch = True Then
OXDesc.AddNew
OXDesc!Type = 99
OXDesc!Desc = APMast!City
OXDesc.Update
OXDesc.Bookmark = OXDesc.LastModified
End If
End If
APMast!PostalCd = PostalCd
If InStr(1, City, ", AB") > 0 Then
APMast!AreaCd = 780
Else
APMast!AreaCd = 0
End If
APMast!Phone = Phone
APMast!Contact = Capitalize(Contact, 0, 0)
APMast.Update
Name = ""
Address1 = ""
Address2 = ""
City = ""
PostalCd = ""
End If
End If
End Select
Loop

MsgBox "Conversion Completed"
End

End Sub

Hope this will shed a different solution to your problem.

Duke



jonathandrott@xxxxxxxxx wrote:
I'm trying to read specific information from a text file. I need to
get stuff like invoice date, invoice number and account number
(9/18/06, 586007, 1543 respectively) i was able to use a stream reader

to display the text in a textbox for viewing, but i want to pick these
items out. I don't even know where to start.

EW1 .FW0 Invoice Date 9/18/06
Invoice No. 586007


PO#
RTL#2650000023
-1
-0
Ship-To |Bill-To
|Acct#- Rte Slm#
TO | CHARGE| 1543 0 1
| |ent: ees pck: SP
| |Driver Collect:
| |
0.00
-1
-0
Qty SUM ------------Description----------------Stock-#--- Itm -Price--

---Total---


Dear Mr. Drott,

The procedure would be like this:


1. Declare some string variable (e.g. x As String, InvoiceDate As
String, ...)
2. Declare 3 integer variable (e.g. posi As Integer, t As Integer,
StartData As Integer)
3. Open the file
4. Read file into a string variable
x = sr.ReadToEnd()


5. Get the position of invoice date
posi = Instr(x.ToUpper,"INVOICE DATE")


6. Go to the actual start of the date.
If posi is greater than 0 the string was found.
Then add the length of the string (12 characters)
to the position to start right after it and search for the number.
If posi>0 Then
posi=posi+12
For t = posi To x.Length
If Strings.Mid(x,t,1)<>" " Then
StartData = t
Exit For
End If
Next
End If


7. Go to the end of the date
For t = StartData To x.Length
If Strings.Mid(x,t,1)=" " then
InvoiceString = Strings.Mid(x,StartData,t-StartData)
Exit For
End If
Next


Best Regards,


HKSHK


i'm sorry this is a first for me. (not quite understanding everything)
is the code below correct? how can i output the date to a lable to
test if it worked? lblTest.text = InvoiceDate?

'Try to read in the Invoice Date
Dim InvoiceDate, x As String
Dim posi, t, StartData As Integer
myReader = New IO.StreamReader(txtReadFile.Text)
x = myReader.ReadToEnd
posi = InStr(x.ToUpper, "INVOICE DATE")
If posi > 0 Then
posi = posi + 12
For t = posi To x.Length
If Strings.Mid(x, t, 1) <> "" Then
StartData = t
Exit For
End If
Next
End If
For t = StartData To x.Length
If Strings.Mid(x, t, 1) = "" Then
InvoiceDate = Strings.Mid(x, StartData, t - StartData)


Exit For End If Next

.

Relevant Pages

  • Re: serial numbering
    ... Add the macro and a button to a custom toolbar to call it, to the invoice ... Dim SettingsFile As String ...
    (microsoft.public.word.docmanagement)
  • Re: serial numbering
    ... Dim SettingsFile As String ... Dim iCount As String ... The next macro will allow you to reset the invoice number to the next number ...
    (microsoft.public.word.docmanagement)
  • Re: Get the exact case of a folder name
    ... Dim s As String = GetExactPathCase ... Private Function GetExactPathCase(ByVal Path As String) As String ... Dim ParentDirs() As System.IO.DirectoryInfo ... If Posi = 0 Then 'Huh? ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Invoice Number Problem
    ... "Sue" wrote: ... denotes the department that is issuing the Invoice. ... Dim invnum As String ...
    (microsoft.public.excel.programming)
  • Re: I need a routine to generate a unique number for a key
    ... I use this routine to generate a variety of numbers used for orders, ... Public Function GetNextIDAs String ... '5 = Creditors Invoice ... Dim mySQL As String ...
    (microsoft.public.access.formscoding)