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

Question

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

You could simply omit the WHERE clause, and do a DELETE * FROM NewTable WHERE field4 = '0' OR field5 = '0' after the import. — Andre
– Andre, Commented Sep 4, 2015 at 6:14
Please do not use setwarnings it is unsafe and system wide. Use Execute against a databas instance instead. — Fionnuala
– Fionnuala, Commented Sep 4, 2015 at 9:15
You may need to use a schema.ini file if you have non-standard delimters stackoverflow.com/questions/12137085/… — Fionnuala
– Fionnuala, Commented Sep 4, 2015 at 9:19
@Fionnuala - According. I implemented a new version. Follow: code Dim sqlStr As String Dim db As DAO.Database db.Execute sqlStr, dbFailOnError db.Close` — Ralph MacLand
– Ralph MacLand, Commented Sep 5, 2015 at 20:19

Community · Accepted Answer · 2017-05-23 10:29:26Z

2

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:

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'));

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Sep 4, 2015 at 11:42

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ralph MacLand Over a year ago

Perfect @HansUp / @Fionnuala / @Andre451. I put one more small piece of code to get the text file from within the own project. Follows: code Dim strFolder As String strFolder = CurrentProject.Path sqlStr = sqlStr & " FROM [Text;HDR=No;FMT=Delimited;Database=" & strFolder & "].Teste.txt AS t"

L42 Over a year ago

@HansUp I applied your approach in one of my projects but I get inconsistent results. Though I didn't use a text file as source but an xlsb Excel file I don't think that should be a problem (this is where I might be wrong). Appreciate if you can check it out here, I just want to understand why it gives inconsistent results and hear your thoughts about it.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related