How to import a txt file in MS Access with VBA

[jdraw]

I just ran this code with spec SachExample
In Sample2 I modified dates to YMD format in the sample2.txt

Sub MyTableImport()
DoCmd.TransferText acImportDelim, "SachExample", "MyTesTTable", "C:\users\jp\documents\documents_Lenovo\Documents\Sample2.txt", True
End Sub

This the specification:



OutPut
MyTesTTable MyTesTTable

pername	perCity	perstate	perDoB
John	Miami	FL	21-Apr-98​
Bob	Chicago	IL	15-Nov-92​
Fred	Toronto	ON	30-Aug-01​

 

[SachAccess]

Can you post your sample.txt file --at least a few records?

My sample was delimited with |. I'd also like to see the design of Table1.
Update1
Just saw Doc's comment ---he's probably right about the trim with Dates.
Give me a few records and we'll get it working.

But it worked with my sample (my regional setting has DD-MMM-YY)
SachTable1 SachTable1

PersName	PersCity	PersState	PersDoB
John	Miami	FL	21-Apr-98​
Bob	Chicago	IL	15-Nov-92​
Fred	Toronto	ON	30-Aug-01​

Can you post your sample.txt file --at least a few records?

My sample was delimited with |. I'd also like to see the design of Table1.
Update1
Just saw Doc's comment ---he's probably right about the trim with Dates.
Give me a few records and we'll get it working.

But it worked with my sample (my regional setting has DD-MMM-YY)
SachTable1 SachTable1

PersName	PersCity	PersState	PersDoB
John	Miami	FL	21-Apr-98​
Bob	Chicago	IL	15-Nov-92​
Fred	Toronto	ON	30-Aug-01​

Sorry for late reply.

 

[SachAccess]

Typically, "Data type conversion error" means you are trying to use something that is the wrong data type for the field in question. If you get to the line in question and J is 4, it points to a date field. I don't think Trim works correctly with date fields because dates are internally stored as numbers. Only the displayed date is text-oriented.

Thanks for the help. Have a nice day ahead.

 

[jdraw]

??Utilitatis|causa|amicitia|est|quaesita??
From Google translate ??Friendship is sought for the sake of advantage??

Repeated lines---is this your real data?