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

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.
Who is Participating?
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.

Please post the file
colinasadAuthor Commented:
Thanks for the fast reply.
Sample file is attached.
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
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


Open in new window

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
            Put #OutFileID, , strChar
        End If
    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 MVPCommented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.