[Solved] Data import from MSAccess
-
- Posts: 7
- Joined: Sat Dec 17, 2022 1:21 am
[Solved] Data import from MSAccess
I'm really trying hard to get into OOBase, from MSAccess2013. Please see attached pic for error. I'm able to connect to the AccessDB. I successfully created new OOdb and have copied over 1 table but continue to get error on 2nd table, which has 3663 records. Problem might be the data type "long text" from Access, which has NO defined length. I defined VarChar length as 200 and 500 but it made no difference. Other fields are "short text" and 50, which is same as OO. HELP please.
Update: Oddly it did copy over 13 records???
Update: Oddly it did copy over 13 records???
- Attachments
-
- OOErr124.jpg (74.48 KiB) Viewed 4411 times
Last edited by MrProgrammer on Wed Dec 28, 2022 4:37 am, edited 2 times in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
Jake, OpenOffice 4.1.13, Win10
-
- Posts: 7
- Joined: Sat Dec 17, 2022 1:21 am
Re: Data import from MSAccess
Update: I was finally able to get all 3667 records into the OOdb, minus a single field that was apparently the source of the problem. It is a "long text" data type that apparently is NOT compatible with OOBase. Not sure where to go from here since this is too much data to input manually. I see from posts that MANY others have had this problem and I'm surprised it still persists. I don't mean to be critical of the many who have devoted their time and talent toward development of this alternative to MSOffice. Like many my license was revoked when my motherboard failed and I'm now completely locked out from accessing my data. I would really like to get MS's hook out of me once and for all. Thanks again for any advise.
Jake, OpenOffice 4.1.13, Win10
Re: Data import from MSAccess
If I understood you correctly, you have an Access database, and, can attach to it with OpenOffice OR LibreOffice Base.
And, you need to know the maximum length ( number of characters ) in a specific column in Access so you can define a column length for a table you are defining within Base.
Please follow these steps, so you will know the MAXIMUM length of text characters.
Explanation: The Query will use two Access functions:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
And, you need to know the maximum length ( number of characters ) in a specific column in Access so you can define a column length for a table you are defining within Base.
Please follow these steps, so you will know the MAXIMUM length of text characters.
- Start your OpenOffice / LibreOffice database file ( *.odb
- On the left, click on Queries
- Under Tasks, click on Create Query in SQL View...
- Copy and Paste the Query below:
Code: Select all
SELECT MAX( LEN(`MY_ACCESS_COLUMN`) ) as `MAX_LENGTH_OF_COLUMN` FROM `MY_TABLE`
- Critical step, Either:
- Click on the icon SQL with a check mark
- From the Menu: Edit -> Run SQL command directly
- Change . . . the name of the column, that I used as `MY_ACCESS_COLUMN` in your Access database to the name that is defined in your database
- Change . . . the name of the table, that I used as `MY_TABLE` in your Access database to the name that is defined in your database
- Run the Query, EITHER:
- Press F5 key
- Press Run Query icon
- From the Menu: Edit -> Run Query Directly
Explanation: The Query will use two Access functions:
- LEN which returns the length ( number of characters in each row )
- MAX returns the maximum of the returned values for the LEN function
- The reason the above Query needs to be executed ( Run In Direct Mode is the OpenOffice / LibreOffice Base Parser will flag the LEN function as illegal, since it does NOT follow the SQL standards, or LENGTH. Therefore, run it directly, withOUT the OpenOffice / LibreOffice Parser, and, it will be sent to the database back-end, in your case, Access directly.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 7
- Joined: Sat Dec 17, 2022 1:21 am
Re: Data import from MSAccess
Update: YES, it was in fact 60k characters in a few cases due to users skipping MANY lines and inadvertently including blank spaces... my bad!
Hmmm, this can't be... can it? 63,240 characters??? I guess that could be a max for Access' "Long Text" field type, but I'm certain I never approached this. One would think that OO would simply truncate the data should you exceed the maximum.
Regardless and to reiterate, I can connect to my existing accdb (Access 2007 format) with OO. I can also import my Access table data into OO, IF I exclude this problematic "Long Text" field. All fails on importing when I include this field, as shown in pic of original post.
Hmmm, this can't be... can it? 63,240 characters??? I guess that could be a max for Access' "Long Text" field type, but I'm certain I never approached this. One would think that OO would simply truncate the data should you exceed the maximum.
Regardless and to reiterate, I can connect to my existing accdb (Access 2007 format) with OO. I can also import my Access table data into OO, IF I exclude this problematic "Long Text" field. All fails on importing when I include this field, as shown in pic of original post.
- Attachments
-
- OOErr122622.jpg (21.77 KiB) Viewed 4301 times
Last edited by JakeRogers on Wed Dec 28, 2022 3:55 pm, edited 1 time in total.
Jake, OpenOffice 4.1.13, Win10
Re: Data import from MSAccess
One possibility is, you have trailing spaces at the end of a field, so that it filled up the LENGTH to 63240 characters.JakeRogers wrote: Hmmm, this can't be... can it? 63,240 characters??? I guess that could be a max for Access' "Long Text" field type, but I'm certain I never approached this.
Therefore . . . try the following Query . . . replacing your column name, and, table name according to your Access database.
Code: Select all
Select
MAX( LEN( `Remarks` ) ) as `MAX_LENGTH_REMARKS`,
MAX( LEN( TRIM(`Remarks`) ) ) as `MAX_LENGTH_TRIM_REMARKS`,
MAX( LEN( LEFT(`Remarks`, 32000) ) ) as `MAX_LENGTH_LEFT_REMARKS`,
From `Recipes`
- The TRIM function will remove any trailing spaces from the column for calculation of LENGTH
- The LEFT function will only look at the first 32000 characters, OR, whatever number you replace it with.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 7
- Joined: Sat Dec 17, 2022 1:21 am
Re: Data import from MSAccess
Sorry to be so much trouble, but I now get a Syntax error when executing code.
Jake, OpenOffice 4.1.13, Win10
Re: Data import from MSAccess
¿ Did you follow steps 5 AND 8 above ?JakeRogers wrote:Sorry to be so much trouble, but I now get a Syntax error when executing code.
IMPORTANT: I made a small mistake in the Query . . . please . . . remove the trailing comma from 4th line . . . the line BEFORE the FROM part.
It should have been:
Code: Select all
Select
MAX( LEN( `Remarks` ) ) as `MAX_LENGTH_REMARKS`,
MAX( LEN( TRIM(`Remarks`) ) ) as `MAX_LENGTH_TRIM_REMARKS`,
MAX( LEN( LEFT(`Remarks`, 32000) ) ) as `MAX_LENGTH_LEFT_REMARKS`
From `Recipes`
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 7
- Joined: Sat Dec 17, 2022 1:21 am
Re: Data import from MSAccess
OK, removing the comma did the trick. However, the max trimmed len showed 63,192; which surprisingly was in fact ACCURATE.
Regardless, upon setting the "Remarks" field to a VarChar length of 63192, it did indeed transfer. Many thanks for your patience and help with this. Now to get busy with form and query development.
Regardless, upon setting the "Remarks" field to a VarChar length of 63192, it did indeed transfer. Many thanks for your patience and help with this. Now to get busy with form and query development.
Last edited by JakeRogers on Wed Dec 28, 2022 3:57 pm, edited 1 time in total.
Jake, OpenOffice 4.1.13, Win10
Re: [Solved] Data import from MSAccess
Code: Select all
SELECT *, MAX( LEN( `Remarks` ) ) AS `MAX_LENGTH_REMARKS`
FROM `Recipes`
ORDER BY `MAX_LENGTH_REMARKS` DESC
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Data import from MSAccess
Villeroy:
Just as an FYI, ( For Your Information ), and, others reading this post, the Query you have written will not work exactly as you have written it . . . but . . . it would need a small change as below, for use by Access with an ODBC connection:
OR to return the columns displayed in as defined ( first column to last ) use:
The reason, the ORDER BY clause cannot reference a calculated column ( with Access and ODBC connection ) . . . BUT . . . by including it ( calculated column ) again in the ORDER BY clause, it works.
Great idea.
Sliderule
Just as an FYI, ( For Your Information ), and, others reading this post, the Query you have written will not work exactly as you have written it . . . but . . . it would need a small change as below, for use by Access with an ODBC connection:
Code: Select all
SELECT *, MAX( LEN( `Remarks` ) ) AS `MAX_LENGTH_REMARKS`
FROM `Recipes`
ORDER BY MAX( LEN( `Remarks` ) ) DESC
Code: Select all
SELECT `Recipes`.*, MAX( LEN( `Remarks` ) ) AS `MAX_LENGTH_REMARKS`
FROM `Recipes`
ORDER BY MAX( LEN( `Remarks` ) ) DESC
Great idea.
Sliderule