Hello Dinbandhu, Well, I can be very slow sometimes so if you don't mind let me try to recap.
You have an Access 97 database, correct? ( I assume this since you mentioned Win98 ) You do no have a Windows version of Base, that is only on your Ubuntu installations, correct? When you run Base under Ubuntu you are offered the option to connect to an external database of type MS Access, correct? ( I believe this means you have the community build of the OO.o application ) You can open the Access97 database directly with a Base file and see the data, but the columns are in the wrong order. ( This may be misleading. Access allows you rearrange the presentation view of the table, but this does not change the physical order of the fields in the actual table. When Base opens the table it is not aware of these presentation view settings and therefore displays the fields in the table in the natural order. At least this is what I believe is happening, but could be wrong ) There are two datetime fields in the Access database, but the copy table wizard marked them as being something else. Was it varchar by any chance? You changed these to datetime in the wizard and they came over ( those that did ) as 01/01/70 12:00 Am, which is the NOT A DATE, date for Access97. Also - you said you exported the data to a csv file. In this file is the data correct? Now, if it is you could also do this. Start Linux and create a new Base file. In the new file wizard select 'Connect to existing database' again, but this time of type TEXT. On the second screen of the wizard you select the directory where the csv file is located. When the Base file is opened the csv file will appear as a table. If you do this, is the data correct? One final thing - If this is test data, or data that is not sensitive you could email me the file and I can try to import them to Base under a Windows version of OO.o. If there is no problem then I'll send the Base file back. Meanwhile I'll wait on your answer. I am going to be up and online for a little while still. On 8/30/07, Dinbandhu <[EMAIL PROTECTED]> wrote: > > Drew, > > I tried hard for almost two hours to import a table using the copy table > wizard, but could not get success. I went over your tips (below) > carefully, and incorporated all the points relevant to my particular > table. > > My table has 23 columns, all of type 'text' except for four: the ID > which is AutoNumber (in MSA terminology), two of type "date/time", and > one of type "yes/no". And that table has 40 rows. > > So I carefully went through the type fields when the copy table wizard > would get to that window. > > For the ID field, according to your below recommendation I set it by > hand. It was already set to BIGINT, but I changed it to something else > and then brought it back to BIGINT. By so doing, the number of digits > allowed went up from 4, to 19. I do not know whether I was able to set > it the way you wanted, but this was the best way I could figure out to > increase the number of permitted digits. You had written to be sure to > set it by hand, and then added that "AutoIncrement in Access is always a > LONG INTEGER". There didn't seem to be any option in BASE for selecting > "LONG INTEGER", so I did what I have described above. > > For the two "date/time" fields, BASE had selected something else other > than "date/time"-- it had selecting something obviously inappropriate. > So I changed both to the "date/time" type. > > And the "yes/no" column was also incorrectly set. So I changed it to the > "yes/no" (boolean) option. > > All the remaining were text fields, appropriately designated so by BASE, > with "var char". But I went through each and made the number of allowed > characters "255". > > After making the above-noted changes, upon clicking the "finish" button, > the wizard still came back with the message that there was an error, and > would I like to continue writing the table anyway. I answered yes. This > time, unlike earlier, the table is not empty. It filled in five rows. > But the data is wrong. For example, in the "date" column, all five cells > have the same date in them (01/01/70 12:00 AM)-- which is wrong. I do > not have any such date in the table. > > Also, the order of the columns is different from the actual MSA table in > my windows partition. But that error seems to stem from the way BASE's > "connected" table appears. In that table--the one BASE made in the > "connect" mode--the data appears correct, but the order of the columns > in the table is all wrong. And that incorrect order is reflected in the > order of columns in the table made by the copy wizard. > > So if you have any tips/suggestions as to how to make this import > proper, please kindly let me know. > > Otherwise I am thinking I may get more success by using the .csv method > for doing the import. I have already done the export from MSA into > a .csv format, and would just need instruction as to how to import > that .csv table into linux BASE. > > Thanks for all your help, > Regards, > Swarup > > > > On Thu, 2007-08-30 at 07:20 -0400, Andrew Jensen wrote: > > Hi Dinbandhu, > > > > I've been at the msa -> base conversion page for a couple of hours ( > still > > on my machine, so don't go look as I have only been using StarOffice / > now > > need to try OpenOffice ). > > > > I just went back and read over this thread again and a couple of > comments to > > you however, > > > > 1) There is no such thing as a date only field in MS Access. You may > have a > > column defined as a date in Access, and you may never have added a time > > value but internally they are only date/time fields. > > > > 2) Be sure that if you have any Auto Increment fields in Access that you > set > > the field types of BIGINT in Base, by hand. AutoIncrement in Access is > > always a LONG INTEGER. > > > > 3) Currency fields. In Base be sure to add decimal places - by hand. > Base > > copy table dialog selects numeric with 0 decimal places. Any decimal > values > > in the source are lost in the transfer if you don't make the change. > > > > 4) TEXT fields in Access. Always set these to varchar( 255 ) > > > > One final comment for now ( and if Marc reads this I believe he will > laugh ) > > the best way to get a handle on what the data is going to look like > coming > > over from Access is to use Calc. > > > > Assuming your Base file that connects to your mdb file is registered > then > > all you do is create a new Calc file. Open the datasource window ( F4 ), > > select your Base file and open it. Open the table item and then drag one > of > > the tables onto the sheet. > > > > In the case of your date fields you will see that every row where you > > entered only a date in Acesss a date and a time of 00:00 ( no seconds ). > > > > Currency fields in Access, when there is a negative value. Bringing the > data > > to Calc had no problems - even formatted the darn things as currency, > on > > it's own. > > > > You asked if the import "By my question I was just trying to get a sense > of > > how good Base's > > import wizard is. i.e. does it give most people trouble? Or is rather my > > data giving it trouble? " > > > > >From my experience, and I have helped perhaps a few dozen people > convert the > > data in some way. If you are under MS Windows no problems, a hicup > maybe, > > but not a real problem. Under Linux - not so good. > > > > > > > > > > On 8/30/07, Dinbandhu <[EMAIL PROTECTED]> wrote: > > > > > > On Wed, 2007-08-29 at 22:56 -0500, Barbara Duprey wrote: > > > > > > > > Dinbandhu wrote: > > > > > <snip> > > > > > Tell me, is this copy table wizard usually trouble-free? Or are > others > > > > > also having trouble with it. I just want to get a sense of whether > > > this > > > > > tool is quite finicky, or is it just an isolated sort of problem I > am > > > > > having and for most folks it goes without a hitch. > > > > > > > > > I've only recently gotten into this area myself (had a largish table > to > > > > get in from a .dbf file, then updates to pick up from Writer tables > and > > > > Calc spreadsheets). I had problems with various aspects -- the .dbf > one > > > > worked fine (and that's probably more like your Access stuff than > the > > > > other sources I used), and so did Calc, but I had some ill-behaved > data > > > > in a Writer table that violated uniqueness when used as a key, and > Base > > > > added leading and trailing blanks to text fields that made them > violate > > > > lengths when I tried to restrict the fields to their proper sizes. > (I've > > > > since found out how to strip them off again easily.) The Calc > > > > spreadsheet worked fine, too. So I'd say the success probably > depends > > > > more on the source type than anything else, and Access should be > pretty > > > > good. To get more of a feel for how frequent the problems are, you'd > > > > probably have to look at the archives of this list and the > > > > [EMAIL PROTECTED] list, or check the Issue Tracker for what > it's > > > > got. Maybe somebody else here can help more, or on the dev list > (which > > > > seems somewhat more active, and has been extremely helpful for me.) > > > > > > I see. Perhaps that would also be a good mailing list to join. > > > > > > By my question I was just trying to get a sense of how good Base's > > > import wizard is. i.e. does it give most people trouble? Or is rather > my > > > data giving it trouble? I can hardly think my data would give anyone > > > trouble, it's so simple and straightforward. > > > > > > I'm hopeful that Drew's HowTo on the wiki will help me get find what > the > > > problem is and get through this. > > > > > > > > Also, is the other method--via the cvs file--more dependable? That > is, > > > > > would you recommend that I go that route instead? If so, is there > a > > > > > simple command in MS Access for exporting a database as a cvs > file? > > > > > > > > > I haven't tried the .csv route at all, but since that's the most > basic > > > > and straightforward format of all, and serves as the most nearly > common > > > > language for data transfers, I'd expect it to be the least > troublesome. > > > > In Access, if you open a table and go to File > Export, you can set > the > > > > Save As filetype to Text Files, which includes .csv. Set the file > name > > > > to have the right extension, and Export All; that will bring up an > > > > export wizard that leads you through the rest of the process. "csv" > > > > stands for "comma separated variables" but you have some other > options > > > > for separators within that type. > > > > > > That is very helpful information-- thanks a lot. Now I'm sure I'll be > > > able to do the export from MS Access using this method. I would just > > > need some similar guidance for the import side into Base. I know you > > > haven't done this before. Perhaps either Frank or Drew could give some > > > simple step-by-step approach as you have done above for the export. > > > > > > > > P.S. Don't know whether you had a chance to take a look at the > > > > > functionality issue re: when you have simply "connected" with a > MDB, > > > > > which I had asked Frank about in my last note to him. I have > pasted it > > > > > just below. Any comments? > > > > > > > > > > > > > > >>> 1. I would like to know that if I just simply "connect" to the > MDB > > > > >>> > > > > > file > > > > > > > > > >>> and don't import it, then what sort of functionality is there? > > > > >>> > > > > >> Read/Write data. See MSA's queries as so-called views (i.e. they > > > > >> > > > > > behave > > > > > > > > > >> like tables, but you cannot edit their constituting statement). > Don't > > > > >> see forms/reports/macros. > > > > >> > > > > > > > > > > I see. That is still very good. I was just unable to find the > commands > > > > > of how to write data there. That is, the tool bar commands for > save, > > > > > edit data, cut, paste, and data input, are all grayed out. Then > there > > > > > above those are the File, Edit (all lines grayed out except > "copy"), > > > > > view, tools, window, and help options but none of them seem to > have > > > any > > > > > option to edit the edit the data, add a row, add a column. And I > can't > > > > > even put my cursor in any of the cells of the table. In fact, > there is > > > > > no cursor. In short, in my computer the table and the data in it > looks > > > > > like a picture window. You can see it, but you can't do anything > with > > > > > it. > > > > > > > > > > > > > > Not sure what's going on here, but it sounds as if the data is being > > > > considered read-only. It may be a matter of permissions or locks. > Can > > > > you update the data in Access? Do you have Access open on the > database > > > > when you're trying to work with it in Base? > > > > > > I update the data in Access all the time. No problem there. I'm not > sure > > > what you mean when you ask: > > > > > > > Do you have Access open on the database > > > > when you're trying to work with it in Base? > > > > > > How does one have Access (do you mean the program MS Access) open on a > > > database? I'm in Ubuntu Linux doing the import into Base, so of course > > > there is no program running on the Windows side. These are two > different > > > partitions of the same computer. Nothing in Windows is running when I > > > have booted to the Linux partition. Perhaps you meant something other > > > than what I understood? > > > > > > Regards, > > > Swarup > > > > > > --------------------------------------------------------------------- > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
