Importing Excel

[Spirodoc]

I am trying to import data from an Excel spreadsheet. The data headers are set out in the first two rows and the data is stacked two rows at a time. Is there a way to convert the data to import into matched fileds in FMP?

[Vaughan]

This is a really crappy way of organising your data in Excel, and that's why you cannot inport it into FileMaker Pro (or any other application for that matter).

The only method I can think of is to import into an intermediate file. This would have two tables, one for each row of data -- odd and even. Import into the odd table, then delete every even record. Import into the even table, then delete every odd record.

For the import into the main table, you'll have to import from the odd rows table, creating a new record for each, then import/update these records with the data form the even row table.

But like I said up front, all of this mucking around would disappear of you structured your Excel database correctly in the first place.

[Spirodoc]

Thank you! I didn't set up the Excel file and agree with your assessment of it's structure. Is there a way to "script" the intermediate files? Then the proces would be less labour intensive.

[LaRetta]

But like I said up front, all of this mucking around would disappear of you structured your Excel database correctly in the first place.

Usually data coming INTO FileMaker is NOT our choice. I receive telephone logs (from phone companies), receipts (from online services) and several other sources with the data in these formats, ie, first three rows are headers or double lines per record. One such report comes as pdf only. Good grief. I question the mentality of those developers; surely they understand someone on the other end will need to pull that data into their systems. I think we need to just get very good at parsing these messes. Until all Developers have a brain, the problem will continue.

Hi Spirodoc, the answer is yes. But it will be specific to your row/fields and requires an intermediate file as Vaughan mentioned. And if the Excel spreadsheet changes, you will need to re-script the import/parse of it. If you can post an example to work with, we can help you.

LaRetta :wink2:

[Spirodoc]

Thanks! I have attached representative files for you to look at. The excel is set up in the format that I get from a softeware program I use but is not designed for function to allow assessment of data. The FMP program is a table with two copies which would be "intermediate". Appreciate the help!!

Archive.zip