MacNN Forums (
-   Applications (
-   -   Can FileMaker Pro help me with my excel files? (

mindwaves Aug 3, 2013 06:26 PM
Can FileMaker Pro help me with my excel files?
I have a small business that has a lot of data in the form of Excel files including customer name, address, company name.

Adding new customers and when they came to the office is very important as some of them come multiple times a week. Can FM do this?

I need to keep track of who paid for their fees and who did not. I also need to keep track of additional customers and the ability to print out invoices for the new customers. Can FM do this directly without me editing the excel file and FM just acting as the front for the Excel file? Can FM actually change the excel file to populate the excel file with new information?
turtle777 Aug 3, 2013 08:19 PM
I don't think FM nor Excel are a good solution.

What you need is some kind of accounting software like Quickbooks that takes care of customer data, invoices, accounts receivables etc.

mindwaves Aug 4, 2013 12:11 AM
Thanks, I never thought about QB until you told me. I downloaded the trial version and read some forums and discovered that it cannot directly import Excel files. It must be converted into some weird format first, which there is no Mac tool to do so. So it is a no go for me.
mindwaves Aug 4, 2013 12:38 AM
Any other suggestions? Importing Excel files is an absolute must.
turtle777 Aug 4, 2013 03:16 AM
I'm having a hard time believing that.
You might have to export Excel into CVS or some other text format first, but there's gotta be a way to import.

Well, I guess the question is what KIND of data you want to import.

Customer names, addresses, contact info ? That shouldn't be a problem.
Customer balances and open invoices is probably a different matter.

I'll play around with my Quickbooks to find out.

turtle777 Aug 4, 2013 03:17 AM
mindwaves Aug 4, 2013 09:05 AM
Thanks, but if I am not mistaken, that only applies to the Windows version and not the Mac version 2013. The Mac version seems to be missing an option to import native Excel files.
turtle777 Aug 4, 2013 10:30 AM
You might be right, I'm not using the Mac version.

But perhaps you could use a Windows Trial to import, and then use the Mac version to open the Quickbooks file created with Windows.

However, it sounds like creating am IIF file is not magic with Excel, as long as you understand the format.

Import lists into QuickBooks with IIF - Little Square - Intuit QuickBooks for Mac

--> Vendor list (VEND)

The only thing that's not specified clearly is which of the text files you should use when saving the file from Excel.
But since there's only a few choices (Tab delimited, Comma Separated, Windows Formatted Text), some trial and error shouldn't take too long.

mindwaves Aug 5, 2013 12:11 AM
I have read that creating IIF files is extremely tedious, and where I have tens of Excel files some with thousands of entries, manually creating IIF files is definitely not an option for me.

The non-ability to import Excel files in the Mac version is very stupid. I bet it hurts their sales quite a bit.
turtle777 Aug 5, 2013 02:34 AM
You think it's easier to program a FM database that interacts with Excel and keeps track of customer billings, open receivables etc, as compared to exporting data from Excel ?

Do you buy a new car every time you need an oil change ?

I really don't understand the issue. It doesn't matter if you have 10 customers or 10,000.
If you have the data in an organized manner in Excel (which I assume you have), you would create a Pivot Table off of the data, bring it into the IIS format, copy & paste it into a new Excel file, and export as a text file.

This shouldn't take more than 30 minutes. Add 5 minutes to figure out the right export text format. That should be it.

I'm 100% positive that this is WAY faster than what you had originally in mind.

turtle777 Aug 5, 2013 02:38 AM
Quote, Originally Posted by mindwaves (Post 4241274)
The non-ability to import Excel files in the Mac version is very stupid. I bet it hurts their sales quite a bit.
I think you have to stop saying that. It's completely besides the point.

Even IF Quickbooks for Mac was able to read Excel files, you would still have to bring the Excel data in the right structure.
Do you think Quickbooks can just magically guess how your data is organized, and magically piece things together ?

The difference between saving a IIF text file and and Excel file is literally 1 minute.
All the other work (making sure the data is in the fields that Quickbooks expects to find, putting the right headers in the columns) would have to be done anyways.

turtle777 Aug 5, 2013 02:41 AM
Oh, one more thing.

You talk about "a business". What system are they using to do their bookkeeping / accounting ?
Please don't tell me they have thousands of customers, but use no bookkeeping software.

mindwaves Aug 5, 2013 04:39 AM
Of course, I don't expect QBs to magically guess how my data is organized, but if it is anything like the FileMaker Pro demo version I downloaded, I expect it to ask me which columns/rows correspond to what just like FM. Of course, I never used bookkeeping software before....

From what I gathered online, creating an IIF file is extremely tedious so I am hesitant to create such files manually. Many people were complaining online about creating IIF files.

There is currently no bookkeeping/accounting software being used. It is a small business. All of the data is in Excel files and encompasses hundreds (not thousands of users). The data was formerly managed by a retired accountant (again just using Excel).
Waragainstsleep Aug 5, 2013 05:06 AM
FMP can do what you want, except you'd be looking to import the excel file once and then let FMP take over after that.

It takes some learning, but its a powerful tool and unless you want to do things which are much more complex than you describe, its not that complicated.
turtle777 Aug 5, 2013 11:43 AM
Ok, so here's the thing: the issue is NOT that QB can't import Excel files, it can if the file is saved in the IIF format.

The issue is that Quickbooks does not have a data import wizard (like FMP apparently has). I actually doubt that QB for Windows has that, so in that regard, QB just isn't as comfortable to use when importing data as compared to FMP.

This is hardly surprising - a database software should have better import features, because it's a standard task. Accounting software, on the other hand, is not optimized for importing, because that's a fairly unusual thing. Migrating accounting data from one system to another is not trivial, therefore, considerable thought has to be put in it, and with it comes some work to organize the data upfront.

I maintain that you are being mislead by the statement "creating an IIF file is extremely tedious."

I don't know how to make it much more clear: organizaing your data in Excel and exporting to IIF will guaranteed be less work than trying to set up a functioning FMP database that not only handles customer contact information, but also keeps track of billing data, creates invoices and keeps track of receivables.

Moreover, if you don't use a bookeeping software yet, you absolutely need to think about this for the future.
How do intend to you run a succesful and expanding business without proper reports like Profit & Loss statements, statements of Cash Flow, Balance Sheets, Accounts Receivables Aging etc... ?
Even if you are going to get help from an accountant, please, for your accountants sake, start using a standardized bookkeeping software like QB.

All times are GMT -4. The time now is 05:09 PM.

Copyright © 2005-2007 MacNN. All rights reserved.
Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2017, vBulletin Solutions, Inc.

Content Relevant URLs by vBSEO 3.3.2