Solved: Export MSI Table to Excel

[stanl]

The Windows Installer can be used to create and maintain SQL database tables, and has an Export() method. The exported file is tab-delimited with the first 2 lines containing the field names and field types.

You can give the Export file an extension of .xls, but then have to manually import and declare data types.

I'm wondering if anyone has suggestions how to automate this entire process?

TIA - Stan

[stanl]

maybe a sample file output will help. In the attached zip is Zipcodes.idt, which contains an MSI export() for zipcodes in the state of North Carolina. This file could be exported as Zipcodes.xls, but the 2nd and 3rd lines need to be removed and the code field assigned as 'Text' (for if I created a file for zipcodes in New England, the leading zeroes would be dropped).

So, I can create the output via OLE using CreateObject("WindowsInstaller.Installer"), and assume I assigned .xls as the extension on the output file, I would like to continue with CreateObject("Excel.Application") and finish the formatting...

Stan

[mvidas]

Hi Stan,

Quick question.. is this VB/VBA, or is it VBS? Some of the methods I might use depend on which language I'm writing it for.
If you're unsure, on your Dim statements, can you use "Dim xlApp As Object" or do you have to just use "Dim xlApp"?

Matt

[mvidas]

Ok, to get a working solution for you I assumed it was not VBS (as I've never used the windows installer). If it is, let me know, only a few changes needed:

[vba]Sub StanFormatFile()
Dim vFile As String, FCont() As String, xlApp As Object
vFile = "C:\ZipCodes.idt"
FCont = LoadTextFile(vFile)
Set xlApp = CreateObject("excel.application")
With xlApp.Workbooks.Add(1)
With .Sheets(1)
.Columns(1).NumberFormat = "@"
.Range("A1").Resize(UBound(FCont, 2) + 1, UBound(FCont, 1) + 1).Value = _
Application.Transpose(FCont)
.Columns.AutoFit
End With
xlApp.DisplayAlerts = False
.SaveAs "C:\ZipCodes.xls"
xlApp.DisplayAlerts = True
.Close False
End With
xlApp.Quit
Set xlApp = Nothing
End Sub
Function LoadTextFile(ByVal vFile As String, Optional ByVal vDelim As String _
= "") As String()
Dim Cnt As Long, FileData() As String, TempStr As String, vFF As Long
Dim TempArr() As String, iUB As Long, i As Long
LineCnt = 0
If Len(vDelim) = 0 Then vDelim = Chr(9)
Cnt = 0
vFF = FreeFile
Open vFile For Input As #vFF
Line Input #vFF, TempStr
TempArr = Split(TempStr, vDelim)
iUB = UBound(TempArr) 'use line 1 to get column count
ReDim FileData(iUB, Cnt)
For i = 0 To iUB
FileData(i, Cnt) = TempArr(i)
Next
Cnt = Cnt + 1
Line Input #vFF, TempStr 'read line 2 into nothingness
Line Input #vFF, TempStr 'read line 3 into nothingness
Do Until EOF(vFF)
Line Input #vFF, TempStr
TempArr = Split(TempStr, vDelim)
ReDim Preserve FileData(iUB, Cnt)
For i = 0 To iUB
FileData(i, Cnt) = TempArr(i)
Next
Cnt = Cnt + 1
Loop
Close #vFF
LoadTextFile = FileData
End Function[/vba]Matt

[stanl]

Thanks, but assume file was exported as zipcodes.xls (only in the same format as the .idt file, viz. rename the idt file). Then open it in Excel?

Stan

[mvidas]

[vba]' vFile = "C:\ZipCodes.idt"
vFile = "C:\ZipCodes.xls" [/vba]

If it is still as a tab delimited format, that change should do it

[stanl]

Ok, to get a working solution for you I assumed it was not VBS

The installer can be completely controlled with VBA/VBS; using MSI as strictly a database is an extra, but has some potential... see

http://www.jsware.net/jsware/msicode.php3

I'm going to try pre-establishing the column formats from the datatypes in the MSI, then copy and paste via the clipboard, remove rows 2-3 and adjust column widths, then compare against your code (which is probably faster).

Thanks again,

Stan

[mvidas]

(that page bookmarked - thanks!)
Here is what you would use in vbscript to accomplish the same thing. Since your tab-delimited file would use the same name as your desired excel file (I have C:\ZipCodes.xls in my code), you only have to change it once to what you're using:[vba]Sub StanFormatFile()
Dim vFile, FCont, xlApp
vFile = "C:\ZipCodes.xls"
FCont = LoadTextFile(vFile)
Set xlApp = CreateObject("excel.application")
With xlApp.Workbooks.Add(1)
With .Sheets(1)
.Columns(1).NumberFormat = "@"
.Range("A1").Resize(UBound(FCont, 2) + 1, UBound(FCont, 1) + 1).Value = _
Application.Transpose(FCont)
.Columns.AutoFit
End With
xlApp.DisplayAlerts = False
.SaveAs vFile
xlApp.DisplayAlerts = True
.Close False
End With
xlApp.Quit
Set xlApp = Nothing
Set FCont = Nothing
Set vFile = Nothing
End Sub
Function LoadTextFile(ByVal vFile)
Dim Cnt, FileData, TempStr, FSO, TS, TempArr, iUB, i, vDelim
LineCnt = 0
vDelim = Chr(9)
Cnt = 0
Set FSO = CreateObject("scripting.filesystemobject")
Set TS = FSO.OpenTextFile(vFile)
TempStr = TS.ReadLine
TempArr = Split(TempStr, vDelim)
iUB = UBound(TempArr) 'use line 1 to get column count
ReDim FileData(iUB, Cnt)
For i = 0 To iUB
FileData(i, Cnt) = TempArr(i)
Next
Cnt = Cnt + 1
TempStr = TS.ReadLine 'read line 2 into nothingness
TempStr = TS.ReadLine 'read line 3 into nothingness
Do Until TS.AtEndOfStream
TempStr = TS.ReadLine
TempArr = Split(TempStr, vDelim)
ReDim Preserve FileData(iUB, Cnt)
For i = 0 To iUB
FileData(i, Cnt) = TempArr(i)
Next
Cnt = Cnt + 1
Loop
TS.Close
LoadTextFile = FileData
Set Cnt = Nothing
Set FileData = Nothing
Set TempStr = Nothing
Set TS = Nothing
Set FSO = Nothing
Set TempArr = Nothing
Set iUB = Nothing
Set i = Nothing
Set vDelim = Nothing
End Function[/vba]By the way, on your sample NC zip codes file, this took about 4 seconds.

Matt