Load csv data into a table in a Libre Office Database
- or -
Post a project like this2400
$$$
- Posted:
- Proposals: 5
- Remote
- #1706454
- Awarded
Description
Experience Level: Expert
SEE ADDITIONAL POINTS ADDED AFTER THE FIRST POSTING AT THE BOTTOM
General information for the business: We are management consultants.
Description of requirements/functionality: I buy database information from the Russian Customs Service.
I buy a set of data related to the imports of a particular product. Maybe diesel engines, maybe coffee. So I purchase regularly.
It has the same format every time. There are 78 columns and up to 75,000 records. The initial format is *.csv. I want to load this into a table in a Libre Office BASE database management system. The underlying database is hsqldb. The standard way to load data into LO Base is to cut and paste! There is no Import facility. Even on a 78 x 8,000 csv the system crashes. 75,000 is too many.
I want to import/append rather than create a new data table because I want to automate complex chains of macros and numerous relationships with other data tables, queries and reports - the same ones every time. Also, the headings on the csv file are in Cyrillic. My blank database will have the same heading but translated into English.
The ultimate goal is to automatically write a first draft about the Russian market for (for example) peanuts. I have attached the initial peanut csv.bz2 as an example.
The answer might be to use SQL? Can it import a csv document into Base? A line by line importation might be required. My old DBMS, Lotus Approach, would handle about 20,000 rows an hour, including data cleaning as each row was imported. Once imported, Lotus Approach handled the records much faster than that. Also every year when the clocks changed, Lotus A wiped out the Cyrillic encoding.
I think this is about writing an SQL script for me and telling me how to paste it into Libre Office . Screen sharing via Google Hangouts or Skype to get that done. For someone who knows how to do it, I have an SQL script written for a slightly different set up that was posted on a help site. This is a day's work at the most to write it and maybe half a day to support me to implement it.
Specific technologies required: Libre Office Base
Extra notes:
ADDITIONAL POINTS
This is a link to a discussion where some commenting on 21 Jul 2015 offered some code to solve the problem
http://ooo-forums.apache.org/en/forum/viewtopic.php?f=20&t=64300#
The last time I coded anything it was called Dartmouth BASIC. IT was FORTRAN before that.
I can set up the SQL Array statements myself. Not every column in the csv needs to make it into the hsqldb so I need to know how to deal with unwanted columns.
It hasn't worked until up to 75,000 records (we will test with imports of Tea) have been appended.
A workaround could be that I create a new HSQLDB table with the same title every time. Creating the database from new works. My problem is access to macros and scripts. I don't want to rewrite them every time. So then I need to append data from table ORIGINALCUSTOMSDATA to WORKINGCUSTOMSDATA. WORKINGCUSTOMSDATA starts life as a blank table but if has all the macros, scripts and extensions that I need set up.
General information for the business: We are management consultants.
Description of requirements/functionality: I buy database information from the Russian Customs Service.
I buy a set of data related to the imports of a particular product. Maybe diesel engines, maybe coffee. So I purchase regularly.
It has the same format every time. There are 78 columns and up to 75,000 records. The initial format is *.csv. I want to load this into a table in a Libre Office BASE database management system. The underlying database is hsqldb. The standard way to load data into LO Base is to cut and paste! There is no Import facility. Even on a 78 x 8,000 csv the system crashes. 75,000 is too many.
I want to import/append rather than create a new data table because I want to automate complex chains of macros and numerous relationships with other data tables, queries and reports - the same ones every time. Also, the headings on the csv file are in Cyrillic. My blank database will have the same heading but translated into English.
The ultimate goal is to automatically write a first draft about the Russian market for (for example) peanuts. I have attached the initial peanut csv.bz2 as an example.
The answer might be to use SQL? Can it import a csv document into Base? A line by line importation might be required. My old DBMS, Lotus Approach, would handle about 20,000 rows an hour, including data cleaning as each row was imported. Once imported, Lotus Approach handled the records much faster than that. Also every year when the clocks changed, Lotus A wiped out the Cyrillic encoding.
I think this is about writing an SQL script for me and telling me how to paste it into Libre Office . Screen sharing via Google Hangouts or Skype to get that done. For someone who knows how to do it, I have an SQL script written for a slightly different set up that was posted on a help site. This is a day's work at the most to write it and maybe half a day to support me to implement it.
Specific technologies required: Libre Office Base
Extra notes:
ADDITIONAL POINTS
This is a link to a discussion where some commenting on 21 Jul 2015 offered some code to solve the problem
http://ooo-forums.apache.org/en/forum/viewtopic.php?f=20&t=64300#
The last time I coded anything it was called Dartmouth BASIC. IT was FORTRAN before that.
I can set up the SQL Array statements myself. Not every column in the csv needs to make it into the hsqldb so I need to know how to deal with unwanted columns.
It hasn't worked until up to 75,000 records (we will test with imports of Tea) have been appended.
A workaround could be that I create a new HSQLDB table with the same title every time. Creating the database from new works. My problem is access to macros and scripts. I don't want to rewrite them every time. So then I need to append data from table ORIGINALCUSTOMSDATA to WORKINGCUSTOMSDATA. WORKINGCUSTOMSDATA starts life as a blank table but if has all the macros, scripts and extensions that I need set up.
Philip O.
100% (11)Projects Completed
17
Freelancers worked with
12
Projects awarded
46%
Last project
27 Oct 2021
Russian Federation
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies