Import from CSV

[Kirsty Hannam]

I have a CSV file, generated from another (non-FileMaker database), which I am importing into a custom built FileMaker solution, simply in order to manipulate/search the data (which the other database couldn't handle!).

 

The data comes in a rather unwieldy format, which I am trying to amend for ease of use/readiability. As an example, the original data has a field for every individual item, where perhaps in FileMaker you would make one field with check boxes/radio buttons.

 

As an example, in FileMaker I would like to have a "Timing" field, with 4 checkboxes - one each for "daytime", "evening", "night", "weekend". The original data has a field for each of these, with either a 0 or 1 to indicate the field contents. Is there a way I can import the CSV with these original 4 fields and put the info together in a calculation field that can display the results on the layout as a set of checkboxes?

 

Cheers.

[JFMiller3]

An Import table within the file might help you. Import the all the raw data into an "Import Table". Once the data is in your solution, you can filter specific data to import into the live side. I have several solutions that purge and replace data, a solution that continuely adds data, and a solution that purges and replaces data with a loop/set varialbe/set field across many tables.

 

After the data is imported to the live side, you can delete or archive the data in from the "Import Table".

[Kirsty Hannam]

I'm already importing the data into a table that consists of fields that match up to the fields in the CSV file. The problem is that some of the fields in the CSV file are "split up" into 4 separate fields, when in FileMaker it makes sense for them to be 1 field, with 4 options. So instead of having the following 4 fields, which in the CSV file (and subsequent import table in FileMaker) have either a 1 or 0 in each field:

 

Daytime

Evening

Night

Weekend

 

I want to create a calculation field called "Timing" which has a set of 4 checkboxes. If the "Daytime" field in the import table is set to 1, the checkbox for that option is checked in my new "Timing" field. If "Daytime" and "Evening" are both 1 in the import table, both options are checked in the "Timing" field etc. What I don't know how to do is take the 4 separate fields and combine the data by calculation into my new Timing field.

[JFMiller3]

I'm already importing the data into a table that consists of fields that match up to the fields in the CSV file. The problem is that some of the fields in the CSV file are "split up" into 4 separate fields, when in FileMaker it makes sense for them to be 1 field, with 4 options. So instead of having the following 4 fields, which in the CSV file (and subsequent import table in FileMaker) have either a 1 or 0 in each field:

 

Daytime

Evening

Night

Weekend

 

I want to create a calculation field called "Timing" which has a set of 4 checkboxes. If the "Daytime" field in the import table is set to 1, the checkbox for that option is checked in my new "Timing" field. If "Daytime" and "Evening" are both 1 in the import table, both options are checked in the "Timing" field etc. What I don't know how to do is take the 4 separate fields and combine the data by calculation into my new Timing field.

 

OK -

 

I might just pass on the 4 fields and create a Case statement on the Live side.

 

Another option is to create a loop/ set variable / set field to pass data from the import table to the live side.

Break this down for every iteration

 

IF ( Daytime = 1 and Evening = 0 and Night = 0 and Weekend = 0)

set variable $timing ; daytime

Else if( Daytime = 1 and Evening = 1 and Night = 0 and Weekend = 0)

set variable $timing; Daytime¶Evening

...

 

Go to layout (liveside)

Set field Timing; $timing (Timing is text field by value list; Daytime;Evening;Night;Weekend)

[Jack Rodgers]

It also makes sense to have them in four fields.

 

Forgive the twist in this question: why other than your own ego is having the data in one field better than four fields?

 

And if you put it in one field, you must limit the field to one choice via radio button or popup menu otherwise why bother?

 

Actually that is a prime question to ask every time we want to do something in Filemaker. Am I doing this just because it makes me think I am a hot shot?

 

Let me tell you how much time I have wasted because of that attitude...

 

Of course the results proved that but still... smiley_cool

[Jack Rodgers]

A replace field will do the trick for you. Just add one text field for the checkboxes and then do a replace on it using the idea below:

 

daytime & P & evening & P & night& P & weekend

 

Be sure to format as a checkbox.

 

You could use IF(IsEmpty) or substitution tricks discussed elsewhere to eliminate double and triple Ps.

[Kirsty Hannam]

Forgive the twist in this question: why other than your own ego is having the data in one field better than four fields?

 

Simply put Jack, they shouldn't be 4 separate fields in the first place - the original database that this data is coming from should have them as 1 field, with 4 options. If I tell you that, every field in the original set of data is formatted this way and that the result is a table with over 185 fields in it, when less than half that number of fields would have done, maybe you'll understand why I need to do something to make this more manageable. I'm certainly not doing this to make myself work, I've got plenty of more interesting jobs I could be working on. As for it being an "ego trip" - us girls don't have time for those (LaRetta - back me up here!).

 

The FileMaker solution that I'm working on is to fix a problem with another system and allow non-techie users to make sense of this info, without having to scroll down a very long page (or along a very wide Excel CSV file) reading a bunch of 1s and 0s. Computers like 1s and 0s - people don't.

 

I know I can set up a Case statement for all the possible permutations but I was hoping someone might have an easier way. If not, I guess I'll go and write that Case statement. I'll try your replace idea and see where it gets me.

[Jack Rodgers]

Have you verified that the original database was capable of more than one option in the field? Sometimes a developer is forced to create one field for each option rather than have multiple values in one field as Filemaker does.

 

Anyway, you will find my solution provides the easiest answer.

 

I'll adapt it a bit after rereading your original post:

 

If ( fielda = 1; "daytime" & P; "" ) &

If ( fieldb = 1; "evening" & P; "" ) &

If ( fieldc = 1; "night" & P; "" ) &

If ( fieldd = 1; "weekend"; "" )

 

Create the additional field and then use replace field with something like the above to fill the field which produces something like

 

daytime

evening

night

weekend

 

depending upon the contents of the old fields.

 

Create the fields in a copy of the original import table. When done export just the new fields to a filemaker file and you're done. Add the appropriate checkbox formatting and your done.

 

This simple method bypasses all of the complexities and problems created with the other suggestions.

[HBMarlowe]

As an example, in FileMaker I would like to have a "Timing" field, with 4 checkboxes - one each for "daytime", "evening", "night", "weekend". The original data has a field for each of these, with either a 0 or 1 to indicate the field contents. Is there a way I can import the CSV with these original 4 fields and put the info together in a calculation field that can display the results on the layout as a set of checkboxes?

 

Make a calc field (result text) in your import table:

Field1 & ¶ &

Field2 & ¶ &

Field3 & ¶ &

Field4

 

 

Then use a looping script to set the field in your live table to the calc field. Of course you'll format your live field as checkbox.

 

Good luck!