Read Data From A Text File Into DB

J

JohnLee

Registered User.
Local time
Today, 03:35
Joined
Mar 8, 2007
Messages
692
Good day,

I hoping someone can help me out, I have some code that enables me to read a fixed width text files data into my database, which works fine, however I also have a text file which is not fixed width or of csv type which I want to read into my database, but I’m stuck on how to amend my code to get what I want and I would appreciate it if someone could find the time to help me with that.

Below is the code that I currently have that reads the fixed width text files.
Code: 
[COLOR=blue][FONT=Times New Roman]Option Compare Database[/FONT][/COLOR]
[FONT=Times New Roman][COLOR=blue]Public[/COLOR] FileName [COLOR=blue]As String[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Function[/COLOR] eFlowProcess3()[/FONT]
 
[COLOR=green][FONT=Times New Roman]'=============================================[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'References:[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Visual Basic For Applications[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Access 9.0 Object Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft DAO 3.6 Object Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Excell 11.0 Object Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Outlook 14.0 Object Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Word 14.0 Object Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Scripting Runtime[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'OLE Automation[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft ActiveX Date Objects 2.1 Library[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'Microsoft Outlook View Control[/FONT][/COLOR]
[COLOR=green][FONT=Times New Roman]'=============================================[/FONT][/COLOR]
 
[FONT=Times New Roman]DoCmd.Echo [COLOR=blue]False[/COLOR], "Running Program - mod_eFlowProcess3" [/FONT]
[FONT=Times New Roman]DoCmd.Hourglass [COLOR=blue]True[/COLOR][/FONT]
[FONT=Times New Roman]DoCmd.SetWarnings [COLOR=blue]False[/COLOR] [/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] filenum [COLOR=blue]As Integer[/COLOR] [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] Count [COLOR=blue]As Long[/COLOR] [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] tmp [COLOR=blue]As String[/COLOR] [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] rst [COLOR=blue]As[/COLOR] DAO.Recordset [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] DB [COLOR=blue]As[/COLOR] Database [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] FS [COLOR=blue]As[/COLOR] FileSystemObject [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] Folder [COLOR=blue]As[/COLOR] Folder [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] subFolder [COLOR=blue]As[/COLOR] Folder [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] File [COLOR=blue]As[/COLOR] File  [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] TextFilePath [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] dtmDate [COLOR=blue]As[/COLOR] [COLOR=blue]Date[/COLOR] [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] dtmFileDate [COLOR=blue]As Date[/COLOR] [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] TextFileDate  [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] NameOfFile [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] FileNameWithExt [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] strTemp [COLOR=blue]As String[/COLOR] [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Dim[/COLOR] FileLoc [COLOR=blue]As String[/COLOR] [/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Const[/COLOR] ForReading = 1 [/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] DB = CurrentDb [/FONT]
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] FS = CreateObject("Scripting.FileSystemObject") [/FONT]
 
[FONT=Times New Roman]   dtmDate = Date  [/FONT]
 
[FONT=Times New Roman]   TextFilePath = "H:\John Lee\Test Folders\"  [/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] Folder = FS.GetFolder(TextFilePath) [/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]For Each[/COLOR] subFolder [COLOR=blue]In[/COLOR] Folder.SubFolders [/FONT]
[FONT=Times New Roman]       [COLOR=blue]For Each[/COLOR] File [COLOR=blue]In[/COLOR] subFolder.Files[/FONT]
[FONT=Times New Roman]           NameOfFile = GetAttr("File.Name") [/FONT]
[FONT=Times New Roman]           [COLOR=blue]If[/COLOR] Right(File.Name, 4) = ".txt" [COLOR=blue]Then[/COLOR]                       [/FONT]
[FONT=Times New Roman]               FileNameWithExt = Mid$(File.Name, InStrRev(File.Name, "\") + 1) [/FONT]
[FONT=Times New Roman]               strTemp = Mid$(File.Name, InStrRev(File.Name, "\") + 1)[/FONT]
[FONT=Times New Roman]               NameOfFile = Left$(strTemp, InStrRev(strTemp, ".") - 1) [/FONT]
 
[FONT=Times New Roman]               subFilePath = Left$(subFolder, InStrRev(subFolder, "\")) [/FONT]
[FONT=Times New Roman]               SubFolderName = Mid$(subFolder, InStr(3, subFolder, "\")) [/FONT]
[FONT=Times New Roman]               FileLoc = subFolder & "\" & File.Name [/FONT]
[FONT=Times New Roman]               [COLOR=blue]If[/COLOR] File.Name <> "rtmail.txt" [COLOR=blue]Then[/COLOR] [/FONT]
[FONT=Times New Roman]                   FileName = SubFolderName & "\" & File.Name[/FONT]
[FONT=Times New Roman]                   [COLOR=blue]Call[/COLOR] ReadTextFile[/FONT]
[FONT=Times New Roman]               [COLOR=blue]End If[/COLOR][/FONT]
[FONT=Times New Roman]           [COLOR=blue]End If[/COLOR][/FONT]
[FONT=Times New Roman]           DoEvents[/FONT]
[FONT=Times New Roman]           DoCmd.Echo [COLOR=blue]True[/COLOR], "PROCESS 3: Importing Text Files From Live Drive Location: " & NameOfFile & ".txt" [/FONT]
[FONT=Times New Roman]       [COLOR=blue]Next[/COLOR][/FONT]
[FONT=Times New Roman]  [COLOR=blue]Next[/COLOR][/FONT]
 
[FONT=Times New Roman]   DoCmd.Echo [COLOR=blue]True[/COLOR], "Program End" [/FONT]
[FONT=Times New Roman]   DoCmd.Hourglass [COLOR=blue]False[/COLOR]  [/FONT]
[FONT=Times New Roman]   DoCmd.SetWarnings [COLOR=blue]True[/COLOR] [/FONT]
[COLOR=blue][FONT=Times New Roman]End Function[/FONT][/COLOR]


Code: 
[FONT=Times New Roman][COLOR=blue]Function[/COLOR] ReadTextFile()[/FONT]
[COLOR=blue][FONT=Times New Roman]On Error Resume Next[/FONT][/COLOR]
 
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] DB [COLOR=blue]As[/COLOR] Database[/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] rst [COLOR=blue]As[/COLOR] DAO.Recordset[/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] FileToProcess [COLOR=blue]As String[/COLOR][/FONT]
 
[FONT=Times New Roman]FileToProcess = FileName[/FONT]
 
[FONT=Times New Roman][COLOR=blue]Set[/COLOR] ObjFSO = CreateObject("Scripting.FileSystemObject") [/FONT]
[FONT=Times New Roman][COLOR=blue]Set[/COLOR] ObjFile = ObjFSO.OpenTextFile(FileToProcess, 1) [/FONT]
 
[FONT=Times New Roman][COLOR=blue]Set[/COLOR] DB = CurrentDb[/FONT]
 
[FONT=Times New Roman][COLOR=blue]Do Until[/COLOR] ObjFile.AtEndOfStream  [/FONT]
[FONT=Times New Roman]   ObjFile.Skip (1)[/FONT]
[FONT=Times New Roman]   strBranchNoTextFile = ObjFile.Read(2)[/FONT]
[FONT=Times New Roman]   ObjFile.Skip (12)[/FONT]
[FONT=Times New Roman]   strTitleTextFile = ObjFile.Read(10)[/FONT]
[FONT=Times New Roman]   strInitialsTextFile = ObjFile.Read(15)[/FONT]
[FONT=Times New Roman]   strSurnameTextFile = ObjFile.Read(25)[/FONT]
[FONT=Times New Roman]   strFirstLineAddTextFile = ObjFile.Read(30)[/FONT]
[FONT=Times New Roman]   strSecondLineAddTextFile = ObjFile.Read(30)[/FONT]
[FONT=Times New Roman]   strThirdLineAddTextFile = ObjFile.Read(30)[/FONT]
[FONT=Times New Roman]   strForthLineAddTextFile = ObjFile.Read(30)[/FONT]
[FONT=Times New Roman]   strPostCodeTextFile = ObjFile.Read(8)[/FONT]
[FONT=Times New Roman]   strTelNoTextFile = ObjFile.Read(15)[/FONT]
[FONT=Times New Roman]   strDoBTextFile = ObjFile.Read(7)[/FONT]
[FONT=Times New Roman]   strstrManufacturerTextFile = ObjFile.Read(10)[/FONT]
[FONT=Times New Roman]   strstrModelNoTextFile = ObjFile.Read(15)[/FONT]
[FONT=Times New Roman]   strApplianceDescriptionTextFile = ObjFile.Read(30)[/FONT]
[FONT=Times New Roman]   strProductCodeTextFile = ObjFile.Read(6)[/FONT]
[FONT=Times New Roman]   strCoverPeriodTextFile = ObjFile.Read(1)[/FONT]
[FONT=Times New Roman]   strSerialNoTextFile = ObjFile.Read(20)[/FONT]
[FONT=Times New Roman]   strPurchaseDateTextFile = ObjFile.Read(6)[/FONT]
[FONT=Times New Roman]   strPurchasePriceTextFile = ObjFile.Read(7)[/FONT]
[FONT=Times New Roman]   ObjFile.Skip (37)[/FONT]
[FONT=Times New Roman]   strDPATextFile = ObjFile.Read(1)[/FONT]
[FONT=Times New Roman]   strExtendedPeriodText = ObjFile.Read(1)[/FONT]
[FONT=Times New Roman]   strAgentNoTextFile = ObjFile.Read(5)[/FONT]
[FONT=Times New Roman]   strEmailAddTextFile = ObjFile.Read(50)[/FONT]
[FONT=Times New Roman]   ObjFile.Skip (20)[/FONT]
[FONT=Times New Roman]   strScannedBatchNoTextFile = ObjFile.Read(8)[/FONT]
[FONT=Times New Roman]   strScannedBatchDateTextFile = ObjFile.Read(8)[/FONT]
[FONT=Times New Roman]   strUserIDTextFile = ObjFile.Read(10)[/FONT]
[FONT=Times New Roman]   strImagePathTextFile = ObjFile.Read(250)[/FONT]
 
[FONT=Times New Roman]   [COLOR=blue]Set[/COLOR] rst = DB.OpenRecordset("tblTextFile") [/FONT]
[FONT=Times New Roman]       rst.AddNew [/FONT]
[FONT=Times New Roman]       rst!strBranchNo = strBranchNoTextFile[/FONT]
[FONT=Times New Roman]       rst!strTitle = strTitleTextFile[/FONT]
[FONT=Times New Roman]       rst!strInitials = strInitialsTextFile[/FONT]
[FONT=Times New Roman]       rst!strSurname = strSurnameTextFile[/FONT]
[FONT=Times New Roman]       rst!strFirstLineAdd = strFirstLineAddTextFile[/FONT]
[FONT=Times New Roman]       rst!strSecondLineAdd = strSecondLineAddTextFile[/FONT]
[FONT=Times New Roman]       rst!strThirdLineAdd = strThirdLineAddTextFile[/FONT]
[FONT=Times New Roman]       rst!strForthLineAdd = strForthLineAddTextFile[/FONT]
[FONT=Times New Roman]       rst!strPostCode = strPostCodeTextFile[/FONT]
[FONT=Times New Roman]       rst!strTelephoneNo = strTelNoTextFile[/FONT]
[FONT=Times New Roman]       rst!strDoB = strDoBTextFile[/FONT]
[FONT=Times New Roman]       rst!strManufacturer = strstrManufacturerTextFile[/FONT]
[FONT=Times New Roman]       rst!strModelNo = strstrModelNoTextFile[/FONT]
[FONT=Times New Roman]       rst!strApplianceDescription = strApplianceDescriptionTextFile[/FONT]
[FONT=Times New Roman]       rst!strProductCode = strProductCodeTextFile[/FONT]
[FONT=Times New Roman]       rst!strCoverPeriod = strCoverPeriodTextFile[/FONT]
[FONT=Times New Roman]       rst!strSerialNo = strSerialNoTextFile[/FONT]
[FONT=Times New Roman]       rst!strPurchaseDate = strPurchaseDateTextFile[/FONT]
[FONT=Times New Roman]       rst!strPurchasePrice = strPurchasePriceTextFile[/FONT]
[FONT=Times New Roman]       rst!StrDPA = strDPATextFile[/FONT]
[FONT=Times New Roman]       rst!strExtendedPeriod = strExtendedPeriodTextFile[/FONT]
[FONT=Times New Roman]       rst!strAgentNo = strAgentNoTextFile[/FONT]
[FONT=Times New Roman]       rst!strEmailAdd = strEmailAddTextFile[/FONT]
[FONT=Times New Roman]       rst!strScannedBatchNo = strScannedBatchNoTextFile[/FONT]
[FONT=Times New Roman]       rst!strScannedBatchDate = strScannedBatchDateTextFile[/FONT]
[FONT=Times New Roman]       rst!strUserID = strUserIDTextFile[/FONT]
[FONT=Times New Roman]       rst!strImagePath = strImagePathTextFile[/FONT]
[FONT=Times New Roman]       rst.Update[/FONT]
[FONT=Times New Roman]       rst.Close[/FONT]
[FONT=Times New Roman]       [COLOR=blue]Set[/COLOR] rst = [COLOR=blue]Nothing[/COLOR][/FONT]
[FONT=Times New Roman]       ObjFile.SkipLine[/FONT]
[FONT=Times New Roman][COLOR=blue]Loop[/COLOR][/FONT]
[FONT=Times New Roman]ObjFile.Close [/FONT]
[COLOR=blue][FONT=Times New Roman]End Function[/FONT][/COLOR]

And the sample data below is the data in a text file that I want to read into my database.

<Song FilePath="C:\Users\John Lee\Music\00's\All Saints\On & On.wma" FileSize="5773158">
<Display Author="All Saints" Title="On & On" Genre="Pop" Color="7693971" Tag="2" />
<Infos SongLength="10543104" FirstSeen="802110639" />
<BPM Bpm="26199" Phase="21358" BeatPhase="2" />
<FAME IsScanned="1" Volume="8099" />
<Automix MixType="1" CutStart="427520" CutEnd="9505472" FadeStart="428032" FadeEnd="10279424" RealStart="0" RealEnd="10542592" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\70's Rock\America\Ventura Highway.wma" FileSize="3366714">
<Display Author="America" Title="Ventura Highway" Genre="Rock" Year="1972" Color="7863208" Cover="2048" Tag="2" />
<Infos SongLength="9179136" FirstSeen="802110652" />
<BPM Bpm="16174" Phase="11504" />
<FAME IsScanned="1" Volume="6043" />
<Automix FadeStart="671232" FadeEnd="8748032" RealStart="0" RealEnd="9178624" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\00's\All Saints\Chick Fit.wma" FileSize="5164756">
<Display Author="All Saints" Title="Chick Fit" Genre="Pop" Color="6179899" Tag="2" />
<Infos SongLength="9431040" FirstSeen="802110639" />
<BPM Bpm="26457" Phase="13196" />
<FAME IsScanned="1" Volume="8157" />
<Automix MixType="1" CutStart="13056" CutEnd="9200704" FadeStart="26112" FadeEnd="9271808" RealStart="0" RealEnd="9430528" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\00's\Amillionsons\Misty Blue.wma" FileSize="3641588">
<Display Author="Amillionsons" Title="Misty Blue" Genre="Pop" Year="2002" Color="33253" Tag="2" />
<Infos SongLength="9936896" FirstSeen="802110639" />
<BPM Bpm="23077" Phase="9232" />
<FAME IsScanned="1" Volume="7705" />
<Automix MixType="1" CutStart="1194048" CutEnd="8594112" FadeStart="343040" FadeEnd="9329152" RealStart="0" RealEnd="9936384" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\70's\Stylistics\Betcha By Golly, Wow.wma" FileSize="3205292">
<Display Author="Stylistics" Title="Betcha By Golly, Wow" Genre="Pop" Year="1972" Color="14197421" Cover="2048" Tag="2" />
<Infos SongLength="8740864" FirstSeen="802110652" />
<BPM Bpm="16608" Phase="10592" />
<FAME IsScanned="1" Volume="6032" />
<Automix FadeStart="17408" FadeEnd="8230912" RealStart="0" RealEnd="8740352" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\00's House\Tears For Fears\The closest thing to heaven (BIR MIX).wma" FileSize="7968220">
<Display Author="Tears For Fears" Title="The Closest Thing To Heaven (BIR MIX)" Genre="House" Color="15346150" Tag="2" />
<Infos SongLength="21803008" FirstSeen="802110642" />
<BPM Bpm="20356" Phase="1044" BeatPhase="3" />
<FAME IsScanned="1" Volume="7187" />
<Automix MixType="3" TempoStart="652436" TempoEnd="20845588" CutStart="795264" CutEnd="21618656" FadeStart="16896" FadeEnd="21641216" RealStart="0" RealEnd="21802496" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\Albums\Fleetwood Mac\Watch Out!.wma" FileSize="6170418">
<Display Author="Fleetwood Mac" Title="Watch Out!" Genre="Rock" Color="10388472" Cover="2048" Tag="2" />
<Infos SongLength="11237376" FirstSeen="802110659" />
<BPM Bpm="23456" Phase="6112" />
<FAME IsScanned="1" Volume="3331" />
<Automix CutStart="797120" FadeStart="54784" FadeEnd="11064832" RealStart="0" RealEnd="11236864" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\00's Indie\Arctic Monkeys\I Bet You Look Good On The Dancefloor.wma" FileSize="4255218">
<Display Author="Arctic Monkeys" Title="I Bet You Look Good On The Dancefloor" Genre="Indie" Color="8368481" Tag="2" />
<Infos SongLength="7757824" FirstSeen="802110642" />
<BPM Bpm="25544" Phase="18848" BeatPhase="1" />
<FAME IsScanned="1" Volume="8590" />
<Automix MixType="3" TempoStart="759624" TempoEnd="6481480" CutStart="51392" CutEnd="7198112" FadeStart="22528" FadeEnd="7469056" RealStart="0" RealEnd="7757312" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\80's\Lotus Eaters\First Picture Of You.wma" FileSize="5344768">
<Display Author="Lotus Eaters" Title="First Picture Of You" Genre="Pop" Year="1983" Color="733642" Cover="2048" Tag="2" />
<Infos SongLength="14604288" FirstSeen="802110654" />
<BPM Bpm="16070" Phase="9160" BeatPhase="3" />
<FAME IsScanned="1" Volume="5794" />
<Automix FadeStart="1831424" FadeEnd="14229504" RealStart="0" RealEnd="14603776" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\50's\Jive Aces\Sweet Gerogia Brown.wma" FileSize="3139052">
<Display Author="Jive Aces" Title="Sweet Gerogia Brown" Genre="Swing" Color="3855444" Tag="2" />
<Infos SongLength="8560640" FirstSeen="803161041" />
<BPM Bpm="19320" Phase="12736" BeatPhase="1" />
<FAME IsScanned="1" Volume="7751" />
<Automix TempoStart="920776" FadeStart="28160" FadeEnd="8423424" RealStart="0" RealEnd="8560128" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\60's\Susan Maughan\Bobby's Girl.mp3" FileSize="3343612">
<Display Author="Susan Maughan" Title="Bobby's Girl" Genre="Rock 'N' Roll" Year="1962" Color="16286289" Cover="2" Tag="1" />
<Infos SongLength="6879744" FirstSeen="802110650" FirstPlay="806132048" LastPlay="1307022126" PlayCount="4" Bitrate="160" BpmTag="19322" />
<BPM Bpm="18528" Phase="1088" BeatPhase="2" />
<FAME IsScanned="1" Volume="6875" />
<Automix MixType="1" CutStart="38336" CutEnd="6369760" FadeStart="16896" FadeEnd="6707200" RealStart="0" RealEnd="6879232" />
</Song>
<Song FilePath="C:\Users\John Lee\Music\70's Reggae\Judge Dread\The Winkle Man.wma" FileSize="2822926">
<Display Author="Judge Dread" Title="The Winkle Man" Genre="Reggae" Year="1976" Color="1939604" Cover="2048" Tag="2" />
<Infos SongLength="7684096" FirstSeen="802110652" />
<BPM Bpm="32328" Phase="4896" BeatPhase="1" />
<FAME IsScanned="1" Volume="6741" />
<Automix MixType="1" CutStart="19200" CutEnd="6380384" FadeStart="26112" FadeEnd="7428608" RealStart="0" RealEnd="7683584" />
</Song>

The Data in red is the data I want to read into my database and the data in blue is the trigger to identify the data to be read. As you can see from the sample data not all of them have a Year associated with them, so I need to be able to have code that can search for the trigger data and then read in the data to the right of that trigger data, the data to read will always appear between double quotes to the right of the trigger data and after the equals symbol.

So in my database the trigger data is associated with the fields shown below:

Database Text File
Fields Data
strArtist - Display Author
strSongTitle - Title
strGenre - Genre
strYear - Year

Regards

John
 

Attachments

What you are looking for is XML parsing.. Google will have several hits on the topic use keywords like "VBA XML parsing".

I have myself not used it, so I cannot be of huge help here, but it should get you started.
 
Hi,

Thanks for the tip, I'll have a look and let you know how I get on.

Regards

John
 
Hi,

I've googled as you suggested, came up with a lot of stuff to do with XML programming to parse text files, unfortunately I'm not that familiar with XML programming, nodes, tags etc a bit out of my depth to be honest. I'm only just starting to get some sort of handle on VB, but as you can see I still need help with that. :o

So there's no method I can use in vb to get at the data I described in my earlier post then, it is a true text file that was converted from an XML file, I just need to know how to get at the data identified.

I just want to get at the data in the identified text lines, any suggestions would be appreciated.

Regards

John
 
John, I have written this code that will do something you want..

BUT ! This is very much hard coded. Might not be suitable for something generic.. You have been warned !

Code: 
Public Sub readText()
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    Dim FileNum As Integer
    Dim DataLine As String
    Dim authStr As String, titleStr As String
    Dim genStr As String, yearStr As String
    
    FileNum = FreeFile()
[COLOR=Green]    'Change the file location.[/COLOR]
    Open "C:\Users\John Lee\Desktop\sample.txt" For Input As #FileNum
    
    While Not EOF(FileNum)
        Line Input #FileNum, DataLine [COLOR=Green]' read in data 1 line at a time[/COLOR]
        DataLine = Replace(DataLine, """", "'")
        If InStr(DataLine, "<Display") <> 0 Then
            authStr = IIf(InStr(DataLine, "Author"), Mid(DataLine, InStr(DataLine, "='") + 2), vbNullString)
            titleStr = IIf(InStr(DataLine, "Title"), Mid(authStr, InStr(authStr, "=") + 2), vbNullString)
            genStr = IIf(InStr(DataLine, "Genre"), Mid(titleStr, InStr(titleStr, "=") + 2), vbNullString)
            yearStr = IIf(InStr(DataLine, "Year"), Mid(genStr, InStr(genStr, "=") + 2), vbNullString)
            
            authStr = Replace(Mid(authStr, 1, InStr(authStr, "'")), "'", vbNullString)
            titleStr = Replace(Mid(titleStr, 1, InStr(titleStr, "'")), "'", vbNullString)
            genStr = Replace(Mid(genStr, 1, InStr(genStr, "'")), "'", vbNullString)
            yearStr = Replace(Mid(yearStr, 1, InStr(yearStr, "'")), "'", vbNullString)
            
            CurrentDb.Execute ("INSERT INTO tblMusicList ([Author], [Title], [Genre], [Year]) VALUES ('" & authStr & "', '" & titleStr & "', '" & genStr & "', '" & yearStr & "')")
        End If
    Wend
End Sub
 
Last edited:
Good afternoon Paul,

Thanks very much for that, I will let you know how I get on. I must say I wasn't expecting it all, but it is greatly appreciated.

Regards.

John
 
No problem, just that you know I am considering the Year to be a String.. If it is not a Text type in your table, then modify that accordingly. Keep me posted. Good Luck !
 
Hi Paul,

Thanks for the heads up, but you were correct it is a string and not numeric. I'll let you know how I get on.

Regards

John
 
Good morning Paul,

I've tried your code and I get the following message "Run-time error 3134 - Syntax error in INSERT INTO statement".

I've googled this error message but couldn't find anything that made sense to my particular problem.

Attached are some screen shots of the error message and the line of code that it highlights is:

Code: 
[FONT=Arial]CurrentDb.Execute ("INSERT INTO tblMusicList ([Author], [Title], [Genre], [Year]) VALUES ('" & authStr & "', '" & titleStr & "', '" & genStr & "', '" & yearStr & "'")[/FONT]

I thought that perhaps in might be the field names in my table that might be causing the problem so I changed them to match the following:

Author, Title, Gener & Year, but that made no difference.

I also thought I should check my references, of which a screenshot is attached.

Any suggestions would be appreciated.

Regards

John
 

Attachments

  • Run-time error 3134 - Syntax error in INSERT INTO statement.JPG
    Run-time error 3134 - Syntax error in INSERT INTO statement.JPG
    10.7 KB · Views: 596
  • Run-time error 3134 - Syntax error in INSERT INTO statement 2.JPG
    Run-time error 3134 - Syntax error in INSERT INTO statement 2.JPG
    28.4 KB · Views: 686
  • MS Access 2000 Module References.JPG
    MS Access 2000 Module References.JPG
    42.8 KB · Views: 313
Hi Paul,

I've run the code with the immediate window open, but nothing populates the immediate window. I see the same message box with the same information about a syntax error.

Is there somethin I need to do to get information to populate the immediate window.

Regards

John
 
Okay I found the error.. Try this..
Code: 
CurrentDb.Execute ("INSERT INTO tblMusicList ([Author], [Title], [Genre], [Year]) VALUES ('" & authStr & "', '" & titleStr & "', '" & genStr & "', '" & yearStr [COLOR=Red][B]& "')")[/B][/COLOR]
 
Hi Paul,

I Replaced the code with your updated code and got a compile syntax error message and this part (in red) was highlighted:

Edit: 11:20 hours - it was expected an expression

Code: 
[COLOR=red]*&[/COLOR] "')")*

To get the Immediate window to work do I type:

Code: 
Debug.print readtext()

Do I place the Debug code after the "wend" and before the "End sub" statements

Regards

John
 
Why is there an * before the ampersand ? Remove that..

To debug, when the Code throws an error, go to the Code window, copy the insert statement. In the immediate type a Question mark followed by the copied Insert statement and hit enter, it would show you the generated SQL.
Code: 
? "INSERT INTO tblMusicList ([Author], [Title], [Genre], [Year]) VALUES ('" & authStr & "', '" & titleStr & "', '" & genStr & "', '" & yearStr & "')"
Or even better assign the Insert statement to a String variable and CODE that to be printed using the Debug.Print statement just before the Current DB statement..
Code: 
            genStr = Replace(Mid(genStr, 1, InStr(genStr, "'")), "'", vbNullString)
            yearStr = Replace(Mid(yearStr, 1, InStr(yearStr, "'")), "'", vbNullString)
            
            sampSQL = "INSERT INTO tblMusicList ([Author], [Title], [Genre], [Year]) VALUES ('" & authStr & "', '" & titleStr & "', '" & genStr & "', '" & yearStr & "')"
            Debug.Print sampSQL
            
            CurrentDb.Execute (sampSQL)
        End If
 
Hi Paul,

Your a star, that has worked now, I don't why the asterisk was there, I did copy your code as per your post. I will also start to learn more about how to use the immediate window to debug problems, thanks for your pointers and most of all your help in solving this particular problem for me.

Regards

John
 
Hi Paul,

Sorry to be a pain, I just noticed that any records that do not have a year associated with it, is not added to my table, is that an easy fix.

Regards

John
 
In the table, change the Year field property : Allow Zero Length to Yes. That should sort your problem !
 
Good afternoon Paul,

I ran your code against my main text file in which the sampe data came from, something I identified that I didn't with the sample data I supplied was the following:

Where any records contained an apostrophe within the text of the data to be extracted only had data preceeding that apostrophe imported into my database table for example:

Title="Bobby's Girl" only Bobby was imported into the respective field and under genre where Genre="Rock 'N' Roll" appeared only Rock was imported into the respective field.

Something else I noticed because it was not included in the sample data and only identified when I ran the code against the main text file, was that where Album="Some text here" was in the line of data and because it preceeded Year="1972" that any text that contained within that part of the text line was imported into the table into the Year field.

below is an example of a line of text that includes the Album aspect:

<Display Author="Judge Dread" Title="The Winkle Man" Genre="Reggae" Album="Dread The Naughty Bits" Year="1976" Color="1939604" Cover="2048" Tag="2" />

It would appear to me that anything that has an apostrophe in the text line, that only data to the left of that apostrophe was being imported into my respective database fields.

As there were no comments in your supplied code I struggled to work out what your code was doing and how, so I was wondering if you would be kind enough to do that, that way perhaps I can work out how to address the above identified observations.

Your assistance is most appreciated.

Regards
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom