Importing Excel Data into my Database

[TaiChi56]

I built a database and would like to import the data so I do not have to type everything out. The data is on an Excel spreadsheet. There are 816 rows and 16 columns. Is there a way to import this into the fields I made in FileMaker Pro? Would I have to ensure the Excel column headers are the same names I have in the database? How would I be able to do this, can it be done?

[LaRetta]

You can import directly from Excel. Be sure the first row of your Excel spreadsheet contains the field names. They do NOT have to match exactly but should be clear enough to tell you which to map to which. If you CAN get them exact, you can click the option to auto-match on 'matching field names' in FM's Import Dialog but be sure to scroll and check each one carefully to be sure they matched correctly. If not, you can drag items in the right-pane up or down (by grabbing the up/down arrow) to match them; then click the line between them until it turns into an arrow.

Be on an FM layout of the table you wish to import into. Open Import Dialog. At bottom, select Excel (xls) and find your Excel file. Select the proper worksheet. Use the 'Add Records' radio button. Map your fields according to the left-pane record 1 (which will show the Excel field names). Then click 'Do not Import first record. Contains field names.' Back up before performing any mass data change such as this one.

NOTE: If any of the cells in Excel are calculations, they will not import. You must select that entire column and change it to it's data result (copy entire column, then Edit > Paste Special and Values, I believe).

LaRetta

[TaiChi56]

Awesome. Thank you. That worked perfectly. Boy I like this database program much better than Microsoft Access. Thank you for the quick response. I imported 883 rows with 16 columns of information. After ensuring the fields matched, it took just a couple of seconds for it to transfer. You were a great help. This is a great forum.

[albud1962]

I tried using access the other day, what an ordeal. I have been an intermittent user of filemaker pro for about ten years and haven't found anything better! Its the most office friendly database program ever written. Just glad it was ported over to windows.

[TaiChi56]

Yes I agree with you. I love FileMaker Pro. Much better than Access. I tried Access but came back to Pro. This is also a great forum.

[saurya_s]

Hi all

I must tell this is the first time I am working with FMP.

I have exported an excel file which has contact address of many persons. However, each field/(column in excel) is like Title,name,surname,address etc. I was wondering can I merge all of them into one field as address.

Thanks for much needed help.

S

[Fenton]

If that data is also in separate columns, then ignore the "concatenated" column when you import. You can easily either create the "address block" on a FileMaker layout for printing, simply by using Insert Merge Field, choosing the fields. Or, if some conditional testing is needed (and it often is) you can create a calculation field:

Case ( not IsEmpty (Title); Title & " ") & First Name & " " & Last Name & ¶ & Address

I would set the Storage Options to "Do not store", as otherwise these calculations bloat your file for little reason.

If that is the only place the data exists in Excel, then you can bring it in as is, but create an auto-enter calculation, with the "Do not replace" checkmark off; to change the text to the format you want. Or, better, pull the data out into proper separate fields. It won't be a pretty calculation(s) however, so I'll pass until you say it must be done.

[Joska]

Using DDE commands you can import everything.

best regards

joska