CODEWITHSUNDEEP
sqlms-accessvba
Ralph MacLand
Ralph MacLand

Reputation: 97

VBA Code - Import a text file into an Access table - With Condition

I need to import a text file into an Access table, but using import conditions in TXT file.

Below is my VBA code. It works only when you do not use the WHERE clause, because it is asking for parameter entry. I think the problem is in the missing field format specification.

Which adjustments do you recommend? Any suggestions would be appreciated.

TEXT FILE - Teste.txt

1;24;433;43;5
2;436;424;43;24
3;5454;656;656;555
4;545;545;0;0
5;65465;756;0;0

My Code VBA Access:

Sub MyTableImport()

    Dim sqlStr As String

    sqlStr = "SELECT * INTO NewTable "
    sqlStr = sqlStr & " FROM [Text;HDR=Yes;FMT=Delimited;Database=C:\Temp].Teste.txt "
    sqlStr = sqlStr & " WHERE field4 <> '0' AND field5 <> '0';"

    DoCmd.SetWarnings False
    DoCmd.RunSQL sqlStr
    DoCmd.SetWarnings True

End Sub

Upvotes: 3

Views: 5513

Answers (1)

HansUp
HansUp

Reputation: 97101

I used a Schema.ini approach, as Fionnuala suggested, similar to what you had in one of the earlier versions of your question.

With your Teste.txt source file, and the Schema.ini and query below, this is the result for NewTable. All five fields are text datatype:

enter image description here

This is C:\Temp\Schema.ini:

[Teste.txt]
ColNameHeader=False
Format=Delimited(;)
Col1="field1" Text
Col2="field2" Text
Col3="field3" Text
Col4="field4" Text
Col5="field5" Text

And this is the query which created NewTable:

SELECT t.field1, t.field2, t.field3, t.field4, t.field5
INTO NewTable
FROM [Text;HDR=No;FMT=Delimited;Database=C:\Temp].[Teste.txt] AS t
WHERE (((t.field4)<>'0') AND ((t.field5)<>'0'));

Upvotes: 2

Related Questions