Solved

OpenOffice macro to make text-file usable as database

Posted on 2011-03-20
3
376 Views
Last Modified: 2012-05-11
I don't know much about OpenOffice, but now I need to know a lot!

I need to use an output form one program (text file) as a database for adresses in a standard letter merge in Writer.

The output is a standard text-file containing a lot of addresses. Each address is 6 lines.

I hve to transform this six line address into something like a CSV file: 6 fields per address, semicolon as delimiter, preferrably contained in quotes - all neatly presented in one line per address.

And the solution has to be fool proof. The user, that will be using this on a monthly basis, knows just enough to folow a guide on how to do the merging from a template. So this has to be done almost automatic.

How do I solve this??
0
Comment
Question by:ellegaarddk
  • 2
3 Comments
 
LVL 23

Expert Comment

by:redrumkev
Comment Utility
ellegaarddk,

How are you doing?

Could you please post some test data in the following formats.

1. The original data in a text file as it comes from your database (change names/address numbers, etc before posting if the information is private) but please try to keep the structure the same.

2. Can you take the same data and post a file of "how you want it to be", as a before and after.

This will give me something to go on.

Thank you,
Kevin
0
 
LVL 23

Accepted Solution

by:
redrumkev earned 500 total points
Comment Utility
ellegaarddk,

Try this:

Function STRJOIN(range, Optional delimiter As String, Optional before As String, Optional after As String)
    Dim row, col As Integer
    Dim result, cell As String

    result = ""

    If IsMissing(delimiter) Then
        delimiter = ";"
    End If
    If IsMissing(before) Then
        before = ""
    End If
    If IsMissing(after) Then
        after = ""
    End If

    If NOT IsMissing(range) Then
        If NOT IsArray(range) Then
            result = before & range & after
        Else
            For row = LBound(range, 1) To UBound(range, 1)
                For col = LBound(range, 2) To UBound(range, 2)
                    cell = range(row, col)
                    If cell <> 0 AND Len(Trim(cell)) <> 0 Then
                        If result <> "" Then
                            result = result & delimiter
                        End If
                        result = result & before & range(row, col) & after
                    End If
                Next
            Next
        End If
    End If

    STRJOIN = result
End Function

Open in new window


If your data is cell A1:A300 for example and you do no have a header, then you can have this count rows 1-6, join those, then start counting again 7-12 (second address), 13-18 (third address).

If you have a header, then we start at A2, so rows 2-7 (1st address), 8-13 (second), 14-19 (third and so on..).

If you have a header and a space between each, then we are going to want to count like this:

Start at 2 (header is 1) then run 2-7, row 8 is a space, so 9-14, with 15 for a space. This could be an array (1-6) that adds 7 to the base number of 2 after each loop until the end of your range.

Kevin
0
 

Author Closing Comment

by:ellegaarddk
Comment Utility
Sorry, solved the problem in a cmpletely different way - and totally forgot about the question
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now