Import CSV files into Base tables?

[Hemo2]

I apologize for posting what I believe has been discussed somewhat already. I'm new to Open Office and can't figure out something that I'm hoping will have an easy answer. I'm a novice at this stuff, but I've got a baseball stats database in MS Access. With the release of Base in Open Office 2, I thought it might be interesting to see if I could get a similiar database setup in Base. The data for the baseball database came in the form of several CSV files. In MS Access, I created a table for each of these files setting up the fields accordingly for each table and then simply imported the CSV files into the appropriate table. However, in Base I have created the first table and setup the fields the way they should be but I see no way to import the CSV files into this table. I feel like an idiot that I can't figure out how to do this in Base so I figure I better ask for help. Is there a way to simply import the info from these CSV files into the blank tables I've created in Base?

I've searched on the forum for some answers and have seen reference to linking to text files, but this isn't what I would want to do. I also saw some info on putting the info into Calc and then dragging it to a table but this didn't seem to work for me on the one I tried plus some of the other CSV files have too many rows in them and Calc fails to open them so I don't think I could do it through this route.

Many thanks

Keith

[DrewJensen]

Hemo2,

OK...Base just doesn't have a general purpose import export facility at the moment. So if you have your data in CSV files and you want it in a "native" Base format it is going to be a two step process.

You can choose which two steps..but it is two.

Way one. Create a "native" Base database (HSQLDB Embedded engine). In this database you would have to use a DDL command to create a TEXT table. Really not that hard, the documentation at www.hsqldb.org is quite clear on how to do this. If you go this route and have questions ask I can help. Anyway, when you do this then you can just use a Select INTO command to move the data from the TEXT table into a HSQLDB native table. It really is the easiest way.

The second way is to create two databases, one where the database type is TEXT. (This maps to a directory and all the CSV files will come up as tables) The other will be a standard Base database. Now you need to create a ooBasic procedure that opens both databases and again moves the data from the text database to the 'native' databse.

A third way is one you mentioned. Open the CSV file in Calc, and then drag the data over to a Base database in table view. You can not drop the data into an existing table, rather you must import it to a new table. When you do this a wizard will open allowing you to map column types and the like. There are plenty of threads on the board covering transfering data from Calc to Base. Look especially at ones that cover dates ,as this can be a hassel.

Personally, I can tell you that the first way is actually the best. A little more work getting it set up, but it pays off with the control you get. Then the final step is just one simple select statement.

I HTH

Drew Jensen

Boy you gotta love those Astros... I say they do it in....4

[Hemo2]

Thank you for the reply Drew. It's good to know the Base can't do an import of a CSV file so I can stop scratching my head why I couldn't figure it out!

I think I understand what you're saying on what needs to be done to accomplish this, but I don't know that I have a good handle on how to do it yet. I'll take a look at the www.hsqldb.org website and see if the helps me out.

Yes, the Astros surprised me by bouncing back that well after loosing game 5 the way they did. It could be an interesting series. Both teams have very good pitching so it's hard to tell what way that series will go. If you are into baseball stats, etc. the database I'm referring to is available for anyone to download and use. It's quite a nice project done by some very dedicated baseball fans. They're constantly working through various things trying to improve it. It can be found at:

http://www.baseball-databank.org/

They have CSV versions of all the tables along with the table definitions. They also have a mySQL version of it available for download. I'm 'slightly' able to do things in my MS Access 2000 so I imported the CSV version of all the tables into Access and managed to create various queries, reports, and forms for me to use for browsing & viewing the baseball stats throughout baseball history. It's nothing special, but it's functional. I thought it might be kind of cool to attempt something in Open Office Base since it is free and anyone that doesn't have MS Access would have a route available to them to enjoy this project. I admit I'm a novice at a lot of this and know nothing of the capabilities of Base so I'm not sure even if I get things into Base that I'll be able to get a similiar setup in Base like I do in Access and may have to drop this idea. (It's certainly not the end of the world if I can't get it done.) But, one step at a time and step one is to get the data into Base.

Keith

[DrewJensen]

Hemo2,

I'll see if I can't find a few minutes to grab the csv files from the site and put together an example of how to do the import.

Drew

[Keith Silva]

An evil fourth way is to open the text file in MS Excel. Excel is very good about converting such a file to the Excel file format. Once converted, it can be opened and used in calc. I'm also trying to import a csv file into Base and haven't quite got there yet.

Regards, The other Keith

[DrewJensen]

I downloaded the files for 'the american passtime stats', I'll put together a litle demo of bringing them into a base file - sans calc this weekend, and post it to this thread.

Drew

[Hemo2]

Many thanks Drew. As I recall, the 'batting' and 'fielding' tables have many more rows than Base (or Excel) allows in a single sheet. I hope it's not too much of a bother for you to look into this because I'm still not sure I'll be able to accomplish what I want in Base. But I enjoy doing these types of things and am always open to learning more.

Keith

[DrewJensen]

Hemo,

Here is a little help on this.

Download the zip file with...suppose you have already done this.

Now create a standard Base database, be sure to save it in the same directory as the *.sql and *.txt files from the zip file.

OK, because they supplied the table definitions - albeit in MySQL dialect, we can alter this to HSQLDB dialect and create an import script.

[DrewJensen]

Ok, so let's look at these last three tables.

But before we do -

I see an error - I forgot to move the FieldingOF table in last time - so copy this into the sql window and exectue it

[Hemo2]

Wow Drew! You've gone above and beyond with your reply! I'm going through it all now and even though I don't necessarily understand some of the statements or syntax, I understand enough to see what you are doing and why. I understand what you said about the larger tables and not being able to use the script to bring them in. I imagine there isn't a 'magic size' for this and a person has to simply try it with the scripting code first and if it fails, then do it the manual way you explain.

You've put a lot of time into replying to me and I can't thank you enough! Your reply is very detailed and I "think" I can follow your example of how to do this for future reference by using scripting. If not, I think I can at least get it done the manual way. Again, thank you for helping with this.

I did notice a couple of things that I see that look different to me and since I don't know any better, I thought I'd ask about them.

In the tables "AllStar", "AwardsVotes", "AwardsWinners" where you are creating the text & non-text tables for each, you have a statement of: "PRIMARY KEY ("fieldname") but this statement is missing from the other tables getting created. I'm guessing, but Is this Primary Key statement supposed to be there for these 3 tables?

For the "ManagersHalf" table, when you get to your CREATE INDEX statement, it is being done ON "ManagersHalf_TXT". The CREATE INDEX statement for all the other tables appear to not get done on the 'text' version of the table. Should the CREATE INDEX statement for the ManagersHalf table be done ON "ManagersHalf"?

The other thing I noticed was the table "Managers" didn't get created in your example code. (You've probably gotten carpal tunnel syndrome by now with typing as much as you have in your reply to me so I can understand how this table was over looked!)

I've tried to follow your example and have come up with the below for the statements to bring in the Managers table. Does this look correct?

[DrewJensen]

Before we talk about the data...the important stuff..

Darn - I said the Astros would do it in 4, just didn't think they would 'do it' this way...
I am really getting old...around 1:30 AM I stretched out on the couche...told myslef..don't do this..your gonna miss the win..and yup..missed the whole 14th inning..

You are right on both accounts, ManagersHalf was a typo (the index should of been on MangerHalf not ManagerHalf_TXT) and Manager import was a miss. Did on my machine, and somehow cut it our of the script...

As for the transactions table...here is the problem:

The second column is a date field. In HSQSLDB (and many other DB - not MySQL apparaently - although it could of been a problem in the extract routine) a valid date must have a valid year, month and day. But in the import data there are 4,100 and change records that are of the format:

1899-00-00 or 1934-04-00

I can understand that given the nature of the subject perhaps sometimes they don;t know the month or the actual day of a trade...but zeros is not acceptable to this data engine.

Anyway - I started a script that pulls these out and then imports just the 49,000+ valid records..when I managed to cause the first ever Microsoft Viisuall C++ runtime error message I have seen in OpenOffice. Which was just about the time the game was starting...so...a guy has to have priorities...

Drew

[Hemo2]

Thanks for the explanation on the transactions table Drew. It makes perfect sense now. I think for my purposes I could maybe just have that transaction date field created as a text style field and not a date field. It would be nice if it were formatted correctly as a date, but with what I would use this field for would be nothing more than to display it for information purposes so a text type field would probably work for me.

[DrewJensen]

Hemo,

No problem. Truth is I have been looking for a dataset to use for some timing test scripts. This turned out to be just the kind of thing I was looking for.

I also wanted to get a handle on importing issues - again a good data set.

First thing I'm thinking of doing is making a 'bullet' proof conversion script that automates all the steps needed and then posting this up the board the data is found on. There may be others that would want this in Base.

Drew

[DrewJensen]

Hemo,

Don't know if you have had any luck with the Transaction table just making the data column a varchar(10). I had the same idea, but the table is still too big (and wide) to bring in with one gulp - Base runs out of memory.

So either chop the file into smaller peices..or use a few lines of code to bring in chuncks of records. I am going to do the latter later tonight..

For now I am into the evening chill (freezing and snow here in the Allegany mtns today) with my nephew to watch a Halloween parade...hope they have hot apple cider for the crowd..

Drew

[Hemo2]