Hi;
Searched Help files to no avail.
I have 400 rows of customer addresses in a table in a Writer document.
I want to get this data into a table in Base.
Currently I am totally lost. Any hints will be greatly appreciated.
I think I mostly don't need step by step instructions, just the gist of it. Although I may be back if I get stuck
TIA
How To get Witer Table into (data-) Base
Re: How To get Witer Table into (data-) Base
Copy the writer table to a Calc document. Save.
Now create your new base file, pointing ("connect to existing data source": spreadsheet document) to the existing calc sheet.
Does this help?
Now create your new base file, pointing ("connect to existing data source": spreadsheet document) to the existing calc sheet.
Does this help?
Re: How To get Witer Table into (data-) Base
Note, however, that the method suggested by probe1 will not allow you to edit your customer addresses from within the Base document. You will have to do all editing from within the Calc document.
If you want to be able to edit the data in Base, there is another method:
Copy the Writer Table into a Calc Document.
Copy the entire range of your data (including column headers, if you have them)
create a new database document (or open the existing one you want to include this table)
If you are creating a new database, set the radio button for Create a New Database. Click Next. In the next screen, make sure "register the database" is checked and the "open the database for editing" radio button is selected. Click finish.
In the main database document window, click on the "tables" icon in the left column.
In the Tables section of the database document (the bottom half of the window), right-click on any white area and choose "paste" from the contextual menu.
Follow the instructions in the wizard that appears. Be sure to eiither identify a primary key, or to have the wizard create one for you. If you don't, you won't be able to edit data. (To identify a current field as a primary key, in the pane where the fields are listed, right click the field and choose "primary key" from the contextual menu.)
kabing
If you want to be able to edit the data in Base, there is another method:
Copy the Writer Table into a Calc Document.
Copy the entire range of your data (including column headers, if you have them)
create a new database document (or open the existing one you want to include this table)
If you are creating a new database, set the radio button for Create a New Database. Click Next. In the next screen, make sure "register the database" is checked and the "open the database for editing" radio button is selected. Click finish.
In the main database document window, click on the "tables" icon in the left column.
In the Tables section of the database document (the bottom half of the window), right-click on any white area and choose "paste" from the contextual menu.
Follow the instructions in the wizard that appears. Be sure to eiither identify a primary key, or to have the wizard create one for you. If you don't, you won't be able to edit data. (To identify a current field as a primary key, in the pane where the fields are listed, right click the field and choose "primary key" from the contextual menu.)
kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
How To get Witer Table into (data-) Base
Thank You, probe1 and kabing.
It will take me a while until I have the time to try kabing's solution. I intend to report back.
probe1, what you suggest is what I would call a Kludge, but thanks anyway.
PS: "Need" to know what a Kludge is?
Look here http://en.wikipedia.org/wiki/Kludge and scroll down to to "Computer science use".
Oh, BTW, I am old enough to witness to the fact that Datamation spelled it kluDge. In Datamation's column about Kludge Komputer Korporation (aka KKK, a spoof on IBM) it was the least effective, least efficient, least economical and generally least adequate solution to a problem that mostly could have been avoided by better engineering in the first place.
Sounds to me a lot like Macro$oft
It will take me a while until I have the time to try kabing's solution. I intend to report back.
probe1, what you suggest is what I would call a Kludge, but thanks anyway.
PS: "Need" to know what a Kludge is?
Look here http://en.wikipedia.org/wiki/Kludge and scroll down to to "Computer science use".
Oh, BTW, I am old enough to witness to the fact that Datamation spelled it kluDge. In Datamation's column about Kludge Komputer Korporation (aka KKK, a spoof on IBM) it was the least effective, least efficient, least economical and generally least adequate solution to a problem that mostly could have been avoided by better engineering in the first place.
Sounds to me a lot like Macro$oft
- Hagar Delest
- Moderator
- Posts: 32683
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: How To get Witer Table into (data-) Base
Please do. It will help other users with this same issue.eikelein wrote:It will take me a while until I have the time to try kabing's solution. I intend to report back.
Thanks to add '[Solved]' in your first post title (edit button) if your issue has been fixed.
NB: moved the thread in the Base forum.
LibreOffice 24.2 on Xubuntu 24.04 and 7.6.4.1 portable on Windows 10
Re: How To get Witer Table into (data-) Base
This is the recommended (and the only?) way to import csv data as well. As a matter of fact this limits the import capabilities of Ooo to the quite ridiculous number of 65536 lines... I wonder when there will be a direct import feature for csv data in Oo Base.kabing wrote:Note, however, that the method suggested by probe1 will not allow you to edit your customer addresses from within the Base document. You will have to do all editing from within the Calc document.
If you want to be able to edit the data in Base, there is another method:
Copy the Writer Table into a Calc Document.
Copy the entire range of your data (including column headers, if you have them)
create a new database document (or open the existing one you want to include this table)
If you are creating a new database, set the radio button for Create a New Database. Click Next. In the next screen, make sure "register the database" is checked and the "open the database for editing" radio button is selected. Click finish.
In the main database document window, click on the "tables" icon in the left column.
In the Tables section of the database document (the bottom half of the window), right-click on any white area and choose "paste" from the contextual menu.
Follow the instructions in the wizard that appears. Be sure to eiither identify a primary key, or to have the wizard create one for you. If you don't, you won't be able to edit data. (To identify a current field as a primary key, in the pane where the fields are listed, right click the field and choose "primary key" from the contextual menu.)
kabing
It is a must if Ooo is meant to catch up with Access where importing csv data is not limited at all and (by the way) performs quite well.
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: How To get Witer Table into (data-) Base
A couple of points on importing csv files to base.
You do not have to do this via Calc. You can use a temporary Base file instead:
File>New>Database
Select 'Connect to existing database'
In the pull down box select "Text"
[Next]
On this page browse to the location of the csv file(s)
Setup up the options as needed.
[Finish]
Name the temp odb file and save it.
This gives you a Base file that shows each csv file in the directory as a table - it is a linked connection to these tables if you will.
Now create or open the actual Base file ( embedded Relational Database Engine ) file that you want the data in.
Drag a table from the Text based file to the Final database. This will start the Copy Table Wizard.
You can insert the data to a new table, or append it to an existing table.
You could use the query designer in the Text table to manipulate the data and drag this query to the Table section in the Final database and import this 'massaged' data to a new table or append it to an existing one.
This will get you beyond the Calc limitation on the number of rows allowed.
Two - In the 2.4 release of OpenOffice.org there are some new API stubs added, specifically to allow extension writers to build a more user friendly import / export feature. There are already 2 implementations under way for this type of extension, so no doubt at least one will be ready when 2.4 ships.
Drew
You do not have to do this via Calc. You can use a temporary Base file instead:
File>New>Database
Select 'Connect to existing database'
In the pull down box select "Text"
[Next]
On this page browse to the location of the csv file(s)
Setup up the options as needed.
[Finish]
Name the temp odb file and save it.
This gives you a Base file that shows each csv file in the directory as a table - it is a linked connection to these tables if you will.
Now create or open the actual Base file ( embedded Relational Database Engine ) file that you want the data in.
Drag a table from the Text based file to the Final database. This will start the Copy Table Wizard.
You can insert the data to a new table, or append it to an existing table.
You could use the query designer in the Text table to manipulate the data and drag this query to the Table section in the Final database and import this 'massaged' data to a new table or append it to an existing one.
This will get you beyond the Calc limitation on the number of rows allowed.
Two - In the 2.4 release of OpenOffice.org there are some new API stubs added, specifically to allow extension writers to build a more user friendly import / export feature. There are already 2 implementations under way for this type of extension, so no doubt at least one will be ready when 2.4 ships.
Drew
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: How To get Witer Table into (data-) Base
I tried this shortly after having posted my message. Since I need write-access to the data only, this would be sufficient. But now I found that the datatypes are not correct. If a number has leading zeros Base makes the field a text field. I cannot change this but by copying the tables like you suggest. This is not appropriate for a workflow for non-techies which I am working on. Unfortunately I cannot wait for OO2.4 so I am evaluating Kexi now.DrewJensen wrote:A couple of points on importing csv files to base.
You do not have to do this via Calc. You can use a temporary Base file instead:
File>New>Database
Select 'Connect to existing database'
In the pull down box select "Text"
[Next]
On this page browse to the location of the csv file(s)
Setup up the options as needed.
[Finish]
Name the temp odb file and save it.
This gives you a Base file that shows each csv file in the directory as a table - it is a linked connection to these tables if you will.
Thanks for the help anyway.
-
- Volunteer
- Posts: 283
- Joined: Tue Dec 04, 2007 6:38 pm
- Location: Houston, TX
Re: How To get Witer Table into (data-) Base
I do not know if are still in search for another solution, but just in case here is a function I wrote that takes data from a recordset and writes it to a table in writer. If you reverse the processs you can get the table and put it in a base table/recordset.
use the String property or getString() method of the table cell (rather than setString(...) as above) to get the data from the cell. you can store it into a variable, coarse data type if necessary, and put it in your table (RowSet Service). you can read my docs at http://www.geocities.com/rbenitez22 for some info on using the RowSet Service.
Code: Select all
Sub convertRecorSetToTextTable(ResultSet As Object,Doc As Object)
Dim TextTables As Object
Dim Table As Object
Dim ColCount As Integer
Dim RowCount As Integer
Dim DataArray() 'As Object 'As String
Dim DataRow()
Dim I As Integer
Dim InsertPoint As Object
Dim TableName As String
Dim J As Integer
TableName="ResultSet export"
ResultSet.last()
RowCount=ResultSet.Row+1 REM 1 MORE ROW FOR HEADING
ColCount=ResultSet.Columns.getCount()
ResultSet.first():ResultSet.relative(-1)
Table=Doc.createInstance("com.sun.star.text.TextTable")
Table.initialize(RowCount,ColCount)
If Doc.getBookMarks().hasByName("TABLE_BOOKMARK") Then
InsertPoint=Doc.getBookMarks().getByName("TABLE_BOOKMARK").getAnchor().getText()
Else
InsertPoint=Doc.Text.getEnd().getText()
End If
InsertPoint.InsertTextContent(InsertPoint,Table,False)
REM ADD HEADER--COLUMN NAMES
For I=0 To ColCount-1
Table.getCellByPosition(I,0).setString(ResultSet.Columns.getByIndex(I).Name)
Next I
While ResultSet.next()
For I=0 To ResultSet.Columns.getCount()-1
Table.getCellByPosition(I,ResultSet.Row).setString(ResultSet.getString(I+1) )
Next I
Wend
End Sub