Is there an error with my FileSystemObject codes?

[shawnnnnnnn]

Hi all,

I'm currently facing an issue with my codes. I am required to import a text file line by line into access, however, when I check the number of records in Access, it does not match the number of lines in the text file.

E.g. My text file may have 519420 lines of text, but my records only show 518785.

I need my entire text file to be imported line by line in the exact order.

This is my current import code:

Private Sub Command3_Click()
    Dim fs As Object
    Dim filename As String
    Dim tsIn As Object
    Dim sFileIn As String
    Dim Text As String
    Dim sqlcre As String
    Dim sqlsta As String
    
    sFileIn = Me.txtImport
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set tsIn = fs.OpenTextFile(sFileIn, 1)
    
    While Not tsIn.AtEndOfStream
        tmps = tsIn.ReadLine
        sqlsta = "INSERT INTO Table1(Field1) VALUES ('" & Replace(tmps, "'", "''") & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL sqlsta
    Wend
    DoCmd.SetWarnings True
    MsgBox "Finished."
End Sub

[twinnyfo]

The only thing I can think of is that you might have some blank lines in the original file. Since there are about 700 lines missing (which is about 1/10th of 1 percent), you may have to experiment with your import. Start with a small portion of your original text file and import all the records. Keep increasing the size of the text file until the number of records imported does not equal the number of lines in the text file. Then, troubleshoot to determine what is different about that particular line that prevent a full import of the line.

Make sense?

I think your code is fine (the same code the experts here have helped you develop).

[jforbes]

I agree with Twinnyfo, you'll need to do some creative troubleshooting to find out what is happening.

Have you looked at the file in notepad with Word Wrap turned off? That would be a quick visual check for blank lines.

You could also put in a couple counters in your code and throw an error or break when they get out of sync:

Private Sub Command3_Click()
     Dim fs As Object
     Dim filename As String
     Dim tsIn As Object
     Dim sFileIn As String
     Dim Text As String
     Dim sqlcre As String
     Dim sqlsta As String
     Dim lLoopCount As Long
     Dim lStartRecordCount As Long
     Dim lRecordCount As Long

     lStartRecordCount = DCount("Field1", "Table1") 
     sFileIn = Me.txtImport
     Set fs = CreateObject("Scripting.FileSystemObject")
     Set tsIn = fs.OpenTextFile(sFileIn, 1)
 
     While Not tsIn.AtEndOfStream
         tmps = tsIn.ReadLine
         sqlsta = "INSERT INTO Table1(Field1) VALUES ('" & Replace(tmps, "'", "''") & "');"
         DoCmd.SetWarnings False
         DoCmd.RunSQL sqlsta
         
         lLoopCount = lLoopCount  + 1
         lRecordCount = DCount("Field1", "Table1")
         If lLoopCount <> (lRecordCount - lStartRecordCount) Then Err.Raise 12345, , "Record Count out of Sync on line " & lRecordCount 
     Wend
     DoCmd.SetWarnings True
     MsgBox "Finished."
 End Sub

[shawnnnnnnn]

Well, I managed to find some lines that were not inputted into the the table.

The lines are:
" :61:1410311031D 22,50NSC-SW000000//SC-SW"
" :86:M04"
" 000000"
" :62F:C141031USD 746743,53"
" :64:C141031USD7 46743,53 )]~"

Is there a possibility that the code is unable to read lines that start with " :"?

[twinnyfo]

Hmmmmmmmmmmmmmm mm, I was able to insert those lines into my tables......

Are you sure these are not wrap lines (perhaps part of a line that is more than 255 characters)?

There does not appear to be anything incredibly strange about your text.

This is an ASCII file, not Unicode? No hidden special characters or anything like that?

[shawnnnnnnn]

It's actually a unicode file, however, I've already checked through and I don't think there are any special characters in it.

[twinnyfo]

Shawn,

The Unicode may be the issue. Have you tried changing the data type of the field in the table to "Memo" and setting the TextFormat Property to Rich Text?

Keep in mind that this may significantly bloat your DB during the import, but it may allow you to import your text.

[shawnnnnnnn]

Hi twinny,

I've already tried those methods and it still can't work. I know this is highly unlikely, but could it be a hardware/software issue?

[shawnnnnnnn]

Well, I might have found a solution, but it'll mean more work for the user. I have to open the text log file in notepad++, copy the entire thing and paste it in a new file in notepad++ and import that file instead.

[twinnyfo]

That will certainly get rid of those strange characters. It is a solution--perhaps not your preferred solution, but a solution, nonetheless....