Base DB Connections

Mark_LaPierre · July 7, 2013, 7:42pm

I set up mySQL on my machine and set up a BASE connected to that sql server. I can open the table I created in the mySQL DB inside the BASE session.

Is there now a way to open a connection, or paste in a new table, that is not contained in the mySQL database?

I have data that lives in a CALC spreadsheet that I want to import into the mySQL database. I can't do that if I can't have access to both tables at the same time.

Any suggestions on how to proceed from here? Do I just copy and paste the data from the spreadsheet directly into the mySQL table?

iplaw67 · July 8, 2013, 6:45am

Hi Mark,

Any suggestions on how to proceed from here? Do I just copy and paste
the data from the spreadsheet directly into the mySQL table?

You could try this :

1) Open your ODB file, click on the Tables icon so that you can see the
list of tables in your mysqldb.

2) Now open your Calc spreaadsheet.

3) In the top left hand corner of your spreadsheet, click on the on the
grey corner square/rectangle so that the whole sheet gets selected.

4) Now drag and drop the whole sheet to the space where your mysql table
list is visible within the ODB container and the copy data assistant
should start up. Bear in mind that the assistant doesn't always
recognize and set field types correctly when converting from Calc to
Base, so you will have to check this before validating the creation of
the table, otherwise you may end up with truncated data in your table or
some funky odd stuff. The most problematic ones seem to be date/datetime
strings. If you have images pasted in your spreadsheet cells as part of
your data set, these will not be converted to binary large objects by
the assistant.

HTH,

Alex

Mark_LaPierre · July 9, 2013, 4:54am

Okay, that worked pretty well. It's still not a good solution because, to make it work with the multitude of spreadsheets that I have, I have to import each one by dragging and dropping then spending several minutes defining all the data types to match the internal table that I want to import the data into. This would be so much easier if I could link to the external spreadsheet, import the data from it, then copy the new spreadsheet to the same name/location as the linked file, start up the LO-BASE and import that batch of data into the SQL back end.

I get a new spreadsheet every week with some duplication of data on each sheet from what was on the previous sheet. I don't want to copy and paste the duplicate data into the main table. I'll have to think about this one for a while.

Thank you for the solution Alex. It's a good start to what I hope is a workable solution.

iplaw67 · July 9, 2013, 7:37am

Hi Mark,

I get a new spreadsheet every week with some duplication of data on each
sheet from what was on the previous sheet. I don't want to copy and
paste the duplicate data into the main table. I'll have to think about
this one for a while.

You can't currently use LO to import text/spreadsheet data as a basis
for table creation with the mysql driver (any of them, whether direct,
odbc or jdbc). I don't actually know of any opensource program that does
this for mysql, all of the solutions I've seen so far, which do not use
LO, use some kind of scripting language, e.g. PHP, to import CSV, Excel,
text files (e.g. xml), or direct SQL inserts. For example, PHPMyAdmin
has such a feature (but this is version/build dependent)

Another possibility is to use the HSQLDB functionality of linking
directly to text tables. You would have to have your data as CSV in that
particular case. Of course, this would mean using the HSQLDB as an
intermediary database from which you would then copy the data into your
mysqldb, so it doesn't really solve the problem of simplifying the whole
process. Look up the HSQLDB documentation to find out how this works.

Alex

iplaw67 · July 9, 2013, 7:46am

On further reflection, it might be possible to do this by using a macro,
in which you connect to the Calc sheet, load the data array into a basic
array stored in memory, and then connect to your mysqldb and do the
updates from the stored data array. However :

- I have no idea whether this would actually work ;
- the performance might suck big time, as LO Basic does not exactly have
the greatest of memory management models.

It might possibly work better in python. If you can avoid using the UI
where possible, you will speed things up no end. Redrawing UI components
is pretty costly in terms of performance.

Alex

Marion_Noel_Lodge · July 10, 2013, 1:20am

Hi Mark,

I have written a Macro in Basic that imports Bank Statement details from a
.csv file into my Accounting system. It uses an SQL INSERT INTO statement
to write to the database Table. I run an H2 database, but because the
Insert is done using SQL, it should translate fairly readily to your
mysqldb.

I could post the code if you think it could be adapted for your situation.

Noel

Mark_LaPierre · July 10, 2013, 2:01am

Any suggestions on how to proceed from here? Do I just copy and paste
the data from the spreadsheet directly into the mySQL table?

On further reflection, it might be possible to do this by using a macro,
in which you connect to the Calc sheet, load the data array into a basic
array stored in memory, and then connect to your mysqldb and do the
updates from the stored data array. However :

- I have no idea whether this would actually work ;
- the performance might suck big time, as LO Basic does not exactly have
the greatest of memory management models.

It might possibly work better in python. If you can avoid using the UI
where possible, you will speed things up no end. Redrawing UI components
is pretty costly in terms of performance.

Alex

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be
deleted

> Hi Mark,
>
> I have written a Macro in Basic that imports Bank Statement details from a
> .csv file into my Accounting system. It uses an SQL INSERT INTO statement
> to write to the database Table. I run an H2 database, but because the
> Insert is done using SQL, it should translate fairly readily to your
> mysqldb.
>
> I could post the code if you think it could be adapted for your situation.
>
> Noel

Hey Noel,

I would be interested in seeing your code. Post it to the list so others may gain from it as well.

Marion_Noel_Lodge · July 10, 2013, 1:08pm

Hi Mark,

Here is the code -

Sub BtnPrepareBankEntries()
    Dim sEntryDate AS String
    Dim sBkStat AS String
    Dim sBkStatBak AS String
    Dim aFields(150,7) AS String
    Dim sKey AS String
    Dim aWhoRef(150,7) AS String

    Dim iCount AS Integer
    Dim iFileNo AS Integer
    Dim iRec AS Integer
    Dim iFld AS Integer
    Dim iPtr AS Integer

    Dim iBSB_AcNo AS Integer
    Dim iEntryDate AS Integer
    Dim iTypeCode AS Integer
    Dim iAmount AS Integer
    Dim iEntryType AS Integer
    Dim iWho AS Integer
    Dim iWhoRef AS Integer
    Dim iKey AS Integer

    iBSB_AcNo = 1
    iEntryDate = 2
    iTypeCode = 3
    iAmount = 4
    iEntryType = 5
    iWho = 6
    iWhoRef = 7
    iKey = 0

sBkStat = "D:\Downloads\BBL.csv"
sBkStatBak = "D:\Downloads\BBL.bak"

    if not FileExists(sBkStat) then
        msgBox("File " + sBkStat + " not found. Check that you have
downloaded the month's entries from the Bank", MB_OK, "PRIOR ACTION NEEDED"
)
    else
        iCount = 0
        iRec = 0
        iFileNo = FreeFile()

        ' Read records from "D:\Downloads\BBL.csv into the array -
(aFields(150,7))
        Open sBkStat for Input as #iFileNo
        Do while NOT EOF(iFileNo)
            iRec = iRec + 1
            For iFld = 1 to 7
                Input #iFileNo, aFields(iRec,iFld)
            Next iFld
        Loop

REM1 - The following lines transform the data to suit the Accounting System.
           This is where you could delete duplicates or make data
adjustments.
        For iPtr = iRec To 1 Step -1
            aFields(iPtr,iEntryType) =
fCamelCase(fRemoveNumbers(aFields(iPtr,iEntryType),0))
            aFields(iPtr,iWho) =
fCamelCase(fRemoveNumbers(aFields(iPtr,iWho),0))
            aWhoRef(iPtr,iWhoRef) =
fCamelCase(fRemoveNumbers(aFields(iPtr,iWhoRef),2))

            Select case aFields(iPtr,iEntryType)
                Case "Debit Card Fee" :
                    aFields(iPtr,iKey) = aFields(iPtr,iEntryType)
                Case "Deposit - Cheque(s)" :
                    aFields(iPtr,iKey) = aFields(iPtr,iEntryType)
                Case "Direct Credit" :
                    aFields(iPtr,iKey) = aFields(iPtr,iWho)
                    if aFields(iPtr,iWho) = "Challenger Life" then
                        aFields(iPtr,iKey) = aFields(iPtr,iWho) + ": " +
aWhoRef(iPtr,iWhoRef)
                    End if
                    if aFields(iPtr,iWho) = "Ing Direct" then
                        aFields(iPtr,iKey) = aFields(iPtr,iWho) + " Credit"
                    End if
                Case "Direct Debit" :
                    if aFields(iPtr,iWho) = "Ing Direct" then
                        aFields(iPtr,iKey) = aFields(iPtr,iWho) + " Debit"
                    else
                        aFields(iPtr,iKey) = aFields(iPtr,iWho) + ": " +
aWhoRef(iPtr,iWhoRef)
                    End if
                Case "Interest" :
                    aFields(iPtr,iKey) = aFields(iPtr,iEntryType)
                case "Pay Anyone" :
                    if aFields(iPtr,iAmount) > 0 then
                        aFields(iPtr,iKey) = aFields(iPtr,iWho)
                    else
                        aFields(iPtr,iKey) =
fCamelCase(fRemoveNumbers(aWhoRef(iPtr,iWhoRef),0))' Remove 2 digits
remaining on left of string
                    End if
                case "Pension" :
                    aFields(iPtr,iKey) = aFields(iPtr,iWho) + ": " +
aWhoRef(iPtr,iWhoRef)
                case "Retail Purchase" :
                    aFields(iPtr,iKey) = aFields(iPtr,iWho)
                Case "Transaction Fees Charged" :
                    aFields(iPtr,iKey) = aFields(iPtr,iEntryType)
                Case "Withdrawal - Atm" :
                    aFields(iPtr,iKey) = Left("WDraw-ATM: " +
aFields(iPtr,iWho), 30)
                Case "Withdrawal - Eftpos" :
                    aFields(iPtr,iKey) = Left("WDraw-EFTPOS: " +
aFields(iPtr,iWho), 30)
                case Else :
                    aFields(iPtr,iKey) = aFields(iPtr,iWho)
               end Select

Next iPtr
REM2 - End of transformation section

        ' Add records to the BankStatments table in date order
        For iPtr = iRec To 1 Step -1
            sEntryDate = fReverseDate(aFields(iPtr,iEntryDate))
            gsSQL = "INSERT INTO BankStatements
(EntryDate,Who,Amount,EntryType,WhoRef,""BSB-A/cNo"",TypeCode,Key) " _
                   + "VALUES ('" + sEntryDate + "','" +
fProcessSingleQuotes(aFields(iPtr,iWho)) + "','" + aFields(iPtr,iAmount) +
"','" _
                   + aFields(iPtr,iEntryType) + "','" +
aFields(iPtr,iWhoRef) + "','" + aFields(iPtr,iBSB_AcNo) + "','" _
                   + aFields(iPtr,iTypeCode) + "','" +
fProcessSingleQuotes(aFields(iPtr,iKey)) + "')"
            if goStmt9.executeUpdate(gsSQL) = 0 then '= row NOT
inserted
                MsgBox("INSERT failed. Command was - '" + gsSQL + ";", 0,
"BtnPrepareBankEntries()")
                Exit Sub
            End if
            iCount = iCount + 1
        Next iPtr
        Close #iFileNo

        ' Delete backup file
        If FileExists(sBkStatBak) then
            Kill(sBkStatBak)
        else
            msgBox("File " + sBkStatBak + " not found.")
        End if

        ' Rename BBL.csv to BBL.bak
        if FileExists(sBkStat) then
            Name sBkStat As sBkStatBak
        else
             MsgBox("There was no '" + sBkStat + "' to make a new '" +
sBkStatBak + "' from.")
        End if

MsgBox("Download Completed - " + CStr(iCount) + " entries
processed")
End if
End Sub

Hope you can read this - on a wide screen, the formatting is much better!
Perhaps you could copy and paste the code into a Writer document set to
landscape, to make it more readable.

You can probably ignore all the code between REM1 and REM2. This is just
formatting to suit my requirements.

I use several functions, all starting with "f", e.g. -
fReverseDate - Puts a date into yyyy-mm-dd format.
fProcessSingleQuotes - Single quotes need to be doubled up to avoid
problems.
etc.
If you would like the code for any of these, let me know.

Hopefully this may give you ideas you can experiment with.

Noel

pitonyak · July 11, 2013, 12:22am

Very Nice!

Marion_Noel_Lodge · July 11, 2013, 4:40am

Thanks Andrew,

Much of my macro's code was built on your examples and those of others.
How did we ever manage before the Web?

Noel

Tom_Davies · July 11, 2013, 12:19pm

Hi
I am impressed that this list was able to help you migrate your obvious talent so quickly. Andrew's skills with macro programming are quite extreme so getting praise from him is impressive! It is also great to see you use those new skills to help others on this list and help disseminate that knowledge. So, don't sell yourself short! That was great!

I kinda wish i had the knowledge to understand it! I only do html&css coding (ie not real programming) but even so i was able to see the code looked clear and nicely lined-up (despite the attempts of the various clashing emailing systems to try to mess it up). Everything else went over my head of course.

There are other people on the list that also have extreme skills but Andrew's book seems to be the main one that people say is well worth the money
https://wiki.documentfoundation.org/Documentation/Other_Documentation_and_Resources#Programmers
and he is in the documentation team helping write the official guides which can be downloaded freely.
Regards from
Tom

Marion_Noel_Lodge · July 12, 2013, 9:39am

Thanks for your encouragement Tom.

What little experience I've had with HTML convinced me that it was not that
simple - especially when trying to get a Web page to do similar things to
what was possible using a database Form. Admittedly, that was a while ago
- I believe HTML's capabilities are better now.

My observation is that most people can specialise in only fairly small
areas of IT, and therefore we need help from one another when we are
attempting something that is out of our normal. I'm glad when I'm able to
help a bit.

Noel

Tom_Davies · July 12, 2013, 11:16am

Hi :)
Thanks :) I think most of the trick with html is to use it for what it is good for and find other tools to do anything else you might need.

People often seem to want a single tool to do everything but in the outside world they wouldn't expect to be able to use their soup-spoon to empty the bilges of a sinking boat (unless it was a very tiny boat) and neither of those tools would be great for stirring paint with (it makes the soup taste funny). Finding the right tool for the right job and getting the different tools to work together is most of the trick.

Regards from
Tom