I am facing problem in importing text file to Access database using python.
I know that transferText method of MS Access is used for this, but not sure
if it works with python.
There are two issues here.
A) TransferText is convenient when you're working in Access but isn't
the only way of importing text to an Access/Jet database. If you're
working from outside Access it's usually simpler to use the DAO library
to instantiate a Jet database engine and execute a SQL append or
make-table query.
B) I've never used Python but assume it has a module or library that
lets you use OLE automation. The Perl equivalent is the Win32::0LE
module.
Below are two code snippets that should help you get started. The first
is Perl and simply executes any SQL statement it's given, including
CREATE TABLE and so on if you want full control of the table structure.
The second is VBScript and builds the query before executing it, making
it an append or make table query as required. This one also shows the
SQL syntax for accessing text files.
Unless the text file is plain CSV you'll need to provide a schema.ini
file with details of field names and types: links to documentation are
at the end of this message.
#Sample Perl code to execute a SQL statement against
#a Jet database (mdb file)
use strict;
use Win32::OLE;
my $Jet; #DAO.DatabaseEngine
my $DB ; #DAO.Database
my $SQLquery = "INSERT INTO Details (SaleID, Comment)
VALUES (11, 'Test value from Perl');";
$Jet = Win32::OLE->CreateObject('DAO.DBEngine.36')
or die 'Can't create Jet database engine.';
$DB = $Jet->OpenDatabase('C:\\Temp1\\BoxWithinBox_Backup.mdb');
$DB->Execute($SQLquery, 128); #128=DBFailOnError
$DB->Close;
'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening Access
'Modify DB_NAME, TBL_NAME, DATA_SOURCE as required
'and the code that builds strSQL as necessary.
'If TBL_NAME exists, appends to it; otherwise creates it.
Option Explicit
Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim blTExists 'As Boolean
Dim strSQL 'As String
Const DB_NAME = "C:\Temp\Test 2003.mdb"
Const TBL_NAME = "My_Table"
Const DATA_SOURCE = "[Text;HDR=Yes;Database=C:\Temp\;].B1#txt"
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)
For Each oTDef In oDB.TableDefs
If oTDef.Name = TBL_NAME Then
blTExists = True
Exit For
End If
Next
If blTExists Then
strSQL = "INSERT INTO " & TBL_NAME _
& " SELECT * FROM " & DATA_SOURCE & ";"
Else
strSQL = "SELECT * INTO " & TBL_NAME _
& " FROM " & DATA_SOURCE & ";"
End If
oDB.Execute strSQL
oDB.Close
-------------
SCHEMA.INI
Documentation is towards the end of the Help topic "Initializing the
Text Data Source Driver" (under Microsoft Jet SQL Reference). These
links may also be useful:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp
Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
http://www.devx.com/tips/Tip/12566
Access will create a schema.ini file automatically if you export to
Microsoft Word Merge format or if you use a make-table query to export
to a text file, e.g.
SELECT * INTO [text;database=C:\temp].[mytable#txt] FROM mytable;
