[Solved] Importing 2003 MS Access into Base

Discuss the database features

[Solved] Importing 2003 MS Access into Base

Postby THEBookMan » Tue Jul 10, 2018 6:40 pm

I have an old 2003 MSAccess database that I'd like to convert to OpenOffice BASE

5 tables:
tblLectureId: 144 rows, 5 fields......Easy enough to do manually
tblSwitchBoard: Will recreate in Base
tblBooks: 55 rows, 3 fields......Ease enough to do manually
tblMain: 21,000+ rows, 9 fields (2 of which are memo fields).......... This is the problem!

All have been saved in .CSV files and imported into individual OOCalc files.

Here is the first few rows of the tblMAIN:

Word Context CommonName Page Paragraph Order (BookNum) Chapter Verse Notes
possess I don't think the creature Minister's meeting before 4 2 0 (null) (null) (null) (null)
has the ability to correct the first class in Los
and instruct the Creator Angeles, CA
ability I don't think the creature Minister's meeting before 4 2 0 (null) (null) (null) (null)
has the ability to correct the first class in Los
and instruct the Creator Angeles, CA
correct I don't think the creature Minister's meeting before 4 2 0 (null) (null) (null) (null)
has the ability to correct the first class in Los
and instruct the Creator Angeles, CA
instruct I don't think the creature Minister's meeting before 4 2 0 (null) (null) (null) (null)
has the ability to correct the first class in Los
and instruct the Creator Angeles, CA
creator I don't think the creature Minister's meeting before 4 2 0 (null) (null) (null) (null)
has the ability to correct the first class in Los
and instruct the Creator Angeles, CA
No time,"God has never, at no time, left himself exposed and allowed man to never, "God has never, at no time, left himself exposed and ---->> etc
exposed,"God has never, at no time, left himself exposed and allowed man to never, "God has never, at no time, left himself exposed and ---->> etc
allow,"God has never, at no time, left himself exposed and allowed man to never, "God has never, at no time, left himself exposed and ---->> etc
solely,"God has never, at no time, left himself exposed and allowed man to never, "God has never, at no time, left himself exposed and ---->> etc
confirm,"God has never, at no time, left himself exposed and allowed man to never, "God has never, at no time, left himself exposed and ---->> etc
verify,"God has never, at no time, left himself exposed and allowed man to never, "God has never, at no time, left himself exposed and ---->> etc
existence,"God has never, at no time, left himself exposed and allowed man to never, "God has never, at no time, left himself exposed and ---->> etc
judgement,"This is the judgment of the world, this is sufficient to judge angels ---->> etc
world,"This is the judgment of the world, this is sufficient to judge angels ---->> etc
(not parsing quite correctly, but I hope you get the picture)

And on and on for 21,000+ rows

The header row is OK.
The next 5 rows have been re-formatted manually.

Anyone help with a macro/different way to import/or anything so I don't have to manually re-format the rest of the rows?

TNX in advance (again, You guys are the best!)
⌡im
Last edited by THEBookMan on Tue Sep 11, 2018 6:32 pm, edited 1 time in total.
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 58
Joined: Wed Sep 30, 2015 10:03 pm
Location: GB, WI (some sort of sport team lives around here)

Re: Importing 2003 MSAccess into BASE

Postby Villeroy » Tue Jul 10, 2018 6:50 pm

From Access to csv and then into spreadsheets before importing into another database involves at least 3 steps too many. If you want to get spreadsheets from MS Access, Access can easily export raw data to xls or xlsx (if Excel is installed as well).

How would you do it with MS Access? You would connect Access to the foreign database and then copy over the tables or records in eiter direction and so you could do the job with Base as well.

You can also use your MS Access database with Base just like you can use many types of databsaes with MS Access. If you maintain your database with MS Access and need an additional bridge to your ODF suite, just connect a Base document to the Access database and use the contained data in Calc and Writer.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25839
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing 2003 MSAccess into BASE

Postby Sliderule » Tue Jul 10, 2018 7:07 pm

THEBookMan:

THEBookMan wrote:I have an old 2003 MSAccess database that I'd like to convert to OpenOffice BASE

Since, OpenOffice /LibreOffice Base is NOT a database, but, rather just a front-end to a database back-end . . . you have NOT told us what your database back-end is.

For example:
  1. HSQL Embedded ( HSQL Version 1.8.0.10 )
  2. HSQLDB ( HSQL Version 2.4.1 )
  3. H2
  4. MySQL
  5. Firebird
  6. Sqlite
  7. Microsoft Access
  8. DB2
  9. Microsoft Server
  10. PostgreSQL
  11. Other
You do have an option, to continue using your Microsoft Access database within OpenOffice / LibreOffice Base. That will mean, you can read / write to the data. And, any Queries you have defined will be available. However, any Forms or Reports will not be available ( as stored in your Access file ), and, you will have to create an OpenOffice / LibreOffice Base version.

Please see the following Tutorial Link, scroll down to the link from user Villeroy with a date / time of Sat Apr 30, 2016 5:50 pm :

Tutorial MS Access and OOo Base

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1151
Joined: Thu Nov 29, 2007 9:46 am

Re: Importing 2003 MSAccess into BASE

Postby Villeroy » Tue Jul 10, 2018 7:25 pm

If you have a legal copy of Access 2003 on a Windows-PC, then you have one of the best database front-ends at hand. Keep using it with your Access views, forms, reports etc. While managing your database data with MS Access (which is by far the superior product) you can also use the same database with some other front-end such as Base. The details depend on the back-end, but before anything else, Base is a bridge between databases and office documents in Open Document Format (ODF).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25839
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 9 guests