create .ini file export from query

Question

I have a query, that has 2 fields.  Field 1 is a list of server names.  Field 2 is a concatenation of fields and characters (_ and []), that make up a group name.  There are multiple servers with the same group name.  I want to export this query to a file in an ini format

[group name 1]
server1
server2

[group name 2]
server1
server3

I tried to create a report, this works but the blank lines are erratic when I export it to a text file.  Sometimes theres a blank line below the group, sometimes there's not, sometimes there's several lines between the servers sometimes there's not.  It looks fine in the report but the export doesn't work.  I need it correct to import into another system.  In the report, I moved the group name field down to create a space above it and there are no spaces below.  In the server name section, there's no space above or below.  Right now I'm trying manually export to the text file, but in the future I'll use a macro and a button.  Any suggestions, and is a report export the best option?  Could this just be done with VBA somehow?

Answer 1

Hi Carl,

When you do the export to text, are you using an export specification? Not sure if it will help but you might give it a try.

Otherwise, you should be able to use VBA to loop through the query result and use the Open statement to create the ini file and write each line into the text file using Put or Write.

Hope it helps...

Answer 2

Right now, I'm right clicking on the report, and selecting Export, then Text File.  I then asks about encoding.  I've tried all 4 options, none do anything different.

I'd like to do the VBA option, but I just can't figure out how I'd do it.  I'm sure I'd have to enter all the data into an array, then read each line, split it, then take object(1) write line, then look for each line that has object(1) in it and then write line object(0).  But I can't figure out how I'd ensure that I don't duplicate that for every server lower in the list that has it.

any chance you have an example you could point me to?

Answer 3

Hi,

I have written articles on how to read text files in VBA but haven't had a chance to publish one for writing to text files. Here's a quick example of writing a simple text file in VBA.

Open "C:\FolderName\FileName.txt" For Output As #1
Print #1, "Hello World!"
Close #1

I imagine you'll need to create a query where the recordset will result in the exact order you need to write to the file, so all you have to do is loop through the recordset and write each row to the text file.

Hope it helps...

Answer 4

Thanks.  I don't have too much trouble writing text files.  It's looping thru the data that's the issue here.  Or maybe it's creating the query to begin with.  Right now, each line is server name, group name.  If I could figure out how to create the query to be group name on one line, then each server name under the next, that would solve the issue.  I could easily write that to a file.

Answer 5

Okay, can you show us the data, so we can help you write the query? How is the table structured? Do you know how to work with recordsets already?

Answer 6

the query is comprised of several tables.  Below is the SQL code for the existing query.  This query shows the name of the server and concatenates info from several tables to make up the group name.

SELECT tblServerNames.ServerName, "[" & [SPK] & "_" & [ComponentName] & "_" & [Environment] & "_" & [tblCompServerTypes].[ServerType] & "]" AS HostGroupName
FROM tblIntakeInformation, (tblComponents INNER JOIN tblCompServerTypes ON tblComponents.ID = tblCompServerTypes.Component) INNER JOIN (tblServerNames INNER JOIN tblServerComponentJunction ON tblServerNames.ID = tblServerComponentJunction.Server) ON tblCompServerTypes.ID = tblServerComponentJunction.ServerType;

I've only worked with recordsets once or twice.  Usually if I need to do something like that I use SQL statements.  I've worked with arrays a few times in VBScript, which is what I thought might be the answer here, but maybe not.

Answer 7

Hi Carl,

I am not saying Array won't work. There's always more than one way to do something in VBA. If you're more comfortable with using arrays, you should probably try it first. How are you planning to populate the array?

I am not sure the query you posted will help me to tell you how to modify it to get what you want without seeing or understanding the table structure. Can you post the table structure next? Thanks.

Answer 8

Not entirely sure what you mean by table structure.  Are you referreing to the relationships?

Answer 9

I pasted a picture in that last post, but it didn't post the picture.  I'm doing it differently here.

Answer 10

Hi,

Thank you for posting the image. What I'm trying to do is understand your database design, so I can try to offer a query to get the result you need for the code. The image you posted helps but may not be enough for me to know the result you need.

So now, can you post some sample data from each table and tell us how you want them to show up in the query?

Thanks.

Answer 11

I figured it out.  After work I kept thinking about it and realized that I've done similar things like this before with VBScript.  It's basically comparing 2 files, or it can be.  So what I did was to use your idea of a recordset.  I had an Access script where I used one before and modeled this after it.  First I ran a DISTINCT query on the GroupHostName column of the query, posted above.  Write the line to the file, then I ran the query (select *).  And compared the 2 GroupHostName columns, if equal, then write the line with the ServerName.  So a loop within a loop.  It works perfectly.  I'm posting the code here for anyone who could use it.  FYI, the first 3 lines of the code is what I put in all my scripts.  So something may not be required for this script (ie. ForAppending) but that's why it's there.

Const ForReading = 1, ForWriting = 2, ForAppending = 8, OverwriteExisting = True
Set WshShell = CreateObject("Wscript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
DIM WshShell, fso, rs1, rs2, txtFile

If Not (fso.folderExists("c:\Temp\")) Then
    fso.CreateFolder ("c:\Temp\")
End If

If Not (fso.FileExists("c:\Temp\Ansible")) Then
    fso.CreateTextFile ("c:\Temp\Ansible.ini")
End If

Set txtFile = fso.OpenTextFile("c:\Temp\Ansible.ini", ForWriting, True)

Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM qryAnsibleInventoryExport;")
Set rs2 = CurrentDb.OpenRecordset("SELECT DISTINCT qryAnsibleInventoryExport.[HostGroupName] FROM qryAnsibleInventoryExport;")
If Not (rs2.EOF And rs1.BOF) Then
    rs2.MoveFirst
    Do Until rs2.EOF = True
        txtFile.writeline rs2.HostGroupName
            If Not (rs1.EOF And rs1.BOF) Then
                rs1.MoveFirst
                Do Until rs1.EOF = True
                    If rs1.HostGroupName = rs2.HostGroupName Then
                        txtFile.writeline rs1.ServerName
                    End If
                    rs1.MoveNext
                Loop
            End If
        rs2.MoveNext
        txtFile.writeline
    Loop
End If

Answer 12

Hi Carl,

Congratulations! Glad to hear you got it sorted out. Good luck with your project.