making columns into rows

[Eden Morris]

I have a csv file that could have any number of columns (fields) 300 or more, but it will always have just three records. this is an export of a survey. the first record is the first line of the question the 2nd record is the 2nd line of the question and the 3rd record is the answer. I want to import this csv file and turn each of the columns into one record in my DB that is three fields. Is there anything that will rotate the csv file and allow me to import it or do I have to have a temp table with hundreds of fields and this script it from there?

I think the term for this is transpose? I see that I can do it in excel but is there a way to script this for multiple files?

Edited August 22, 2010 by Guest

[Fenton]

When you import a csv (or tab) file which does not have a "header" line, FileMaker will just name the fields, "f1", "f2", etc.. You could use that fact to construct the name of the field, with an incrementing counter, which could then be used to get the value.

You would need to have enough fields in FileMaker to handle the largest number of columns ever expected.

Assuming the 1st line question always has a value for that position, you can exit the Loop when you run out of values. If you do not have enough fields in FileMaker, it will go into an endless loop, and create bunches of useless records.

Since you only have 3 records in the original data, I just went to the Next Record for the 2nd and 3rd lines. I tried to use GetNthRecord, but was unable to get it to use the text name of the field. Perhaps someone else could; tho with 3 the "next record" is fine).

There is also no way to stop it doing it over again. So you'd want to Delete the original 3 records before running again on new ones.

survey_parse.fp7.zip

[comment]

I tried to use GetNthRecord, but was unable to get it to use the text name of the field.

It's easier when you use GetField() instead of Evaluate().

If you do not have enough fields in FileMaker, it will go into an endless loop, and create bunches of useless records.

I am not sure I understand this point.

---

BTW, this would be a lot easier if we could assume no tabs or carriage returns within fields.

survey_parse2.zip

[mweiss]

An easier (I think) method is:

(1) Open the CSV file in Excel.

(2) Select rows 1-3 (that is, all of the data).

(3) Select Edit > Copy.

(4) Click in the 1st cell of row 4.

(5) Select Edit > Paste Special... and select the option Transpose.

(6) Delete rows 1-3.

(7) Save the Excel spreadsheet and import it into Filemaker.

To automate the process, use a Visual Basic macro or an Applescript (depending on platform).

(Edited to add: Oh, I see you were already aware you could do this in Excel. Sorry I missed that in the original post. I think the thing to do is to work on automating the Excel process rather than trying to get Filemaker to do it for you.)

Edited August 23, 2010 by Guest