Importing Xcel File for 1 Record

[maiton.vang]

I'm trying to import a xcel file which has 5 cell info and would like those info to be imported into 1 record rather than 5 separate differen record on filemaker? Each field does have the repititation from 1 to 15 lines. So what I was hoping for is to have all 5 cell info import within the field since i have 15 available lines.

any suggestion..

[Brian C]

You need to change the format of the excel document so that your data is listed across instead of down.

Filemaker imports each row as a record and each column is a "field".

This is the only way you will get your import to work properly.

[Brian C]

In order to get your data to import into a field with multiple repetitions, you will probably have to create an intermediary FMP table to handle the import and manipulation of the data before it is then imported into the final table. This will make sure that your data is optimized the way you like it and you will not be storing unnecessary fields of data in your final table which only existed to convert the imported data.

For the intermediary import table you should create a calculation field that has repetitions. This calculation field can be set so that each of the repetitions contains the proper data from each of the 5 fields.

[Vaughan]

I've found the easiest method for imports like this is to first suck the data into a temporary table, then process it, then move it into the main table.

[maiton.vang]

What do you mean put the data intor a temporary table. Am I doing that in excel? if so, how? and how do i process the table?

Also how do we create a FMP table in filemaker pro?

Edited July 24, 2007 by Guest

[Brian C]

If you are using FMP 8 you can create new tables within the same file by going to define fields and clicking on the tables tab.

It is similar to creating additional spreadsheets within the same excel workbook document.

Rather than go into extreme detail, I will provide you with a demo file. To use it, simply import your excel data into it with each of the excel columns matching up to each of the file maker fields labeled f1 thru f5.

Then just run the script in the scripts menu to move the data from these separate fields into the single field that has 5 repetitions.

Finally import the data from this field with the repetitions into your filemaker file.

ImportTest.zip

[maiton.vang]

Hello Brian,

Thank you for your filemaker test but it still indicate 5 records see my attached xcel file i would like all the info on the excel file except for the first row which is my header to be imported as 1 record instead of 5 separate record. I also attached the importtest.fp file whic show the 5 records when i import.

TaskTime_Data_Export.xls.zip

ImportTest.fp7.zip

Edited July 25, 2007 by Guest

[maiton.vang]

Any help from everyone...i still need help..i would like all the info on the excel file except for the first row which is my header to be imported as 1 record instead of 5 separate record. I also attached the importtest.fp file whic show the 5 records when i import.

[Lee Smith]

I think the answer lies in [color:orange]Brian C first reply. In other words, you need to change the data in Excel. There is a Function in Excel called Transpose, that changes the data from horizontal to vertical, and vice versa.

There has been some Threads in the past about this, here is one to to start with. Link

Pay attention to [color:orange]comment's replies.

HTH

Lee

p.s.

It's either that, or use a text editor like BBEdit (TextWrangler), and remove the returns and replace them with tabs.

[color:blue]Without seeing an actual sample of the text, it is not possible to supply much help.

Edited January 16, 2008 by Guest

[maiton.vang]

Hello,

The sample was upload on 03/13/07.

The transpose also did not work.

What I am looking for is to have those 4 rows(jpg sample) exactly how it look like ton the jpg sample to be import into 1 record only instead of creating 4 different records.

Edited January 16, 2008 by Guest

[sbg2]

I think your problem stems from a basic file structure problem. See if attacheed example helps you. Note that each line you show in your excel file is enetered as a seperated record in the JobLine table.

MyFile.zip