How can I read a UTF-8 encoded file ina Access 2007

I am developing an Access 2007 "project" (.ADP) as the front-end to a SQL Server 2005 Express database.
Various tasks involve reading ".txt" files and importing their content into the database tables.
A new set of files are arriving in UTF-8 format which do not seem to have the usual "Line Feed / Carriage Return" characters for line breaks.

The content just appears as a single block of text when opened in Notepad and that seems to be the way they are seen by my VBA importing procedures (but the lines do appear separated OK when the files are opened with Wordpad).

In my VBA coding I use the following lines to open up the raw tex file :

intFileNum = FreeFile()
Open strDataFileName For Input As intFileNum

I then read through the file and process the content one line at a time with the following :

    lngRowCount = 0
    Do While Not EOF(intFileNum)
        Line Input #intFileNum, strFullInputString
        lngRowCount = lngRowCount + 1
       
        ' MsgBox "strDataFileName : " & strDataFileName & vbCrLf & _
               "Input Line No : " & lngRowCount & vbCrLf & _
               "Input String  : " & strFullInputString
    Loop

Can anyone suggest how I can handle the UTF-8 encoded file so that I get my usual line breaks?
Can I open the file in a different way or are there VBA routines to convert the files first?

Any help greatly appreciated. Thanks.
colinasadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Please post the file
colinasadAuthor Commented:
Thanks for the fast reply.
Sample file is attached.
Regards.
JoeCool-W-20150810-00007.txt
aikimarkCommented:
Your problem is that the intrinsic VB file I/O methods, such as Line Input, require a file to be CrLf delimited.  Your file is Lf delimited.

You will need to use FileSystemObject and instantiate a TextStream object from that.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

colinasadAuthor Commented:
Thanks again.
Yes I had seen that sort of suggestion on web-sites I had found before posting on EE.
I guess I was hoping for an "easier" solution!
I'll give that a go and report back.
Thanks.
aikimarkCommented:
Start with this:
    Dim oFS, oTS, strData
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile("C:\users\aikimark\downloads\test.txt", ForReading, True, TristateFalse)

    strData = oTS.Readline

    oTS.Close

Open in new window

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I use VBA code to convert the file format  from LF to CRLF line terminators so it can be read easily with VBA.

Also, be sure to backup your source file before running it this

Try this:
Public Sub ConvertLFtoCRLF(strFile As String)
    Dim InFileID As Integer
    Dim OutFileID As Integer
    Dim strChar As String * 1
    InFileID = FreeFile
    Open strFile For Binary As #InFileID
    OutFileID = FreeFile
    Open strFile & ".tmp" For Binary As #OutFileID
    Do Until EOF(InFileID)
        Get #InFileID, , strChar
        If strChar = vbLf Then
            Put #OutFileID, , vbCrLf
        Else
            Put #OutFileID, , strChar
        End If
    Loop
    Close #OutFileID
    Close #InFileID
    Kill strFile
    Name strFile & ".tmp" As strFile
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colinasadAuthor Commented:
Thank you.
This is exactly the sort of solution I was looking for. I have been able to make some changes to the way the file names are handled to suit my requirements exactly.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
You're welcome.

Glad you were able to adapt the code to your needs.

Good luck with your project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.