Simple text import

[davesmith202]

I have a txt file that I want to import into a string. What is the simplest way of doing this?

Thanks,

Dave

 

[vbaInet]

Something like this should work:

public function GetMyFile(pathToFile as string) as string
    dim fileNum As Integer
    
    getmyfile = ""
    fileNum = freeFile

    open pathtofile For Input As #fileNum
        Input #fileNum, GetMyFile
    close #fileNum
end function

 

[davesmith202]

Do I call it like this?

strdata = GetMyFile("C:\Folders\Data\testdata\B21.txt")

 

[vbaInet]

I've amended the code so now you should be able to call it like that.

 

[davesmith202]

This may sound like a daft question but does that pick up just the first line in the text file?

 

[vbaInet]

Always good to try it out first and see what the result is

 

[davesmith202]

I did. I got the first line only!

 

[DCrake]

Your original post did not indicate that there was more than on row of text. Wou actually wanting to do with the text once you have got it. also what does the contents look like? Can you supply a same txt file?

 

[davesmith202]

Not sure how to get the whole file!

 

[DCrake]

Like I said what does the text file contain?

Freetext, Delimited fields, fixed width fields, something else?

How many rows does it contains?
Can they vary?
Cany you post a sample file on the forum to look at?

 

[davesmith202]

Looks like our posts crossed there DCrake. Sample data in text file:

[Event "?"]
[Site "Europe"]
[Date "1620.??.??"]
[Round "?"]
[White "Greco, Gioacchino"]
[Black "NN"]
[Result "*"]
[ECO "B21"]

1. e4 c5 2. f4 e6 3. Nf3 Nc6 4. c3 d5 5. e5 Be7 6. d4 c4 7. Be2 Bh4+ 8. g3
Be7 9. Be3 Bd7 10. Nbd2 Nh6 11. b3 b5 12. a4 a6 13. axb5 axb5 14. b4 O-O
15. O-O Nf5 16. Bf2 Rxa1 17. Qxa1 Nxb4 18. cxb4 Bxb4 19. Qb1 Qa5 20. Qc2 Bc3
21. h3 *

[Event "?"]
[Site "Europe"]
[Date "1620.??.??"]
[Round "?"]
[White "NN"]
[Black "Greco, Gioacchino"]
[Result "0-1"]
[ECO "B21"]

1. e4 c5 2. f4 Nc6 3. Nf3 d6 4. Bc4 Nh6 5. O-O Bg4 6. c3 e6 7. h3 Bxf3
8. Qxf3 Qd7 9. d3 O-O-O 10. f5 Ne5 11. Qe2 Nxc4 12. Bxh6 Na5 13. b4 Nc6
14. Bd2 exf5 15. exf5 f6 16. b5 Ne7 17. Qe6 Qxe6 18. fxe6 Ng6 19. d4 d5
20. Be3 c4 21. Bc1 Re8 22. Re1 Bd6 23. a4 Nf8 24. Nd2 Nxe6 25. Nf3 g5 26. Nh2
h5 27. a5 Rhg8 28. a6 b6 29. Nf1 f5 30. Ne3 Nc7 31. Rf1 f4 32. Nd1 Ne6
33. Ra2 g4 34. Nf2 f3 35. hxg4 hxg4 36. Nh1 0-1

[Event "?"]
[Site "London"]
[Date "1794.??.??"]
[Round "?"]
[White "Atwood, George"]
[Black "Philidor, Francois Andre Dani"]
[Result "1-0"]
[ECO "B21"]

1. e4 c5 2. f4 Nc6 3. Nf3 e6 4. c3 d5 5. e5 f5 6. d4 Nh6 7. h3 Qb6 8. b3 Bd7
9. Be3 Nf7 10. Qd2 O-O-O 11. Qf2 cxd4 12. Nxd4 Nxd4 13. Bxd4 Qc6 14. Nd2 b6
15. a4 Bc5 16. Bb5 Bxd4 17. Qxd4 Qc5 18. Nf3 Bxb5 19. Qxc5+ bxc5 20. axb5 Kb7
21. Ke2 Ra8 22. Ra6 Rhe8 23. Rd1 Nd8 24. Ne1 c4 25. bxc4 dxc4 26. Rd7+ Kc8
27. Rxg7 Rb8 28. Raxa7 Rxb5 29. Rac7+ Kb8 30. Rxc4 Rb7 31. Rb4 Rxb4 32. cxb4
Nc6 33. Nd3 Re7 34. Rxe7 Nxe7 35. Nc5 Ng6 36. Nxe6 Kc8 37. Ke3 Kd7 38. Nd4
Ne7 39. g4 Ke8 40. g5 1-0

I have a parsing routine already that manipulates the data. I just need to get it into a string!

 

[DCrake]

Based on the solution by vbaINet

public function GetMyFile(pathToFile as string) 
    dim fileNum As Integer
    Dim myString As String
    fileNum = freeFile

    open pathtofile For Input As #fileNum
       Do Until EOF(#fileNum)
          Input #fileNum, MyString
          Debug.Print MyString
       Loop

    close #fileNum
end function

 

[davesmith202]

I get a syntax error and compile error on this line:

Do Until EOF(#fileNum)

 

[vbaInet]

That was all aircode, you don't need the hash (#) on that line.

 

[DCrake]

     Do While Not EOF(iFileNum)
        Line Input #iFileNum, sBuf

 

[davesmith202]

Bit confused now because of iFileNum. Should that be fileNum in the above code?

Also, I use var = Split(strPGNdata, vbCrLf) to split my data into an array. When importing using the above method, does it pull in the vbCrLf characters too or does it use just a Lf or just a Cr?

 

[DCrake]

Send me what code you have already. I send the snippet of code to show the syntax code was for brevity only.

 

[davesmith202]

Private Sub cmdParse_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

Set rs = db.OpenRecordset("Table1")




Dim strPGNtoCSV As String
    Dim strPGNdata As String

    'strPGNdata = PGNdata
strPGNdata = GetMyFile("C:\Documents and Settings\Desktop\Folders\Data\testdata\B21.txt")
'MsgBox strPGNdata
'Debug.Print strPGNdata


    Dim var
    var = Split(strPGNdata, vbCrLf)
    Dim tmpMove As String
    'MsgBox var(UBound(var))
   ' MsgBox UBound(var)
    
    strPGNtoCSV = ""

    'show the contents of the array
    Dim i As Integer

    'Cycle through lines
    For i = 0 To UBound(var) - 1
        'Check if Move line
        If (Len(var(i) > 0)) And (InStr(var(i), "[") = 0) Then
            'Cycle through Move lines only
            'Clear move line
            tmpMove = ""
            'Reset counter
           
            x = 0
            Do While (Len(var(i + x) > 0)) And (InStr(var(i + x), "[") = 0) And (UBound(var) > x + i - 1)
                'Add the current row marked by i+x
                
                tmpMove = tmpMove & var(i + x)
                x = x + 1
                If (UBound(var) < x + i) Then Exit Do
            Loop
            'Debug.Print i, x
            i = i + x
            'MsgBox tmpMove
            Debug.Print tmpMove
            'strPGNtoCSV = strPGNtoCSV & tmpMove & "," & Chr(10)
            strPGNtoCSV = strPGNtoCSV & tmpMove & vbCrLf
        Else
            Debug.Print var(i)
            'strPGNtoCSV = strPGNtoCSV & var(i) & "," & Chr(10)
            strPGNtoCSV = strPGNtoCSV & var(i) & vbCrLf
        End If
    Next
    '******************Routine to put data into table *******************
    
    var = Split(strPGNtoCSV, vbCrLf)
       
    rs.AddNew
       'Cycle through lines
    For i = 0 To UBound(var) - 1
    
   If Left(var(i), 2) = "1." Then
   rs!Moves = var(i)
   rs.Update
   rs.AddNew
   End If
    
    If InStr(var(i), "[Event") > 0 Then rs!Event = var(i)
    If InStr(var(i), "[Site") > 0 Then rs!Site = var(i)
    If InStr(var(i), "[Date ") > 0 Then rs!DateField = var(i)
    If InStr(var(i), "[Round") > 0 Then rs!Round = var(i)
    If InStr(var(i), "[White") > 0 Then rs!White = var(i)
    If InStr(var(i), "[Black") > 0 Then rs!Black = var(i)
    If InStr(var(i), "[Result") > 0 Then rs!Result = var(i)
    If InStr(var(i), "[ECO") > 0 Then rs!ECO = var(i)
    If InStr(var(i), "[PlyCount") > 0 Then rs!PlyCount = var(i)
    If InStr(var(i), "[EventDate") > 0 Then rs!EventDate = var(i)
    


    
    Next

    'Debug.Print tmpMove
    Debug.Print "--------------------"
    'MsgBox strPGNtoCSV
    Debug.Print strPGNtoCSV
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    
Dim strFile As String
 strFile = "C:\Documents and Settings\Desktop\Folders\Data\testdata\" & "pgn1" & ".csv"
Open strFile For Output As #1
Print #1, strPGNtoCSV
Close #1

End Sub


Public Function GetMyFile(pathToFile As String)
    Dim fileNum As Integer
    Dim myString As String
    fileNum = FreeFile

    Open pathToFile For Input As #fileNum
'       Do Until EOF(fileNum)
'          Input #fileNum, myString
'          Debug.Print myString
     Do While Not EOF(fileNum)
        Line Input #fileNum, sBuf

       Loop

    Close #fileNum
End Function

 

[DCrake]

Private Sub cmdParse_Click()
dim fileNum As Integer
Dim myString As String
fileNum = freeFile

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim var
Dim tmpMove As String

Set db = CurrentDb()

Set rs = db.OpenRecordset("Table1")

open pathtofile For Input As #fileNum
Do Until EOF(fileNum)
'This get each line of text from the file one line at a time
'And passess it to the string strPGNData
'You will need to put the Loop at the end of the code
'prior to reading in the next line of data from the file
Line Input #fileNum, strPGNdata


var = Split(strPGNdata, vbCrLf)


strPGNtoCSV = ""

'show the contents of the array
Dim i As Integer

'Cycle through lines
For i = 0 To UBound(var) - 1
'Check if Move line
If (Len(var(i) > 0)) And (InStr(var(i), "[") = 0) Then
'Cycle through Move lines only
'Clear move line
tmpMove = ""
'Reset counter

x = 0
Do While (Len(var(i + x) > 0)) And (InStr(var(i + x), "[") = 0) And (UBound(var) > x + i - 1)
'Add the current row marked by i+x

tmpMove = tmpMove & var(i + x)
x = x + 1
If (UBound(var) < x + i) Then Exit Do
Loop
'Debug.Print i, x
i = i + x
'MsgBox tmpMove
Debug.Print tmpMove
'strPGNtoCSV = strPGNtoCSV & tmpMove & "," & Chr(10)
strPGNtoCSV = strPGNtoCSV & tmpMove & vbCrLf
Else
Debug.Print var(i)
'strPGNtoCSV = strPGNtoCSV & var(i) & "," & Chr(10)
strPGNtoCSV = strPGNtoCSV & var(i) & vbCrLf
End If
Next
'******************Routine to put data into table *******************

var = Split(strPGNtoCSV, vbCrLf)

rs.AddNew
'Cycle through lines
For i = 0 To UBound(var) - 1

If Left(var(i), 2) = "1." Then
rs!Moves = var(i)
rs.Update
rs.AddNew
End If

If InStr(var(i), "[Event") > 0 Then rs!Event = var(i)
If InStr(var(i), "[Site") > 0 Then rs!Site = var(i)
If InStr(var(i), "[Date ") > 0 Then rs!DateField = var(i)
If InStr(var(i), "[Round") > 0 Then rs!Round = var(i)
If InStr(var(i), "[White") > 0 Then rs!White = var(i)
If InStr(var(i), "[Black") > 0 Then rs!Black = var(i)
If InStr(var(i), "[Result") > 0 Then rs!Result = var(i)
If InStr(var(i), "[ECO") > 0 Then rs!ECO = var(i)
If InStr(var(i), "[PlyCount") > 0 Then rs!PlyCount = var(i)
If InStr(var(i), "[EventDate") > 0 Then rs!EventDate = var(i)




Next

'Debug.Print tmpMove
Debug.Print "--------------------"
'MsgBox strPGNtoCSV
Debug.Print strPGNtoCSV

rs.Close

Set rs = Nothing
Set db = Nothing


Dim strFile As String
strFile = "C:\Documents and Settings\Desktop\Folders\Data\testdata\" & "pgn1" & ".csv"
Open strFile For Output As #1
Print #1, strPGNtoCSV
Close #1

End Sub

 

[davesmith202]

I get an error with that code, "Do without Loop"

Where should the missing Loop go?