Read Data From A Text File Into DB (1 Viewer)

pr2-eugin

Super Moderator
Local time
Today, 21:56
Joined
Nov 30, 2011
Messages
8,498
John are you only interested in the 4 information? Author, Title, Genre, and Year? I have another working method, but it collects all information. :confused:

And on top of that if the data is not organized properly, then the UPDATE will fail. What I mean is, some of them have :

Author="America" Title="Ventura Highway" Genre="Rock" Year="1972" Color="7863208" Cover="2048" Tag="2"

Author="All Saints" Title="Chick Fit" Genre="Pop" Color="6179899" Tag="2"

In the method I changed its quiet not right. I think I am going to say Sorry ! :(
 
Last edited:

JohnLee

Registered User.
Local time
Today, 13:56
Joined
Mar 8, 2007
Messages
692
Hi Paul,

No need for apologies, you've been a great help. The information I'm interested in is the Author, Title, Genre and Year.

The text file in which this information is stored as you have pointed out has other information in there too.

So in the text file in which this information is being extracted a complete record could like like as shown below:

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


But as you can see from the above example the Album="some data here" is not shown, that is because there is no information pertaining to that within this particular record, however the example below show a record where that information is contained within a record:

<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" Album="Judge Dread Naughty Bits" 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
>

By the same token there are some records that do not contain the Year information, but you already covered that in a previous post, in that if there was no year information to ensure that the field properties setting was changed to allow zero length data.

The information in red in each case is the only information I would like to extract from the text file, so only data that is within the text line beginning <Display ............ and ending /> is what I am actually interested in extracting and only the following caption headers:

Author="some data"
Title="some data"
Genre="some data"
Year="some data"

Everything within the double quotes is what I would like to populate my table with and ignor all other data in that line.

I've identified that there are 4 possible line formats with the <Display line as follows:

<Display Author="Jive Aces" Title="Sweet Gerogia Brown" Genre="Swing" Color="3855444" Tag="2" />

<Display Author="Jive Aces" Title="Sweet Gerogia Brown" Album="Some data here" Genre="Swing" Color="3855444" Tag="2" />

<Display Author="Jive Aces" Title="Sweet Gerogia Brown" Album="Some data here" Genre="Swing" Year="1983" Color="3855444" Tag="2" />

<Display Author="Jive Aces" Title="Sweet Gerogia Brown" Genre="Swing" Year="1983" Color="3855444" Tag="2" />

So to recap, all the data within the double quotes for each caption header is the data I would like to extract to my table.

I hope this clarifies what I would like to achieve, your assistance has been great and is most appreciated.

John
 

pr2-eugin

Super Moderator
Local time
Today, 21:56
Joined
Nov 30, 2011
Messages
8,498
Last attempt..
Code:
Public Sub readText()
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    Dim FileNum As Integer, iCtr As Long, jCtr As Long, kCtr As Long
    Dim DataLine As String, tmpArr() As String, valStr As String, fieldStr As String
    Dim upArr(0 To 20) As String, mainArr(0 To 20) As String, tmpStr As String
    
    
    FileNum = FreeFile()
   [COLOR=Green] 'This opens the file to be read.[/COLOR]
    Open "C:\Users\pef\Desktop\sample.xml" For Input As #FileNum
    
    While Not EOF(FileNum)
        [COLOR=Green]'Read in data 1 line at a time[/COLOR]
        Line Input #FileNum, DataLine
        
        [COLOR=Green]'This line replaces all Double Quotes with Single Quotes,
        'as Access struggles with parsing them[/COLOR]
        DataLine = Replace(DataLine, """", "'")
        
        [COLOR=Green]'Main seperation, as we are interested only with the Display tag.[/COLOR]
        If InStr(DataLine, "<Display") <> 0 Then
            fieldStr = vbNullString
            valStr = vbNullString
            
            [COLOR=Green]'Extracts the information, that we need other than '<Display' and '/>'[/COLOR]
            tmpStr = Trim(Mid(DataLine, InStr(DataLine, " ") + 1, InStrRev(DataLine, " />")))
            tmpStr = Trim(Mid(tmpStr, 1, InStrRev(tmpStr, " />")))
            
            [COLOR=Green]'Split the information based on the Equals operator.[/COLOR]
            tmpArr = Split(tmpStr, "=")
            jCtr = 0
            kCtr = 0
            For iCtr = 0 To UBound(tmpArr)
                If InStr(tmpArr(iCtr), "'") <> 0 Then
                   [COLOR=Green] 'Get the Field Name[/COLOR]
                    mainArr(kCtr) = Trim(Mid(tmpArr(iCtr), InStrRev(tmpArr(iCtr), "'") + 2))
                   [COLOR=Green] 'Get the Data corresponding to the Field[/COLOR]
                    upArr(jCtr) = Trim(Mid(tmpArr(iCtr), 2, InStrRev(tmpArr(iCtr), "'") - 2))
                    jCtr = jCtr + 1
                Else
                    mainArr(kCtr) = Trim(tmpArr(iCtr))
                End If
                kCtr = kCtr + 1
            Next
            
            For iCtr = 0 To kCtr - 2
                Select Case mainArr(iCtr)
                    [COLOR=Green]'Taking only the information we need from the data.[/COLOR]
                    Case "Author", "Title", "Genre", "Year"
                        fieldStr = fieldStr & mainArr(iCtr) & ", "
                        valStr = valStr & Chr(34) & upArr(iCtr) & Chr(34) & ", "
                End Select
            Next
            
            fieldStr = "(" & Left(fieldStr, Len(fieldStr) - 2) & ") VALUES ("
            
            valStr = Left(valStr, Len(valStr) - 2) & ")"
            
            CurrentDb.Execute ("INSERT INTO tblMusicList " & fieldStr & valStr)
            [COLOR=Green]'Debug.Print "INSERT INTO tblMusicList " & fieldStr & valStr[/COLOR]
        End If
        Erase tmpArr
        Erase mainArr
        Erase upArr
    Wend
End Sub
 

JohnLee

Registered User.
Local time
Today, 13:56
Joined
Mar 8, 2007
Messages
692
Hi Paul,

Is this code meant to be on two seperate lines:

Code:
fieldStr = "(" & Left(fieldStr, Len(fieldStr) - 2) & ") VALUES ("
            
            valStr = Left(valStr, Len(valStr) - 2) & ")"

or is it meant to look like this

Code:
fieldStr = "(" & Left(fieldStr, Len(fieldStr) - 2) & ") VALUES ("valStr = Left(valStr, Len(valStr) - 2) & ")"

The reason I ask, is that nothing happens when I run the code with it split across two lines, when I run it as one line I get a compile error expected end of statement and the following is highlighted:

Code:
valStr

Regards

John
 

pr2-eugin

Super Moderator
Local time
Today, 21:56
Joined
Nov 30, 2011
Messages
8,498
I tested the code before sending it to you ! It works just fine, as I gave you.

After running the code, check the tblMusicList.

If you still unsure, uncomment the Debug.Print line, you will see that it works.
 

JohnLee

Registered User.
Local time
Today, 13:56
Joined
Mar 8, 2007
Messages
692
Hi Paul,

I checked the tblMusic table even when the I got the following message

"Runtime error '5':

Invalid Procedure, call or Argument.

On clicking the debug button this was highlighted in yellow

Code:
fieldStr = "(" & Left(fieldStr, Len(fieldStr) - 2) & ") VALUES ("

So that why I asked the question if it should all be on one line.

I put that code all on one line and I got the following:

Compile error

Expected: end of Statement

I then highlighted the following:

Code:
valStr

I then changed that line of code back to how you sent it to me and uncommented the debug.print and displayed the immediate window on running the code it doesn't get generate anything in the immediate window because the "runtime error '5': comes up before that line of code can be executed.

My apologies if I'm taking up too much of your time, I've followed your instructions to the letter, but not to worry if you are unable to assist any further, I've certainly learnt something from this anyway.

I also checked the tblMusic table after each code run and no data has populated the fields

Regards

John
 

pr2-eugin

Super Moderator
Local time
Today, 21:56
Joined
Nov 30, 2011
Messages
8,498
Are you using a different TXT/XML file? I am wondering maybe something is a bit different, if possible copy some sample, here I will check again.

That error states that fieldStr is a NullString. So in that case Len(fieldStr) which is 0, and take away 2 will return -2, String functions go funny when you try to use negative numbers.
 

JohnLee

Registered User.
Local time
Today, 13:56
Joined
Mar 8, 2007
Messages
692
Hi Paul,

I've attached the sample txt file that I've been using from the beginning of this thread.

Regards

John
 

Attachments

  • Sample Data.txt
    8.2 KB · Views: 58

pr2-eugin

Super Moderator
Local time
Today, 21:56
Joined
Nov 30, 2011
Messages
8,498
Ah ! Found the problem, ;) the main problem was no proper indentation was in the file, once I either formatted it in a XML layout like..
Code:
<songList>
    <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>
Formatted by using Tab, it worked a breeze.. Or even this.. With no Indent should work..
Code:
<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>
It works, so a small bit of manual work I should say..
 

JohnLee

Registered User.
Local time
Today, 13:56
Joined
Mar 8, 2007
Messages
692
Hi Paul,

Thanks very much for all your assistance I assure it has been most appreciated.

A bit of manual work never hurt anyone.

Regards

John
 

JohnLee

Registered User.
Local time
Today, 13:56
Joined
Mar 8, 2007
Messages
692
Hi Paul,

I guess you're getting a bit fed up with me now, but I formatted the sample file as you suggested and ran the code again and I'm still getting an error message with this line of code:

Code:
fieldStr = "(" & Left(fieldStr, Len(fieldStr) - 2) & ") VALUES ("valStr = Left(valStr, Len(valStr) - 2) & ")"

Even if it's split over two lines as you sent in earlier I still get and error message as per my earler posts. :banghead:

I have the following references checked:

Visual Basic For Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Scritping Runtime
Microsoft DAO 3.6 Object Library

Could I be missing a reference that may be causing these error messages to generate.

I'll understand if you want to end this communication here.

Regards

John
 

JohnLee

Registered User.
Local time
Today, 13:56
Joined
Mar 8, 2007
Messages
692
Hi Paul,

I've been trying to work out why the code you supplied keeps falling over when it reaches this block of code, without any success:

Code:
            fieldStr = "(" & Left(fieldStr, Len(fieldStr) - 2) & ") VALUES ("
            
            valStr = Left(valStr, Len(valStr) - 2) & ")"

I keep getting the following error message:

run-time error '5'

Invalide procedure call or argument

and then I have the option to either "End", "Debug" or select the "Help" button.

having read the help information I have not been able to establish why this error message generates when it get to this point in the code, even with the immediate window open and the Debug.Print line of code enabled nothing is displayed in the immediate window.

When I change the code from how it is set out above over two lines into one line, please see below:

Code:
fieldStr = "(" & Left(fieldStr, Len(fieldStr) - 2) & ") VALUES ("valStr = Left(valStr, Len(valStr) - 2) & ")"

I get the following message dialog box and on clicking the "Ok" button the above line of code turns red:

Compile error:

Expected: End Of Statement

and the bit of code in red is highlighted and I get the options to choose the "OK" or "Help" buttons.

Again on reading the Help information I have not been able to identify how to resolve this problem.

I've checked my references and none are marked "MISSING" and I've formatted the text file as per your suggestion and even reverted it back and no matter what the code keeps erroring at this code point.

Your assistance would be most appreciated.

Regards

John
 

JohnLee

Registered User.
Local time
Today, 13:56
Joined
Mar 8, 2007
Messages
692
I placed my cursor at the end of <Song> and pressed the return key to get the following:

Code:
<songList>
 <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>

Sample file also attached.

Regards

John
 

Attachments

  • Sample Data.txt
    8.3 KB · Views: 61

JohnLee

Registered User.
Local time
Today, 13:56
Joined
Mar 8, 2007
Messages
692
Hi Paul,

I've reformatted the text file again this time removing all spaces preceeding the < and re-ran the code and the table has been updated.:eek:

I feel a complete you know what, thanks once again for your professionalism.

Regards

John
 

pr2-eugin

Super Moderator
Local time
Today, 21:56
Joined
Nov 30, 2011
Messages
8,498
Do you have NotePad++ Editor? If you do, open the file with NotePad ++ and follow the steps..

-> CTRL+A
-> SHIFT + Tab (as many times as you could. at least 5 times)
-> CTRL + S

Now do the import.

If you do not have it installed, its Free under GPL License and any programmers must have editor, Download, Install and then do the steps.
 

Users who are viewing this thread

Top Bottom