importing text files into jet with dao

[dwhawley]

i am a newbie trying to import a comma delimited text file into my jet database, but am having problems with getting my sql statement to work. i am using the following statement:

"SELECT * INTO Shipping FROM [import#txt] IN '' 'text;database=j:\dwhawley\export\beta2 dao\;FMT=Delimited;HDR=Yes'"

i got this statement off of microsoft's website and only understand a portion of it.

does anyone know what i am doing wrong?

[Mongo]

It's not that you're necessarily doing anything wrong... what you've quoted is part of a known text IISAM driver "feature" (er, how do they say?) It is "by design."

The base sample code is:


Private Sub Command1_Click ()
Dim db As Database
Set db = CurrentDB ' Access only
Set db = DBEngine(0).OpenDatabase("biblio.mdb") ' Visual Basic
db.Execute "SELECT * INTO TestImport FROM [test#txt] IN '' " _
"'text;database=c:\;FMT=Delimited;HDR=Yes'"
db.Close
End Sub

It shows how when importing a delimited text file into a Jet database, the leading spaces get truncated from fields that are not delimited by a text qualifier character. This example demonstrates this, and the party-line is: ensure you use quotes or another text qualifier around fields where you need to preserve leading spaces in your data.

[dwhawley]

thanks for your help. i eventually got things to work last night at the cost of a couple of advil. you wouldn't happen to know of a good reference for using SQL in VB would you? now that i have accomplished importing/exporting to/from text files, i need to figure out the best way to seperate items in a text file into various tables within a jet database. to be honest, i don't even really know where to start.

thanks again.

[JHausmann]

Bear in mind, the SQL you are using is non-standard and may not work with non-jet engines...

[Mongo]

Good SQL? *smirk* I'll swear if you lock any two remotely seasoned code cutters in a closet, you'll get not less than three completely different SQL recommendations. Especially when it comes to VB and the pros/cons of ADO vs RDO connecting to who from where, yada, yada, yada. If you're just starting out & you know the bulk of your intended work will be for stand-alone single user database type apps, look at "Access 97 Power Programming" by F. Scott Barker (Que). If you intend growing into the snooty RDBMS stuff, start with "Sybase System 11 Unleashed" (Sorry, I keep my copy at my customer site, so I can't rattle off the Author & Publisher's names). While the particulars of the internal function names are different in Sybase, the latter has some great guidance about database design and query optimization. But for a real quick start go to www.smithvoice.com Should I ever decide to grow-up, I think I'd be more like Robert Smith.