How to import all tables in Microsoft Access database file (.accdb) – Revvity Signals

Problem:

A single Microsoft Access database (.accdb) file can contain multiple tables. How can I import all of the tables as seperate data table into Spotfire at once?

By the way, selecting all of the tables in the import dialog did not help as this seems to merge the tables into one.

Resolution:

The following IronPython script will import all tables in a single accdb file.

You will need to create two document properties (in bold) in your analysis and change the value in accordance with the accdb file to be imported.

FilePath: The full file path of the accdb file without the file name/file extension.

FileName: Th name of the accdb file without the file extension.

You will also need to install the Microsoft Access Database Driver if it has not been installed already. Change the version number of Microsoft.ACE.OLEDB (in red) to match the version of the driver installed. This script was created using version 2010 (https://www.microsoft.com/en-us/download/details.aspx?id=13255).

import clr

import System

clr.AddReference("System.Data")

from System.Data import DataSet

from System.Data.OleDb import OleDbConnection, OleDbDataAdapter, OleDbCommand

from System.Data import CommandType

from Spotfire.Dxp.Data import *

from System.IO import FileStream, FileMode, File, MemoryStream, SeekOrigin, StreamWriter

import System.String

from Spotfire.Dxp.Data.Import import TextDataReaderSettings

from Spotfire.Dxp.Data.Import import TextFileDataSource

# Function to load the CSV file

def LoadCSV(dataTableName, stream):

settings = TextDataReaderSettings()

settings.Separator = ","

settings.AddColumnNameRow(0)

settings.ClearDataTypes(False)

#settings.SetDataType(0, DataType.Integer)

stream.Seek(0, SeekOrigin.Begin)

fs = TextFileDataSource(stream, settings)

if Document.Data.Tables.Contains(dataTableName):

Document.Data.Tables[dataTableName].ReplaceData(fs)

else:

Document.Data.Tables.Add(dataTableName, fs)

FilePath = Document.Properties["FilePath"]

FileName = Document.Properties["FileName"] + ".accdb"

FullPath = FilePath + "\\" + FileName

conStr = r'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + FullPath + ";"

con = OleDbConnection(conStr)

con.Open()

MyTable = con.GetSchema("Tables").Select("TABLE_TYPE = 'TABLE'")

for t in MyTable:

TargetTable = t["TABLE_NAME"].ToString()

query = "SELECT * FROM " + TargetTable

adapter = OleDbDataAdapter(query, con)

ds = DataSet()

adapter.Fill(ds)

stream = MemoryStream()

csvWriter = StreamWriter(stream)#, Encoding.UTF8)

ColNamLine = ', '.join(str(x) for x in ds.Tables[0].Columns) + "\r\n"

csvWriter.WriteLine(ColNamLine)

for row in ds.Tables[0].Rows:

TargetLine = ', '.join(str(x) for x in row) + "\r\n"

csvWriter.WriteLine(TargetLine)

csvWriter.Flush()

LoadCSV(TargetTable, stream)

con.Close()

Related articles