Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 28th, 2006, 09:40 PM
Registered User
 
Join Date: Jan 2006
Location: Bartlett, TN, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default parsing a text file to use in an Access 2003 app

I'm a newbie in regards to parsing text files. My application requires a multiline bank file, text form comma delimited. I have to obtain the account number denoted by "03" and the balance "015" .
The file contains a lot of information that I will never use, header and validation codes, etc. I need to parse this long text file and only separate the fields that I need to use. Any suggestions on how to pull out only those items. The fields are all numbers but the file is a text file, comma delimited. Any help will be appreciated. Thanx
Reply With Quote
  #2 (permalink)  
Old January 31st, 2006, 12:42 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If you can post some sample lines from the text file, including any opening lines that might have a different format, I can help you write some code that will pull the values you want. This is pretty easy. I use it to pull medical data into one of my databases, and associate the data with the patient by SSN. This sounds similar (associate balance to account number)

The important thing is to include the entire line as it will appear in the text file (not real data though). For example, one of my sample lines is:


"110905,1105,111223333, , , , , , ,20,SE,20,25,15,05,25,10,25,15,20,35,15,20,38949,0 805"

The first group is the date, the second, the time of day, the third is the SSN, there are a few blank fields, then the medical data, then info about the machine that captured the data. I pull the date, time, SSN, and medical data and put it in the appropriate fields in a table, and it is associated with the patient's SSN. So knowing the placement of the fields is important as you can see.

HTH


mmcdonal
Reply With Quote
  #3 (permalink)  
Old January 31st, 2006, 04:34 PM
Registered User
 
Join Date: Jan 2006
Location: Bartlett, TN, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

03,000001588866002,USD,010,152736,,,015,105542,,,0 40,105542,,,045,105542,,/
88,063,000,,,072,000,,,074,000,,,100,32623,4,,140, 32623,4,,400,79817,4,,450/
88,79817,4,/
Here is some of the sample code. The numbers following the field '03' is the account number and the numbers following the '015' is the dollar amount with 2 decimal points. There is no consistency as to the location of the fields. It depends on which back is sending them. the only consistency is the '03' is always followed by the account number and the 015 is always followed by the dollar amount.
Thanks very much for any suggestions or help you can give me. I was thinking about pattern matching but have no idea how to proceed.
tom

Reply With Quote
  #4 (permalink)  
Old January 31st, 2006, 04:39 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

I have used code in the past that breaks an entire line of data into individual pieces and loads each into an array. You could then loop through the array, and check for the various indicators. Let me know if you would like me to post the code. I deal with a lot of data that is randomnly sequenced, so I feel your pain!!

Mike

Mike
EchoVue.com
Reply With Quote
  #5 (permalink)  
Old January 31st, 2006, 04:54 PM
Registered User
 
Join Date: Jan 2006
Location: Bartlett, TN, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mike,
I would appreciate the code examples. I have been pulling my hair out on this one and at this point I can't afford to do that continually. I am already follicle challenged enough.
Thanks

Tom

Reply With Quote
  #6 (permalink)  
Old January 31st, 2006, 05:04 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Here you go...

    Dim arrToken(40) As String
    Dim strBuffer As String
    Dim i As Long
    Dim t As Long
    i = 1
    t = 1
    strBuffer = ""
    While i <= Len(strLine)
        If Mid(strLine, i, 1) <> "," Then
            strBuffer = strBuffer & Mid(strLine, i, 1)
            i = i + 1
        Else
            arrToken(t) = Replace(Trim(strBuffer), Chr(34), "")
            strBuffer = ""
            i = i + 1
            t = t + 1
        End If
    Wend
    arrToken(t) = strBuffer
    'there are t columns
    For i = 1 To t
        'determine what header it is
        Select Case UCase(arrToken(i))
            Case "ACCOUNT"
                colAccount = i
            etc....

The object of this was to figure out which columns my headings were in, and then I can reference the right column when I need that data type.

In your case, I would loop through and check for the 03 and 05, and then save the following column of data as the Account or Amount respesctively.

Hope that helps - Goodness knows it cost me enough hair!!

Mike

Mike
EchoVue.com
Reply With Quote
  #7 (permalink)  
Old February 1st, 2006, 08:33 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is what I came up with:

'-----Code Starts-----
Dim strAccount As String
Dim curBalance As Double
'etc.

Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open "DSN=YourDSN;"
    objRS.Open "SELECT * FROM tblYourTable", objConn, 3, 3
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\data.txt") Then
    Set objStream = fso.OpenTextFile("C:\data.txt", 1, False, 0)
End If
Do While Not objStream.AtEndOfStream
    strLine = objStream.ReadLine
    YourArray = Split(strLine, ",")
        i = 0
        Do Until i = UBound(YourArray)
            If YourArray(i) = "03" Then
                n = i + 1
                strAccount = YourArray(n)
            End If
            If YourArray(i) = "015" Then
                n = i + 1
                curBalance = YourArray(n)
            End If
            i = i + 1
        Loop

     If IsNull(strAccount) Or IsNull(curBalance) Then
     'Error handling here...
     Else
        objRS.AddNew
        objRS("Account") = strAccount
        objRS("Balance") = curBalance
        objRS.Update
        objRS.Close
        objConn.Close
     End If
Loop

Set objFile = fso.CreateTextFile("C:\Data.txt")
MsgBox i & " File(s) Transferred"
'-----Code Ends-----

The last fso.CreateTextFile overwrites the data file so that duplicate information can't be entered. Anyway, I tried this on your string and it works.

HTH


mmcdonal
Reply With Quote
  #8 (permalink)  
Old February 1st, 2006, 08:35 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, the two lines:

objRS.Close
objConn.Close

should come AFTER the last "Loop"

HTH


mmcdonal
Reply With Quote
  #9 (permalink)  
Old February 1st, 2006, 08:48 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, this doesn't address the decimals in the curBalance variable.

Perhaps:

Dim strDec1 As Integer
Dim strDec2 As Integer


strDec1 = Left(curBalance, (Len(curBalance - 2))
strDec2 = Right(curBalance, 2)
curBalance = strDec1 & "." & strDec2

   I am not sure about typing violations here, so you might have to play around with it to get the decimal inserted. Look up regular expressions on MSDN for guidance.

HTH

:D



mmcdonal
Reply With Quote
  #10 (permalink)  
Old February 1st, 2006, 09:11 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, left out a closing paren:

strDec1 = Left(curBalance, (Len(curBalance - 2)))

mmcdonal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rich Text Box Controls In Access 2003 sibrows Access 10 August 28th, 2007 03:46 AM
A parsed text file to an Access table? josmith2 ADO.NET 0 May 10th, 2007 01:52 PM
A parsed text file to an Access table? josmith2 VB Databases Basics 0 May 8th, 2007 07:13 AM
parsing a changing text file on the fly Kannkor VB How-To 0 November 14th, 2005 01:47 AM
text file to access treasacrowe Classic ASP Databases 13 October 28th, 2004 11:02 PM



All times are GMT -4. The time now is 08:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.