Open Office Calc Pro Required

Just a meeting place for professional offers & requests
Forum rules
Disclaimer: this section of the forum is just a meeting place for professional offers & requests. We do not and cannot insure the liability of the proposal made publicly in the forum or privately after contact has been made in the forum. Therefore, please take up the offers made here at your own risk.
Post Reply
colin
Posts: 9
Joined: Mon Apr 28, 2008 7:33 am

Open Office Calc Pro Required

Post by colin »

Hi there,

Looking for some help ASAP with a spreadsheet. Most of what I can not figure out is a repeat of the same problem. This is part of a bigger project I need some help with. If you can sort this out you are the man or the women for the job.

Please download files attached.

Write a formula in a cell b2, see "sample of departure instance extraction.ods", that extracts data from DEPARTUREINSTANCES.csv (saved as ods to allow for upload).

The idea is that the formula will:

- Reference A2 to look for the appropriate FKey number. So if there is a 1 in A2 the then selected data from rows where the Fkey in departureinstances.csvs one is aggregated along with some html.

- Should be able to copy the formula down to b3...b30 so that data is aggregated from Fkey rows 1...30.

Please have a peak at the spreadsheets. Would surely appreciate any help.

Thanks

Colin
Attachments
sample of departure instance extraction.ods
(10.18 KiB) Downloaded 400 times
DEPARTUREINSTANCE - Copy.ods
Please change the extension to csv on this file
(20.08 KiB) Downloaded 373 times
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Open Office Calc Pro Required

Post by keme »

I was a little confused, but it is starting to clear up I guess...
If I'm reading the files right, your stated "Sample output for FKEY_1" should be generated for FKEY_2, and you have applied date formatting to INSTANCEID and STARTDATE, where it should have been applied to STARTDATE and ENDDATE. (Please confirm or explain).

I gather you need field definition blocks to insert into a web form. The sensible approach, as I see it, would be to use Base to process the CSV file, but it sems that Base and Calc use different values for "day number", because when comparing the file imported in Base with the same imported in Calc, all the dates are off by a couple of days. (It might be a setting i've done, as I've tinkered a bit with settings to facilitate importing data from applications on different platforms, not always knowing exactly what I was doing, I guess...)

I'll look into it further if you see this as a viable approach.

Edit: Got the date difference sorted out. It seems that with CSV databases Base uses the old Staroffice "day zero", which is two days after the Calc default "Day zero". Converting the CSV to spreadsheet first seems to resolve the issue.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
colin
Posts: 9
Joined: Mon Apr 28, 2008 7:33 am

Re: Open Office Calc Pro Required

Post by colin »

Jujst posted this in the other thread...sorry for the confusion.

_______________________________

Thanks for getting back to me on this.

Absolutely right on using a data base however I am using a shopping cart with an automated update function that grabs a csv file and then extracts the data to a rather complex set of tables...there are 118 tables in total which are all interrelated and it just seems a whole lot easier to resolve this one formula then to start messing with tables and code.

The csv data is extracted by an automated process every three hours from xml feeds which themselves are varied and complicated.

Anywho, the spread sheet integrates the csv data with html and other code in preparation for auto upload by the carts automated updater.

One formula and everything will work just fine...already tested...just need the darned formula.
colin
Posts: 9
Joined: Mon Apr 28, 2008 7:33 am

Re: Open Office Calc Pro Required

Post by colin »

Yes, we are looking to extract start and end date and availability data for fkey 1 from within the range c2 through g8 on the csv file.
-
Yes, that is correct on the <option>'s for a form but this little sample is only part of a much larger spreadsheet.
-
Please check out the attached.

On the tab marked shopping cart fields in column A are the fields (transposed) that are automatically updated by the shopping cart auto updater. The updater does this by uploading this date from a csv file . In column B, on this same tab is a summary of the data extracted from the xml's to csv's. The light grey fields are where the xml data (parsed from url's) must be integrated with HTML (see tab kumku html) before it is ready for the shopping cart field A21. The dark grey fields are data where instances or strings are involved. A sample csv file with a string is also attached.

All this is easy to do in a spreadsheet except for the instances.
-
I would love to see all this in base if:

1) it could be done in a way that is flexible. Feeds are new to this industry and are likely to change a fair bit over time plus we will adding and deleting feeds as we go.
2) if the exercise is easily repeatable for different feed types. We have 4 sets of feeds to deal which although similar each have slightly different html integration requirements.

This is probably somewhat confusing. Lord knows I am getting confused doing the writing.

Appreciate your interest to date look forward to hearing from you.
Attachments
xml----html----xstl----calc.ods
(20.54 KiB) Downloaded 474 times
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Open Office Calc Pro Required

Post by keme »

The task seems to require a little more work than I have the time for at the moment.
I may get back to it, but someone else with more experience in this field may be able to do it sooner.
(Please feel free to enter, I don't consider the project "owned" on my behalf.)
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open Office Calc Pro Required

Post by Villeroy »

Absolutely right on using a data base however I am using a shopping cart with an automated update function that grabs a csv file and then extracts the data to a rather complex set of tables...there are 118 tables in total which are all interrelated and it just seems a whole lot easier to resolve this one formula then to start messing with tables and code.
shopping cart, update, complex set of 118 interrelated tables, ...
You are describing nothing else but a database!
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
Post Reply