What would the data typically look like now, in rows and columns, as it is in the file that you will be importing from?
nice you're paying attention to my issue. I attached a picture to the initial message.
No problem, Wolf,
The correlation between Excel and Filemaker is that each column is a field and each row is a record. (I think you knew that, anyway)
If you create a table in a Filemaker file with the field names as you have them across the top of the columns, and import the data from that Excel sheet it will let you line up each column against the right field name. It will import the data into the right fields, and create a record for each row.
(In fact, FM can create the Filemaker file directly from the Excel sheet if the column header is set as the field name.)
If that was what happened, and you were looking at a Filemaker layout in list view, and it looked exactly like your spreadsheet, would that mean your question was answered?
I think he wants all combinations of the name in column a with column C through N to be in separate records.
You can do that with a two stage process. Import into a table where each spreadsheet row is a record, then run a script that extracts the data from each column as needed to create records in your final table.
I haven't done this myself, but something might also be possible with an XML import if you can set up the grammar to split the columns into records during the import...
I think you should have fields:
Then to let you be flexible with the name of the trips, I would have the same number of global fields:
Import column 1 into the field Bushaltstelle_Name
Import column 2 into the field Ankunft01
Import column 2 into the field Ankunft03
You will have to tidy the spreadsheet first to remove the empty columns (eg column D)
The 'an' and 'ab' will be a bit tricky, though. Needs some thought.... (You could have two stops: 'Bahnhof - an' and Bahnhof - ab')
- Phil, so:
Mannheim, Hauptbanhof 12:10
Mannheim, Hauptbanhof 12:20
Mannheim, Hauptbanhof 12:35
Mannheim, Hauptbanhof 12:50
Mannheim, Hauptbanhof ...etc
Reichskanzler - Muller - Strasse 12:12
Reichskanzler - Muller - Strasse 12:22
Reichskanzler - Muller - Strasse 12:37
Reichskanzler - Muller - Strasse ...etc
If that's the case, could Wolf not (easily) create the Excel table as is, straight into FM format. Then duplicate the table. Then set up Import A, ImportB, ImportC, etc, which import:
The first import runs:
Source Field Destination
The second import runs:
Source Field Destination
This is like thinking in 3D for me. My head is starting to hurt.
Good idea! And you don't ever have to "Create Excel table as is, straight into FM format". You can just import from Excel each time.
Then delete the records with an empty 'abkunft'.
But need to get the appropriate Fahrtnummer into every import's record.
@Wolf - Before thinking about that, can you confirm which way you want the import to work?
Hi to everybody,
thank you all for your valuable thoughts, and please apologize this reply comes so late. - The project within which I had to solve the problem described above is scheduled rather tightly, so "doing" had a higher priority.
What I took from your answers was that there is no "ready-to-go" solution. As I'm not good in scripting FM, I prepared the tables before importing them to FM. I'm not good in visual basic as well - but good enough to get it done. The script transposes the tables into lists, each line containing all the info necessary - and then it's easy enough even for me to get them imported.
If one of you is intersted in that little script, please drop me a line and I'lll be happy to mail it over.
Yours thankfully, Wolf