Importing/Accessing CSV Data from a form

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

Importing/Accessing CSV Data from a form

Postby craigr12 » Sun Sep 11, 2016 2:44 pm

Hello,

I'm using Base to create an invoicing database for a local charity. Due to the main user's extreme technophobe tendencies, I need someway of getting the monthly data fed into the system without going anywhere near the tables section on base. I have run her through the whole select-paste-right-click process but it's a no go. She can cope with pasting data into a spreadsheet, saving it in a directory then pressing a button on a form.
I've done some googling and it looks like there may be some ways of doing this. I'm quite happy to roll my sleeves up and teach myself a new bit of software but I'd rather know the end result was feasible before I got stuck in.
Any advice gratefully received.

Thanks,
Craig
Libre Office 5.1 on Lubuntu 14.04.01
craigr12
 
Posts: 1
Joined: Sun Sep 11, 2016 2:37 pm

Re: Importing/Accessing CSV Data from a form

Postby F3K Total » Thu Oct 06, 2016 6:23 am

Hi,
there is no way to import csv data directly from a form, AFAIK.
But it should be no problem, to write a macro, that opens a file-selection dialog and then imports the selected files data into the database. It's also possible without file-selection dialog, if the file-name and path are always the same.

R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 904
Joined: Fri Dec 16, 2011 8:20 pm

Re: Importing/Accessing CSV Data from a form

Postby Villeroy » Thu Oct 06, 2016 4:20 pm

Our little database tool is very, very simplistic. You can use it in many unforseen ways. I made the following assumptions about incoming data:

1) The file name is always named Import.csv residing in the same directory as the database document.
2) Incoming csv does not match standards. My incoming text is a tab separated, UTF-8 encoded file from Germany with a header row, dd.mm.yyyy dates and comma decimals.

This is my test file Import.csv in the same directory as the database document:
Code: Select all   Expand viewCollapse view
Datum   Gruppe   Betrag
12.01.2015   A   -3321,87
08.05.2016   B   172911,33


The following SQL creates a HSQL text table linked to Import.csv having a character string of 10 characters representing the DD.MM.YYYY dates, a category field of one character (A,B,C,D) and 10 chars representing some sequence of digits with one optional comma and leading minus sign.
Field separator is a tab. The first line with the German column labels will be ignored.
Code: Select all   Expand viewCollapse view
CREATE TEXT TABLE "csvLink"("Date" CHAR(10),"Group" CHAR(1),"Amount" VARCHAR(10));
SET TABLE "csvLink" SOURCE "Import.csv;fs=\t;ignore_first=true;encoding=UTF-8"


The following view definition translates the German date strings into true date values and the comma decimals into DECIMAL(10,2) type of values.
Code: Select all   Expand viewCollapse view
SELECT
  CAST( substr( "Date", 7, 4 ) || '-' || substr( "Date", 4, 2 ) || '-' || substr( "Date", 1, 2 ) AS DATE ) AS "Date",
  "Group",
  CAST( REPLACE( "Amount", ',', '.' ) AS DECIMAL ( 10 , 2 ) ) AS "Amount"
FROM "csvLink"


In addition to the linked text table and the view, the database contains a native HSQL table "Data" with properly typed Date, Group, Amount, plus an additional auto-ID, a time stamp and a user-ID field. This is the target table of our import. In order to prevent duplicate imports, the "Data" table has a unique index on Date, Group and Amount.
A related "Users" table contains the user IDs and user initials to be picked from a list box.

There is a list box query for the user entries and another query as source of a report.

The form has a frame with explanations, i.e. about the user-ID field which I added for a special reason.

I added 2 reports from the same query. One for the Oracle Report Builder and one old style report without ORB.

Every Day Usage
Most of the work is done by proper setup of the import table and the view. With a file Import.csv in the same directory following the same German data pattern as outlined above, all you've got to do with the attached example file is dragging the view over the data table and confirm the import dialog. The data types and field order is already taken care of.
Attachments
CSV.Import.odb
Link, convert and import csv by means of a form with no macro code.
(28.94 KiB) Downloaded 130 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26623
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 0 guests