AnsweredAssumed Answered

How can I import messy data from Excel?

Question asked by edyas on Jun 3, 2018
Latest reply on Jun 4, 2018 by edyas

HI Everyone, I have had a solid headache for the entire week-end.

I have used Filemaker import tool to import data from Excel.

But it has always been very neat data:

row 1 = field 1, row 2 = field 2, etc.

column 1 = record 1, column 2 = record 2, etc.

 

Now I need to import messy data from Excel.

More precisely, the 6000 + records are not sorted in 6000+ columns; instead they form a huge list in the first 2 columns.

There are 39 potential fields, some records have them all, some do not.

The only good news: they all start with the same field ("NAME").

 

To schematise, this mess looks like this:

(record 1)

NAME                 Alex

AGE                    40

JOB                    LAWYER

 

(record 2)

NAME                Nico

SURNAME         Lewis

JOB                    SALESMAN

 

(record 3)

NAME                 Seb

SURNAME         Lewis

AGE                    35

 

Is there anyway to ask Filemaker to kindly:

- create a record every time he finds NAME in column 1?

- to copy the data (in column 2) in the appropriate field (whose name is in column 1)?

 

Many thanks for any help you can offer...

Outcomes