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()
|
Comments
0 comments
Article is closed for comments.