Easy export to TXT formatted

[ExcelwolfAU]

Super frustrated

Ordinarily i would just do an XML export for this - but i cant because the software we are using uses a file format other than XML, and I really dont want to have to cut and paste or strip headers out of generated files.

So... I need to port over data from columns ("Location"), ("Model"), ("Manufacturer"), ("Address"), ("In Use"), ("Physical Form") into the following format:

«Table» {
         Location<"Location">
         Model<"Model">
         Manufacturer<"Manufacturer">
         In_Use<"yes">
         Address<"Address">
         Physical_Form<"Physical Form">
     }
        {
         Location<"Location">
         Model<"Model">
         Manufacturer<"Manufacturer">
         In_Use<"yes">
         Address<"Address">
         Physical_Form<"Physical Form">
     }

<<Record 1>>

I would ideally like to do this as an excel VBA - but my VBA skills are junk - hopefully someone has some clever way to do this in the above format.

Cheers

 

[diddi]

hi and welcome to MrExcel.
what does a record in the source file look like? and what exactly will the output file look like. (insert sample data to both pls)

 

[ExcelwolfAU]

Hi

Output is exactly as specified in original - its proprietary code, so its not XML, and its not a recognisable format. Its just the values encased in quotations as the values for the variables preceeding them.

Input is an excel table with the variables as column headings.

Location	Model	Manufacturer	In_Use	Address	Physical_Form
Here	Chocolate	Johnson	No	Corner Store	Box
There	Caramel	Christopher	Yes	Supermarket	Bottle

Its just not cost effective to have team members entering values manually, so just wondering if theres a VBA way to put them into a structured text layout as in original post.

thanks

 

[diddi]

there is a simple method for writing to a text file you can experiment with...

Sub ExportText()
    Dim MyFile As String
    
    MyFile = Environ("USERPROFILE") & "\Desktop\Output.txt"
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Open MyFile For Output As #1
        Print #1, "«Table» ";
        For Row = 1 To LastRow
            Print #1, "{" & Chr(10) & "Location<" & Chr(34) & Cells(Row, 1) & Chr(34) & ">"
            Print #1, "Model<" & Chr(34) & Cells(Row, 2) & Chr(34) & ">"
            Print #1, "Manufacturer<" & Chr(34) & Cells(Row, 3) & Chr(34) & ">"
            Print #1, "In_Use<" & Chr(34) & Cells(Row, 4) & Chr(34) & ">"
            Print #1, "Address<" & Chr(34) & Cells(Row, 5) & Chr(34) & ">"
            Print #1, "Physical_Form<" & Chr(34) & Cells(Row, 6) & Chr(34) & ">"
        Next Row
    Close 1
End Sub

note without being able to see the exact hex dump of the files i cannot say whether this will actually be a readable version once imported again. there may be spaces or unprintable chars i cannot see. but it is something you can at least work off i hope

 

[ExcelwolfAU]

Hi Diddi

That was perfect - thanks very much for the assistance - i have made the necessary modifications and it works perfectly now.

Appreciate the assistance.

Cheers

 

[diddi]

glad it worked out for you