Importing from Excel to existing table in FP11

[pedroj012]

Hey I just started a new data entry job where we take a lot of data from SEC filings and put them in our tables in FP.

This is an example of one of the tables

http://www.sec.gov/Archives/edgar/data/200406/000095012311025772/y89086def14a.htm#Y89086144

The tables we are posting to already have the Director names listed, so we would just need the other data to be put in.

So I know these htm tables can't be imported directly into FP, but I pasted the table into excel, and then was planning on importing that into FP.

Since I'm just a lowly data entry worker, I asked the database admin. if it was okay to do this, but she was very worried about formatting issues. Apparently the values need to be just numbers, dates need time stamps and whatnot, and I'm not really sure what else.... I assume the cells in excel are formatted in various ways. The issue according to her is that we write the data directly to the database, so it needs to be properly formatted....I don't know if that will make sense, but that's what I was told.

So my question is, would formatting be a problem, and is there a way to remove the formatting from excel before importing the file? I noticed the file could be saved as csv. Would that do the trick?

Also, is there an easier way to do this? Obviously I don't know a whole lot about FP or importing tables, so maybe there is a faster way to get all of this done. It is very frustrating to copy the same data from one table directly into the same freaking table in FP.

Thanks for any help.

[comment]

In general, Excel is not the best intermediate format - it often changes the pasted data type to what it deems most appropriate, and that can be a pain to deal with.

OTOH, Excel can not only clean up the data and prepare it for importing into Filemaker - it can also be programmed to fetch it, thus saving the tedious and error-prone manual copy/paste/clean routine.

There are other options as well. First of all, you should inquire at the SEC if they can return the requested data in XML format. If so, you can import the response directly into Filemaker - given a suitable XSLT stylesheet.

I will mention the others after you update your profile to reflect your version and OS.

[pedroj012]

Hey thanks for the response.

The SEC just hosts the documents. The documents come from the various thousands of publicly traded companies....I think unless the sec required the companies to provide them, the xml format deal isn't an option.

It's filemaker pro 11.03 I think. I can check on monday.

I like the idea of having excel fetch everything. I will definitely look into that. Does that have a name? Automatic import or something like that?

[comment]

I believe in Excel it's called a web query (I don't use Excel myself).

The SEC just hosts the documents.

I'd think they hold the filings as structured data in their database. Thus they can format it any way they wish on the way out. It's worth asking, IMHO, since it is the shortest path.

[pedroj012]

Hey, I emailed the SEC, but so far no response. I still doubt they have anything to do with how the data is stored. Some of the files that these tables are in are just straight up txt files, not even htm. And the documents aren't just tables; it's a whole report on what will be discussed at the annual meeting...so there's really no reason for the sec to have just the tables from these files in a separate xml format or whatever it is.

Anyway, as of now I'm just copying and pasting individual numbers from the proxy tables into the filemaker tables (I'd originally been told this wouldn't work because of commas and formatting, but it seems to work fine), but obviously that takes a lot longer than importing the whole table.

I finally got through to the computer guy at our work, and he said he actually worked on the problem before giving up. What stumped him was that excel displays a lot of the htm tables differently than they show up on the website, because it doesn't ignore empty cells, and some other reasons...A lot of the times these tables will have a bunch of empty rows + columns when pasted into excel - and the data won't line up very well with the column/row headers. Obviously that kind of stuff can be cleaned up, but at that point it could be faster to just copy and past individual values.

Is there some other intermediate program that could somehow make this work? What are the other methods you alluded to?

Thanks.

[comment]

there's really no reason for the sec to have just the tables from these files in a separate xml format or whatever it is.

That's not what I meant. They don't need to keep tables or anything else in a separate format. They only need to keep ALL of their data in a structured format (a database, in other words). Then they can format the response to a query as XML - or anything else, e.g. a RSS feed, HTML, Excel, text, etc.

What stumped him was that excel displays a lot of the htm tables differently than they show up on the website, because it doesn't ignore empty cells, and some other reasons.

I don't know about that. It works quite well for me in OpenOffice - but I haven't tried it with your data source.

What are the other methods you alluded to?

On a Mac, you could use AppleScript to fetch the data (via cURL), then parse it yourself before passing it to Filemaker. I suppose you could do something similar in Windows with VBS, but I don't know for sure.

Another option would be to find a mash-up service on the web that could extract the data for you.

Finally, you could load the document into web viewer and parse it from there, though that could be quite tedious. It all depends on how much you want to put into this.