Importing AXF files

[christoff]

Hi

I was wondering if anyone has heard of AXF files. We receive them by email every quarter and i have to open them in excel and spend a few hours to get the excel file into an 'importable' state.

If this is a very rare file then I'm guessing Filemaker won't be able to import AXF files anytime in the near future?

[Lee Smith]

Nope. Is this a file format from ACT 2 by chance? If so, I have had some experience with the DBF (if I remember it right).

However, most of the time the originator has options for export. If you can get it as text, or merge text, as Tab Delimited or CSV, you might have an easier job of manipulating the file for import.

To verify what file formats that FileMaker can work with, do a search for File Formats in the Online Help.

HTH

Lee

Edited June 3, 2009 by Guest

[christoff]

Hi Lee,

I'm not really sure of the program which produces this sort of file - I should ask them. I've been told it's quite an old program though. When I open the AXF file up in excel, the text import wizard starts up and I select 'delimited' etc and then I'm in a position to manipulate what was sent to me.

I was thinking of either finding a series of free macros to make help me manipulate the excel file then importing it...but after reading your post, maybe I can do a script in Filemaker that can do the work for me...will go check it out

thanks

[Lee Smith]

There are many, many threads on the Forum about importing text. You will find several help hints. Some of us prefer to use FileMaker and others like me prefer a Good Text Editor like BBEdit, or TextWrangler on the Mac. There are a few on the Windows platform, and as long as it has ability to use has Grep Patterns, and RegEx it can be amazing in how it will prepare your files for import.

If you want to attach a sample of the file you are working with, I would be glad to take a look and give you some pointers.

Lee

Search words would be Extract or Parse and text. For the best return, use the pluses and a variation of the words like

+extract +Text

+Pars +text

+Parse +text

+Parsing +text

Edited June 3, 2009 by Guest
for got info for the search

[christoff]

Hi Lee,

Thanks again for your time, I really appreciate it.

I've never heard of Grep Patterns or RegEx so I think I better do some reading! Are these text editors doing a similar thing to me opening up an AXF statement in Excel and manipulating the data manually?

I'd love to attach a file but I'm not allowed to unfortunately due to confidential info etc but I'll ask for a sample AXF statement and post it...

I'll also read thru some old posts and see what other people have said on this topic.

[Lee Smith]

Buy a used book on Regex (Regular Expressions) The is the one I bought Mastering Regular Expressions. I did a quick check a Amazon, and found it to have a few at bargain prices.

The BBEdit and TextWrangler are products of BareBones Software, and they have Lists which was really helpful to me. I also search the Web for Regular Expressions sites, and found a lot like Link

The Text Application for Windows might also have a list, but I've never checked.

I worked through any samples that I came accross. In fact I created a FileMaker file to hold them (text fields) so that I could see the before and after of each sample.

AND, there are a couple of us on this Forum that will help you figure it things out too.

Lee

Edited June 5, 2009 by Guest

[christoff]

Hi Lee

I just received a sample file, which I haven't looked at yet, but hoping it will show you the sort of data I've been receiving...

sample_AXF_file_for_FM_Forum.zip

[Lee Smith]

Your file is a csv (comma-Separated Text Format) Windows, and will open in Excel or a Text Editor. FileMaker didn't like the file, and wouldn't open it direct. There are a few different file formats that FileMaker can work with. Take a look up [color:blue]Supported import/export file formats in your online Help. and see if there any other exports that you can try with FileMaker. Also, you might be able to export in something besides the csv format, such as merge, which would include the fields too.

If you have a file you have been working with, it would be helpful to me to see your structure, i.e. field names, and TOs.

Since you can open it in Excel, you might be able to manipulate it in Excel and then save it as a Tab Deliminated File for import into FileMaker Pro.

Lee

Edited June 5, 2009 by Guest

[christoff]

Yes I think I mentioned in a post a few posts back that I have been opening it in excel but I think I've been going about it the wrong way.

What I've been doing is spending a long time getting the excel file into an importable condition and then doing an easy import into Filemaker.

But it occured to me after I received your first response that I should be opening these AXF files in Excel, then importing them straight away into Filemaker and then get Filemaker to do all the tedious work with a script to get the data into all the correct fields etc...

[comment]

What extension do these files have when you receive them? Your sample has none - but if you add ".csv" to its name, you can then import it directly into Filemaker (see attached).

The rest is a matter of knowing what needs to go where.

Archive.zip

[christoff]

Yes, I'm sitting here contemplating if I should actually feel a bit embarrassed...

I looked at your attachment and then I went testing a few things out and even the sample file that I attached (which had an .axf extension when emailed to me this arvo) seems to be importing no worries. This means I don't even need Excel to be the middle man at all.

I can't explain why I was having trouble before. Totally weird. Looks like this long weekend that's just started may be what I need to ponder this further.

Thanks to both of you for helping me see that I think I've gone a bit mental, but hopefully will recover...

[Lee Smith]

if you add ".csv" to its name.

I knew I was missing something.

Lee

[christoff]

I spent about 20 hours on the weekend writing 2 scripts, but now that I've done it, it imports these various files perfectly.

Takes 37 seconds to do what it used to take me about 4 hours to do!