Is there an error with my import codes?

[hclifford]

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

[ranman256]

you do not need any code to import text.

It can be done with a single command:
DoCmd.TransferText acExportDelim, "SPEC NAME", Table, file, True

[JoeM]

Does you table have any conditions that would prevent some records from being imported?
For example, if one of your fields is set to be Unique, but your data has some records which are not Unique, that would prevent some from being added. Or if a field cannot be null.
Anything else which violates the Data Types or field conditions you have placed could cause that.

Also, do you have an AutoNumber field in your Table1? You could use that as your Primary Key to make sure you don't have any Primary Key violations, and it would allow you to keep track of what order the records are added in, which may help in your debugging, You could use that to determine which records are not being added (i.e. Compare record 1000 on your text file to record 1000 in your table. Are they the same? If so, your first 1000 records imported correctly. Try a bigger number. Repeat until you locate a record which has not been added).

[hclifford]

Hi ranman,

Can you elaborate more on this command? Like what does "Spec name" mean?

[hclifford]

Hi Joe,

I'll test it out and get back to you!

[hclifford]

Hi Joe,

I managed to locate some lines that were not imported.

The lines are:

:61:1410311031D22,50NSC-SW000000//SC-SW :86:M04
000000
:62F:C141031USD746743,53
:64:C141031USD746743,53 )]~

Is there a problem with lines starting with " :"?

[JoeM]

Is there a problem with lines starting with " :"?

I am guessing that will not fly well with the SQL command you are building.
Try creating a test file of just those bad records, then temporarily comment out this line:

DoCmd.RunSQL sqlsta

and replace it with this:

MsgBox sqlsta

This will return a message box that will show you exactly what it is trying to run. I think you will see that it probably won't be a valid SQL statement.