Jump to content
Mary Ann

Import from Excel

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Techphan

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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...:o

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use