IN 'mytextfile.txt' syntax - access

Question

i would like to use this syntax to update a table in access based on data from a txtfile.

fenton in his comments on this answer: read text file line by line and insert/update values in table

said that this is possible and i would like to see the exact syntax please

score 3 · Accepted Answer · edited May 23 '17 at 12:01

3

There are a number of examples in Stackoverflow, eg Slow MSAccess disk writing

strSQL="INSERT INTO tableX ( Name1,Name2 ) " _
& "SELECT Name1,Name2 " _
& "FROM [ltd.txt] IN '' [Text;Database=c:\docs\;HDR=YES;]"

You can also refer to a file by using the connection string that would be used in a linked table:

strSQL = "SELECT SomeField " _
& "FROM [Text;HDR=YES;FMT=Delimited;IMEX=2;DATABASE=C:\SomeDir\].MyTextFile.csv"

edited May 23 '17 at 12:01

Community

1
1

answered Jun 07 '10 at 20:17

Fionnuala

90,370
7
114
152

remou how come you never ask any questions? is it an ego thing? – Alex Gordon Jun 07 '10 at 20:33
1

I for one don't ask questions on SO because the quality of answer is so low. The questions I need answered are much better asked in forums frequented by experienced professional Access developers. Most of the people on SO don't know a goddamned thing that is useful about Access as a development platform. They don't even know the difference between Access and Jet/ACE. – David-W-Fenton Jun 08 '10 at 20:40
remou and handsup know a lot, also you are d fenton why would u have questions about access at all? – Alex Gordon Jun 08 '10 at 21:11
I have questions about Access all the time, but they are usually complex enough to not get good answers on SO. The people on SO who can answer the kind of questions I have are reading in the other more sophisticated Access-specific forums where I post, and I also then get the help from all the other experienced Access developers there. The participants on SO tend not to know much about Access as development tool, which is where I most often encounter issues that I need help with. – David-W-Fenton Jun 09 '10 at 22:11

score 1 · Answer 2 · answered Jun 07 '10 at 20:17

1

Open "TESTFILE.TXT" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
  Line Input #1, TextLine ' Read line into variable.
  'Parse string into individual fields
  'Execute insert/update SQL statement
Loop
Close #1 ' Close file.

answered Jun 07 '10 at 20:17

BenV

12,052
13
64
92

1

This doesn't answer the question -- it's an entirely different solution. – David-W-Fenton Jun 08 '10 at 20:41

IN 'mytextfile.txt' syntax - access

2 Answers2

Linked