Solved

Parse text file

Posted on 1998-09-08
4
128 Views
Last Modified: 2012-05-07
I have huge text file and I want to export it to database
I have text(row) delimiters ///
I probably want 3 fields  1.identity-number 2.name-first line of text following ///  3.actual data between ///...///
Suppose I open file and save it into variable(variant), it is huge
Can I parse variant
using InStr Left and right functions
What is your best solution to this
Access and MSSQL data transformation services cannot understand
/// delimiters.
0
Comment
Question by:mativare
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Author Comment

by:mativare
ID: 1433764
Edited text of question
0
 
LVL 14

Accepted Solution

by:
waty earned 50 total points
ID: 1433765
You can use the following function :

Public Function GetTokens(sTxt As String, sToken As String) As Variant
   ' *** Returns an array to tokenized values
   ' ***  Ex:  GetTokens("Field1///Field2///Field3///", "///") = ({ "Field1", "Field2", "Field3" })

    Dim iTokenLen As Integer
    Dim iTokenCnt As Integer
    Dim lOffset As Long
    Dim lPrevOffset As Long
    Dim aTokens() As String

    iTokenLen = Len(sToken)
    lOffset = InStr(sTxt, sToken)
   
    Do While lOffset > 0
        ReDim Preserve aTokens(iTokenCnt)
        If lOffset - lPrevOffset > 1 Then
            aTokens(iTokenCnt) = Mid$(sTxt, lPrevOffset + 1, lOffset - 1 - lPrevOffset)
        Else
            aTokens(iTokenCnt) = ""
        End If
       
        lPrevOffset = lOffset
        lOffset = InStr(lOffset + iTokenLen, sTxt, sToken)
        iTokenCnt = iTokenCnt + 1
    Loop
   
    ReDim Preserve aTokens(iTokenCnt)
    aTokens(iTokenCnt) = Mid$(sTxt, lPrevOffset + 1)
    GetTokens = CVar(aTokens)

End Function

0
 
LVL 1

Author Comment

by:mativare
ID: 1433766
Thanks waty
This piece of code is platinum
0
 
LVL 13

Expert Comment

by:Mirkwood
ID: 1433767
Bought This Question.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month6 days, 11 hours left to enroll

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question