How To get Witer Table into (data-) Base

[eikelein]

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

[probe1]

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?

[kabing]

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

[eikelein]

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

[Hagar Delest]

It will take me a while until I have the time to try kabing's solution. I intend to report back.

Please do. It will help other users with this same issue.

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.

[snorri]

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

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.
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]

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

[snorri]

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.

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.
Thanks for the help anyway.

[QuazzieEvil]

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.

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

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.