none
create .ini file export from query RRS feed

  • 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?

    Tuesday, September 25, 2018 5:59 PM

Answers

  • 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




    • Marked as answer by Carl_S_S Wednesday, September 26, 2018 2:43 PM
    • Edited by Carl_S_S Wednesday, September 26, 2018 2:44 PM
    Wednesday, September 26, 2018 2:37 PM

All replies

  • 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...

    Tuesday, September 25, 2018 6:24 PM
  • 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?
    Tuesday, September 25, 2018 7:09 PM
  • 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...

    Tuesday, September 25, 2018 7:23 PM
  • 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.
    Tuesday, September 25, 2018 7:29 PM
  • 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?
    Tuesday, September 25, 2018 7:36 PM
  • 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.

    Tuesday, September 25, 2018 7:58 PM
  • 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.

    Tuesday, September 25, 2018 8:08 PM
  • Not entirely sure what you mean by table structure.  Are you referreing to the relationships?


    Tuesday, September 25, 2018 8:22 PM
  • I pasted a picture in that last post, but it didn't post the picture.  I'm doing it differently here.
    Tuesday, September 25, 2018 8:27 PM
  • 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.

    Tuesday, September 25, 2018 9:09 PM
  • 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




    • Marked as answer by Carl_S_S Wednesday, September 26, 2018 2:43 PM
    • Edited by Carl_S_S Wednesday, September 26, 2018 2:44 PM
    Wednesday, September 26, 2018 2:37 PM
  • Hi Carl,

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

    Wednesday, September 26, 2018 2:59 PM