query to import text file into database
query to import text file into database
query to import text file into database
query to import text file into database
query to import text file into database
query to import text file into database query to import text file into database query to import text file into database query to import text file into database query to import text file into database query to import text file into database query to import text file into database query to import text file into database
query to import text file into database query to import text file into database
query to import text file into database
Go Back  Xtreme Visual Basic Talk > > > query to import text file into database


Reply
 
Thread Tools Display Modes
  #1  
Old 02-27-2017, 11:22 AM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 285
Default query to import text file into database


Hi, I have an (access 97!) DB, obs.mdb with one empty table obsns and a delimited text file lotsofobs.txt. I have set up a schema.ini file which I believe to be OK. All three files (obs.mdb,lotsofobs.txt and schema.ini are in the same folder = app.path & "\backup").
My only problem is writing the proper sql query to transfer the data from the text file into the existing empty table. I am using DAO.
One cause of confusion on my side might be that several examples I have seen feature frequent VB6 line returns (underscore/ampersands) which when coupled with the internal ampersands to keep variables separate from the rest of the SQL query makes for very confusing reading! So a little request: if anyone has a suggesion could it be on one long line please?
Many thanks.
Reply With Quote
  #2  
Old 03-07-2017, 07:23 AM
TheRealTinTin's Avatar
TheRealTinTin TheRealTinTin is offline
Junior Contributor
 
Join Date: Nov 2008
Location: Glasgow, UK
Posts: 382
Default

SQL is extremely easy to use and inserting data, regardless of underscores and ampersands, is very simple:
Code:
INSERT INTO TableName (Field1, Field2, Field3) VALUES ("Value1", "Value2", Value3);
* The last value is a numerical value so does not need the quotation marks.

As you're using DAO however, you can interface with the database "directly" without using SQL
Code:
    Dim dbWorkspace As DAO.Workspace
    Dim dbEngine As DAO.dbEngine
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set dbEngine = CreateObject("DAO.DBEngine.36")
    Set dbWorkspace = dbEngine.Workspaces(0)
    
    ' For unprotected databases.
    Set db = dbWorkspace.OpenDatabase("DatabasePath", False, False)
    
    ' For password protected databases.
    Set db = dbWorkspace.OpenDatabase("DatabasePath", False, False, "MS Access;PWD=password")
    
    ' Open recordset (table).
    Set rs = db.OpenRecordset("TableName", dbOpenDynaset, dbInconsistent, dbOptimistic)
    
    With rs
        ' Navigate to a new blank record.
        .AddNew
        
            ' Populate the record. Numerical values do not need to be surrounded by quotation marks.
            !Field1 = "Value1"
            !Field2 = "Value2"
            !Field3 = 3
        
        ' Commit (save) the record.
        .Update
        
        ' Close the recordset (table).
        .Close
    End With
    
    ' Close the database.
    db.Close
    
    ' Close the workspace.
    dbWorkspace.Close
    
    ' Clear memory references preventing memory leaks.
    Set rs = Nothing
    Set db = Nothing
    Set dbWorkspace = Nothing
    Set dbEngine = Nothing
__________________
Artificial Intelligence is no match for natural stupidity
Reply With Quote
  #3  
Old 03-08-2017, 12:57 PM
starmanMike starmanMike is offline
Junior Contributor
 
Join Date: Oct 2005
Location: near Norwich, UK
Posts: 285
Default

Hi, and ta. The problem isn't writing SQL or querying DBs - I can do that more or less. What is proving difficult is to import data from a delimited text file to a DB, and that's where the SQL difficulty comes from. A typical version I have found from a post on the net is:
(to open the DB)
Set db = OpenDatabase(App.Path, False, False, _
"Text;Database=" & App.Path & ";table=" & _
TABLE_NAME)

And the actual query is:
query = "SELECT * INTO PeopleTable IN '" & _
db_file & "' FROM " & TABLE_NAME

Obviously I replaced his variables, etc., with my own but the problem is, all the files are not in app.path but in a subfolder called 'backup' and I am not sure (because of all the single/double quotes, ampersands and whatnot) how to replace that simple app.path with
app.path & "\backup" - or can I, for this routine, define app.path like this?
app.path = app.path & "\backup"
The problem isn't with any of the DAO, but only with the SQL syntax. I have a schema.ini file, located like the DB file and the text file in the 'backup' subfolder.
Thanks for your help.
Reply With Quote
  #4  
Old 03-13-2017, 06:51 AM
TheRealTinTin's Avatar
TheRealTinTin TheRealTinTin is offline
Junior Contributor
 
Join Date: Nov 2008
Location: Glasgow, UK
Posts: 382
Default

Hi starmanMike,

Apologies for the delay I never got a notification for some reason.

The syntax you're using I wouldn't say is great. You should handle data in a structured manner, similar to the method I've used above. It will perform the same task but much more robust as if there are any errors you will be able to identify them much more easily.

That said, if all you're looking to do is correctly enter the path into your code you can use double quotes within a string i.e.
Code:
strpath = """" & App.Path & "\backup"""
This would give something like: "C:\Users\User\Documents\App\bin\backup" (including the quotation marks).
__________________
Artificial Intelligence is no match for natural stupidity
Reply With Quote
Reply

Tags
file, query, text, schema.ini, line, sql, empty, table, underscore/ampersands, returns, coupled, ampersands, variables, import, internal, feature, examples, frequent, separate, vb6, suggesion, makes, confusing, reading, request


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
query to import text file into database
query to import text file into database
query to import text file into database query to import text file into database
query to import text file into database
query to import text file into database
query to import text file into database query to import text file into database query to import text file into database query to import text file into database query to import text file into database query to import text file into database query to import text file into database
query to import text file into database
query to import text file into database
 
query to import text file into database
query to import text file into database
 
-->