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
Importing/Accessing CSV Data from a form
Importing/Accessing CSV Data from a form
Libre Office 5.1 on Lubuntu 14.04.01
Re: Importing/Accessing CSV Data from a form
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
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 10 AOO, LOLinux Mint AOO, LO
Re: Importing/Accessing CSV Data from a form
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:
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.
The following view definition translates the German date strings into true date values and the comma decimals into DECIMAL(10,2) type of values.
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.
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
Datum Gruppe Betrag
12.01.2015 A -3321,87
08.05.2016 B 172911,33
Field separator is a tab. The first line with the German column labels will be ignored.
Code: Select all
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"
Code: Select all
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"
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 407 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice