I sent the following last night, but thought I should re-send to let Andreas know in the post title that this letter is related specifically with his step-by-step guide (on the dba users' list) for importing .mdb into OO Base. (Sorry for the re-post-- just wanted to ensure that Andreas would be aware of the content.)
--------------------------------------------------------------- On Fri, 2007-08-31 at 21:38 +0200, Andreas Saeger wrote: > Hello, Swarup > I've added a detailed step-by-step instruction how to import mdb via > spreadsheet to Base. Have a look at the users.dba list and give a try. > > Good luck, > Andreas Hi Andreas, I tried doing the How-To you wrote in the users list, and got I think a lot of success. There are three places where I've gotten stuck, but I suspect those will be able to be fixed by my describing the situation to you. (Your write-up is pasted below at the end of this letter, for reference.) 1. I think I was able to understand and follow each step of your instructions. And in the end, the table was made. Indeed, all the columns and rows are there, correctly labeled. And all the data is there as well-- with two exceptions. I have two columns which are of date type. Those columns are both completely blank. I'm not certain why, but as I say, I suspect you will be able to tell me by seeing what I did, described here below. I made the spreadsheet in the way you indicated-- dragged the table in, and for the "date" column, created a new column at the end and relabeled it in the name of the date column. Then, relabeled the date column "SKIP". And did as you describe here below: "Get the first unused column (let's say "K") enter field name GMT in K1, change the original header to "SKIP" or something, select K2 and hit Ctrl+Shift+End this highlights the entire used range from K2 until the end of the list. The input focus is in the last cell (K97 in my case). Now type =TEXT(I97;"YYYY-MM-DD HH:MM:SS") and finish the input with Alt+Enter. This converts the numeric value in column "I" of this row (97) to a text-value, representing the ISO-date. Alt+Enter puts the formula into all highlighted cells. "2002-05-29 13:30:00"." When I did the above, even after hitting "Alt+Enter" the entire "K" column from K2 down to the next-to-last cell (i.e. in your example, K96) remained blank. Just in the lower most cell of the K column (K97), appeared a date-time stamp in the following pattern: "2002-05-29 13:30:00". Is that what was supposed to happen? At the time I was thinking perhaps it is fine, as you had written, "Alt+Enter puts the formula into all highlighted cells"-- so I thought to myself "It's a bit strange the column remained blank in this way, but perhaps its the "formula" is silently there and will apply itself when I do the paste". --But now looking retrospectively, I think there really was nothing there. So something went wrong in the production of that K column. Although as I say, the date did appear in proper format in the very last cell of the column. I basically typed =TEXT(I97;"YYYY-MM-DD HH:MM:SS"), and just replaced the "I97" with my own column letter and final row number. Otherwise I typed what you have above, and then pressed Alt+Enter. Is that correct? I should just mention that I repeated this whole process for the second date colomn, and called the original "SKIP2". The other thing I want to confirm with you is that, in the copy table wizard where for each field you have to specify data types, for each of the two date columns I created in the spreadsheet as ISO strings, I specified those columns as of type "timestamp". Was that the correct field type? As far as I understood, this was correct rather than "date", since the MS Access field contains both date and time. 2. The second place where the results deviated from what was expected, is where you wrote: "When you finish the wizard, you'll probably get some error. Don't bother, continue. Your table will be blank, since there is no primary key yet. Choose "Edit" from the new tables context menu and "Primary Key" from the context menu of your ID field." First, although there was no primary key yet, still I did not get any error. (It was exciting for me not to get it actually, as this was the first time in all my attempts to do this wizard, where I didn't get an error at this stage.) The table was created, and it opens just fine. But I could not find where you indicated "Choose "Edit" from the new tables context menu and "Primary Key" from the context menu of your ID field." When the table is opened, under the "edit" drop-down menu there is no option for primary key. And right-clicking on the ID field does not give any option for primary key designation, either. 3. The last thing is this table that is created, still seems to be "read-only". All the various options for editing and managing the table, are grayed out. But perhaps this is due to the above described problem #2, that there is no primary key. Thanks for all your help, Swarup --------------------------- Andreas' How-To: >From what I've read so far you are running Ubuntu and you can connect to the mdb in read-only mode. If you do so, you get a Base file (*.odb) and when you hit F4 in Writer or Calc you see your mdb as datasource with it's tables. If your mdb does not show up in the datasource window, call Tool>Options>Base>Databases and register the odb file. Open the target database or create a blank new one. I'll use a spreadsheet as "mediator" and assume the following preconditions: - Your Access tables are small. They have up to 256 columns and up to 65536 rows (including one row of field names). - Your Access tables have primary keys and you want to recreate the relations in the target database. - Your tables include dates, times and date/times. The import wizzard has some problems with dates, which can be solved with the help of a spreadsheet. - You have noticed the names of tables, fields, their data types and if they allow null values. It is important that you use identical data types for all related fields across tables. Access has some report tool, but a pencil and paper record should do as well. OK, File>New>Spreadsheet Get the datasource window (F4), browse to your mdb-datasource, and pick a first table, which should include some date/time, and drag the table's icon from the left pane of the datasource window to the top-left cell of your blank spreadsheet. You get a data copy in the sheet. My date/time field is named "GMT" and it occupies column "I" of the spreadsheet. Cell I1 has the "GMT" header and the cells below show (German) date/times like "29.05.02 13:30". Get the first unused column (let's say "K") enter field name GMT in K1, change the original header to "SKIP" or something, select K2 and hit Ctrl+Shift+End this highlights the entire used range from K2 until the end of the list. The input focus is in the last cell (K97 in my case). Now type =TEXT(I97;"YYYY-MM-DD HH:MM:SS") and finish the input with Alt+Enter. This converts the numeric value in column "I" of this row (97) to a text-value, representing the ISO-date. Alt+Enter puts the formula into all highlighted cells. "2002-05-29 13:30:00". If you don't get the desired ISO-date, your office operates with a non-english locale. Call "Format>Cells..." and have a look at the pre-defined date/time formats on tab "Numbers". With my German office I've got to use =TEXT(I97;"JJJJ-MM-TT HH:MM:SS") instead of =TEXT(I97;"YYYY-MM-DD HH:MM:SS"). How to change column order (let's say move "K" to "B"): Click the grey header of "B" and call "Insert Column" from the context menu. You get a blank column "B" inserted, subsequent columns move to the right. Select column "L" (which was "K" before insertion) and cut (Ctrl+X) Select column "B" and paste (Ctrl+V) Select "L" again and call "Delete Columns". This works with more than one adjacent column as well. May be a good point to save the spreadsheet, just in case ... Now copy the entire used range into clipboard: Ctrl+Pos1, Ctrl+Shift+End, Ctrl+C Activate your target database, select the tables container, right-click into the white space and choose "Paste...". The following wizzard lets you specify a table name. Choose option "Definition and data". Don't check option "Create primary key", since you want to reuse your original IDs. In the next step click ">>" in order to choose all fields, select the original date-time field we have labeled "SKIP" and put it back to the left side. It may cause trouble. We use the converted ISO-strings instead. The next step is crucial. For each field you have to specify data types, and if null values should be allowed. You can also change the field names. Make shure that all types match with the types of fields you want to create relations for. When you finish the wizzard, you'll propably get some error. Don't bother, continue. Your table will be blank, since there is no primary key yet. Choose "Edit" from the new tables context menu and "Primary Key" from the context menu of your ID field. You may define additional indices at this point. Still having the data in the clipboard, close the saved table and call again "Paste" from it's context menu. Repeat the import with option "append data" and the right table name. In the second step uncheck the "SKIP" field and move it to the end of the list, so you have the corresponding fields side by side. Now your data should import well. Don't they? Use other sheets of the same spreadsheet document or new documents for the other tables. Once you have imported all your tables, you should create more indices (if required) and restore the relations. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
