Import from Excel

[Mary Ann]

The home office requires purchase requisitions be e-mailed in excel format. The excel form may have different data in the same column, i.e. name in cell c6 and address in c7. I have the requisition form set up in FMP9, but I understand the layout cannot be exported to Excel. The alternative is to import data from the excel purchase requisiton to FMP9 so we can keep track of budget and account balances. I have learned to do basic scripting in FMP6 but am a bit of a newbie to FMP9. How do I set up a script to import purchase request data into an FMP9 purchase requisition file?

[David Head]

Any scripted import process will rely on consistent data structure in the import file. If the data is in a different place each time I don't see how you can ever script that without human interaction.

[Mary Ann]

I'm sorry. I guess I didn't explain things quite right. The requisition is actually a form and not a report. The specific data (business name, item description, or unit cost) is always in the same cell but a column such as column A will not be all business names. Row 1 in column A is the business name and row 6 in colum A is Quantity. I guess I don't understand how the import feature works so I can get my business name, item description, unit cost, etc. from the excel form to the correct field in FMP9.

[Techphan]

Is it possible to manipulate the data in Excel (to place like items in the same column) before attemting to import into FM9?

[David Head]

A standard import to any database requires what is essentially a list. Since you are not importing from a list but rather from a form, you will need to do some reorganising. Whether that happens in Excel before you import or FileMaker Pro after you import is up to you.

 

For Excel, you could make a summary sheet that holds data from various cells in a single row.

 

For FileMaker Pro, you would import the form rows as 'records' and then extract the data from the specific rows and cells as needed.

[Skywise]

I have a similar problem (Excel as the entry form, one column used for multiple purposes) and I think my workaround may work for you too. Basically what I did was to create several hidden sheet in the Excel file. The person filling out the form never sees these sheets because I hide them before distributing the form.

 

These sheets contain what I need for importing:

- cell headings in row 1

- each column contains just one kind of data (e.g. last name)

- any keys necessary for completing table relationships are repeated in every row (whereas in the original form they may only get entered once).

 

I need several of these hidden sheets because the data I'm importing needs to flow into different tables. If your solution is very simple/focussed, you may only need one hidden sheet.

 

Hope this helps!

 

- Skywise

 

p.s. Now if I could just figure out how to tell FM9 which spreadsheet to import ONCE per file instead of once per hidden sheet...

[Skywise]

Well after looking around the forum a bit, this thread seems to be as good a place as any to hang my question.

 

As mentioned above, I have an Excel spreadsheet used as an input form. Hidden sheets with formulas bring the data into a table structure suitable for importing into FM9. Each sheet has the data needed for one of three tables that need to receive the data. A script handles the import.

 

Now here's the rub. Although the import script step is set to "no dialog" it nevertheless needs to know which spreadsheet and (of that spreadsheet) which tab is to be imported. So every time the script is run I still have to answer 6 dialog boxes (three import steps, for each one a dialog confirming the file and a second confirming which tab is to be imported). That's a lot of clicking around.

 

Is there a way to tell FM9 just once per script, which file is to be imported? I tried defining a global variable $$Spreadsheet having the necessary file path as its value and then entering the global variable in the import script step where the file path would normally go. But this merely yielded an error to the effect that the file $$Spreadsheet could not be found.

 

Is there a better way?

[HexxaBubba]

I really would like an answer to this as well. I have a XLS file that I import by variable, but you can only import first sheet. So I can do a file reference without variable, but that blows if someone repalces file or whatever. Is there a way to access sheets with variable?

[ibnalkatib]

hi 2 all

can any one here explan this problem...

i have form contain table example(1c.and 1r.)..and then want import table from excel file by click in bottom ....so who i can do that?

thx...

[Techphan]

To import an Excel file into Filemaker the Excel data MUST be in rows. Each Excel row becomes a Filemaker record after the import (and even the Excel row 1 header/label row will be imported as a record unless you delete that row in Excel before the import).