Import from Excel

[ivyorg]

I'm trying to import a scheduling database created by a third party into FMP so I can show on the web and have it be interactive. The problem: how to import this data that has been created in this form:

Date Bball Wrestling Squash

11/01 H@COR COL@B PR@PE

COL@B H@COR D@Y

11/03 D@B COL@PR H@Y

I want it to end up in FMP with following fields:

Date

Sport

Matchup

How can I do this w/o spending much time preparing my excel database? If it took too long, I'd just retype all by hand, and insist that the schedulers from this point on use my FMP web site to enter.

Thanks

Bevin

[birdman]

Hi Bevin,

The bad news is that I would retype everything. To convert your sample data so that it could be imported would mean numerous cut, paste and copy steps in Excel.

Essentially you would need to end up with 3 column spreadsheet (Date, Sport, Matchup) and a row for each entry. Information would have to be in each cell for each row. Depending on the number of entries you will have this could prove tedious.

[LiveOak]

There is probably a way to automate what you want. I just can't understand the data format. What is 11/01 H@COR COL@B PR@PE COL@B H@COR D@Y, etc. The date part I get, but what does the rest of the stuff mean? -bd

[ivyorg]

well, in keeping with what i thought was going to be bad news, i did in fact copy and paste all the dates for each row, although I kept separate sport headings, imported into a new fmp database and then imported into the existing scheduling database, one sport at a time. FYI, the H@B meant harvard played at brown. this method of inputing the contests was put in place long before me, and i then had to come up with calculations centered around the "@" sign to pull out the individual team names. I'm looking to have to do this all again for one more year that's already been put forth by the schedulers, until I force them to use the new web-based system set up now. Yikes.

Bevin

[BobWeaver]

The simplest way that I can think of is to import the file 3 times and change the import order each time. The first import will import the date and Bball matchup and autoenter "Bball" in the sport field. The second import will import date and wrestling matchup and autoenter "Wrestling" in the sport field. The third import will import date and the squash matchup and autoenter "Squash" in the sport field. Even if you have to import this file regularly, you can make a script to do this.

If you need to split the matchup field apart, it can be easily done with the builtin text functions.