Best way to automate FileMaker data import when I only have the option of .tab or xls.

I haven't used .net unfortunately.  I'll definitely check it out though.  As for the tabbed format to open in Excel; that would work for the formatting--is there a way to automate it?  I'm guessing there probably is--I just haven't figured it out.  Possibly a macro running automatically upon opening Excel?  Text-to-columns won't work unless it's part of a macro.  Because of the funky date formats, I'd have to split columns & take some extra steps which keeps messing up the automated part.  That's why I was sorta leaning toward using SQL to set up the format initially--in the query.  But again, I could be way off.  I love the whole idea of going direct to the database using .net because you mentioned that it works with SAP too--that would be perfect.  Thanks for the input.  This has to be easy for some of you Excel/FileMaker gurus...please tell me how you have handled this in your past experience!  

I don't understand all of it. why do you use ODBC in between FM and excel?
Why don't you import the tab file directly into fm?
.net to read from SAP? you'd need to learn .net which is a language, so that's not a 5mn job. If SAP has an ODBC driver (it must have, it is based on major database engines), you could also read from it directly into fm.
The only thing which can't be automated is getting the file from the site. Would you have another way of getting it? ftp? to store it to a known place, then go ahead...

We do this alot in our company:

We have three different types of connections; ODBC, Direct import of excel file and Direct connection to databases that comply with Filemaaker drivers (Mysql,MSsql and others like)

We have a central box that runs Filemaker Advanced v9.0 (currently), we use a tool like activator from Troi or the free DoScript to trigger the time for updates in Filemaker. If it is direct export from excel we configure the import properties in FM and import the file then run numerous scripts to condition and clean the data.

If it is a direct connect database like MSSQL, we build a FM script that compares the MSSQL table and th local FM table and update the changes. This approach eliminates the dependency on both servers being up during operation.

If the connection is an ODBC connection we import from OBDC into the actual FM table. The biggest problem with this approach is that the FM application goes blank during the update. Where we can't afford to be down we import a local table then run a compare script to make the update live.

Hope this helps

Lesouef--You're right...I would like to get rid of of the Excel step.  The dates from the website come through in several different formats.  Also, I have to reformat several other columns (e.g. zip codes and customer #s beginning w/  0 into text), so I've just been using Excel to clean up the data before import.  Now that I think about it, it probably would make more sense to import the tab file directly into FileMaker.  What is the quickest way to clean up various date formats?  Would it have to be with a script, or is it possible to put a calculation in the corresponding fields that would reformat the data on import?  I would like to have the dates in MM/DD/YYYY; however, they are downloaded in inconsistent formats (see the following example):

DD/MM/YYYY
DD/MM/YYYY 00:00:00.0

Finally, for whatever reason, the website does not support any other format such as SQL or ftp--I have to manually retrieve the excel or tab file.  This is irritating, as this is the newly "re-designed" site for a major trade insurance company.  I understand that .net is a language and would take some time to learn; however, the majority of the imformation I download is extracted from SAP.  I was hoping to get some input from people who have had experience automating SAP to FileMaker.  The company I work for is several SAP projects behind, and I'm sure they aren't going to make room in the budget to help me set this up!

Ivaldes,

You mentioned "activator from Troi or the free DoScript to trigger the time for updates in Filemaker".  I'm checking those out now.  Do you only use these to set the update start times?  Thank you for the comments.  The data for the site I extract does not have a SQL option, and I need to do some more research as to what the possibilities for SAP are, but what you do at your company as far as the central FileMaker box is exactly what I would like to do.  I love having the ability to manipulate data in FileMaker everytime someone needs an ad hoc report.  Furthermore, it is much more cost effective than hiring a consultant to create custom ABAP reports (not to mention a nicer interface visually).  Honestly, I'd like to be more involved in SAP customization & development, but because of a stringent security policy, "they" won't extend my authorization any further.  So...that is where I stand.

I would import the data first and write a script that executes different routines to clean up each of the issues. That leaves you an open ended process to add routines as the data has future surprises, also that gives you tremendous flexibility to deal with blank lines and field type incompatibilities that you will not be able to resolve otherwise.

The best method is to use the ODBC drivers to import the records without excel and then write a script to append new records or delete missing records in your live table. Again if you can't use the data the way it comes from SAP due to field types then a script is the solution to recompute the field. This will make the import routine scalable and the data quicker. Embedding calculations on tables is not scalable for big tables

I agree with jvaldes, I would skip the excel step, and to the cleanup in fmaker, either in a temp table if very extensive work is needed or directly on the imported foundset if easy; the examples you're showing are easy.
For triggered actions, don't forget fm10 has this feature now, so better check if it can do exactly what you need, otherwise the old trick with the scheduler/cron which starts a file with a startup script to do the job also works.
So the only annoying bit for me is to download the file and save it an unattended way. For instance, servoy allows to capture the returned code from a URL, would somebody know of a fm plug-in which allows that? I'll re-check Troi's site as they have a URL plug-in or something similar...

http://www.troi.com/software/urlplugin.html
Have a test with that to get your file. It does not mention you can get the file but data, so I can imagine you'd get the data in a field, then could strip it to fill a table; probably a lot of work to strip it unless you can display a tabbed file at a given step, because stripping html is a headache and obviously they don't offer xml I guess.
Another track to test is those tiny CLI utilities which allows to download files from the web, but I must review them before suggesting anything.

There are many download managers, too many actually a real forest.
Among others, very few have a command line mode or a scheduled/unattended mode, here is what I found so far:
http://www.halogenware.com/ java app woth a cmd line mode which could be used with the windows scheduler or osX cron.
http://www.leechget.net/fr/ is a nice utility; cannot be scheduled but can run continuously and run at given intervals, so some kind of unattended mode (I made a quick read, so double check, but I like the comprehensive aspect of this app).
Using such a utility, you could get the file on local disk, then pick it up with fm.

another one:
http://www.internetdownloadmanager.com/download.html, not free, but cheap and pure command line mode.

and more:
http://www.gnu.org/software/wget/wget.html
http://windowsitpro.com/web/article/articleid/16048/the-command-line-http-client-tool.html
http://www.dfg-crew.com/
actually I had these on my disk, in a forbidden download section...
that should be enough so far. I would opt for command line tools, as you can drive them from fm even if the command completion can't be monitored: if you allow 1mn to complete, it should work.

I just tested httpcmd, not much success, while wget is dead easy to use:
wget.exe [url] --execute "output_document = [file to save]"
puts the file on disk.
I just hope you don't need an extensive dialog to get the file done and that it is available as a direct URL.
If yes, the web viewer may help you to issue the previous steps.

Wow...I really appreciate all the feedback I'm receiving--it's so helpful!  One problem...the URL is not direct.  In fact, I have to enter a username and password and then navigate to another page in order to download the file.  Will any of the programs you've suggested work in a case like this?

A few yes, but I did not make notes of that, so you may have to review them to find out which ones can do it.
Also do yout know if your site accepts user/passwd in get or post mode? (likely to be post mode which will eliinates 2/3 of them).
Bur worth asking your provider the right syntax to request the page while supplying user and passwd in the same URL in post mode, which is a quite common method.
If you type the file URL directly, does it prompt for user and passwd?

she said up there that she has no ftp unfortunately...

whoops - missed that. Thanks.

The following link leads to the login screen:

https://cofanet.coface.com/portalViewWeb/Controller.jpf?application=VC%20Subscriber

I believe they use the post mode.

passwd always use the post mode otherwise it's a clear passwd in the URL in get mode...

Some other helpful information would be:

1)  Are you on a Mac or Windows?
2)  Is the file you download from the web always with the same format and column order?
3)  Does the filename have a naming convention such that you can script the selection of the file for a given date/period?

The steps I would recommend (somewhat depending on your answers above)

a) Create a temp table just to capture the imported data (as mentioned by lesouef).
b) Create a script  to do the following
     1) Use the built-in functionality of FileMaker to open a URL.
     2) Retrieve the file.
     3) Open the file directly, importing the data into the temp table
     4) Make repairs/modifications to the data as needed
     5) Move the data from the temp table into permanent tables.

You may be able to schedule FileMaker to do this on a regular basis.

I would be glad to assist with more details as you present more details on this thread.