importing files - odd format

[tjuiowa]

Hi all -

I have a file i am trying to import into an access database. The program I use (Blaise) exports it into a format like this (actual data is separated by quotation marks):

------------------------------------------------------------------------------
XX993395
DoctorsSeenBeforeMDDx.FamilyPractitioner
FOIC is a doctor so FOIC did a blood test on him.
------------------------------------------------------------------------------
XX993394
Therapies.PT.TherapyFrequency
He also gets range of motion on a daily basis with an associate at school.

------------------------------------------------------------------------------

These are comments that are separated by a line of dashed on the top and bottom. I want to extract the information between the dashes into 3 fields. the first would be an id (XX993395), then the question name (DoctorsSeenBeforeMDDx.FamilyPractitioner) and finally the comment for the question (FOIC is a doctor so FOIC did a blood test on him). All three fields will always be on one line. i do not want to import the dashes.

Thanks in advance for any help...i am sure there is an easy way, but i can't seem to find it!

Thanks
TJ

[MMcCarthy]

Hi TJ

There isn't an easy way to do this as it isn't a comma separated file or any other format that Access recognises. You will have to read this in line by line using VBA and validate each line.

To help you get started check out the following code.

Public Function readTextFile(ByVal fpath As String, ByVal filename As String) As String
Dim fhandle As Integer
Dim fline As String
 
    ' open the text file to be read
    fhandle1 = FreeFile()
    Open fpath & filename For Input Access Read Lock Write As #fhandle1
 
    Do While Not EOF(fhandle1) ' loop until you reach the end of the file.
 
        Line Input #fhandle1, fline
        fline = Trim(fline)  ' cut off spaces
 
        ' you will have to add code here to check for the appropriate value and
        ' I would suggest you create a comma separated string for each record and
        ' then add code to add it to the table once the record is complete
 
    Loop
 
    Close #fhandle
 
    readTextFile = dateValue
 
End Function
 

[tjuiowa]

Thanks. I am not sure how i create a comma separated string for each record.

I know i want it to say if line = "------" then skip to the next line and read in that line, follow that by a comma, then read the next line, comma, next line then start a new record and continue as stated, but i am not sure of the correct context i would use for VB.

thanks
TJ

Hi TJ

There isn't an easy way to do this as it isn't a comma separated file or any other format that Access recognises. You will have to read this in line by line using VBA and validate each line.

To help you get started check out the following code.

Public Function readTextFile(ByVal fpath As String, ByVal filename As String) As String
Dim fhandle As Integer
Dim fline As String
 
    ' open the text file to be read
    fhandle1 = FreeFile()
    Open fpath & filename For Input Access Read Lock Write As #fhandle1
 
    Do While Not EOF(fhandle1) ' loop until you reach the end of the file.
 
        Line Input #fhandle1, fline
        fline = Trim(fline)  ' cut off spaces
 
        ' you will have to add code here to check for the appropriate value and
        ' I would suggest you create a comma separated string for each record and
        ' then add code to add it to the table once the record is complete
 
    Loop
 
    Close #fhandle
 
    readTextFile = dateValue
 
End Function
 

[MMcCarthy]

Thanks. I am not sure how i create a comma separated string for each record.

I know i want it to say if line = "------" then skip to the next line and read in that line, follow that by a comma, then read the next line, comma, next line then start a new record and continue as stated, but i am not sure of the correct context i would use for VB.

thanks
TJ

Create a string variable to hold the comma separated data.
fline is a string so you need an IF statement something like

Dim strCSV As String
 
If fline LIKE "----*"  OR fline = "" Then
 ' do nothing
Else
    strCSV = strCSV & "," & fline
End If

[missinglinq]

You might want to take a look at this white paper. It deals specifically with the Blaise API vis a vis intereacting with COM compatible languages such as Access/Excel VBA as well as VB6 and C/C++.

Using the Blaise API Objects Library

Linq ;0)>