Import challenge

[PC201]

I am trying to import sample file format below and without carriage return on each line.
I would like split them into it own field.

123|john|Jackson|234|500 n. channel street|99584
12|sam|simon|23|80-9 s. beacon drve|91801-7845

I’ve tried the MIDDLE WORDS function but it only work if there isn’t a space.
I know access can do it but not sure on FileMaker.

Thank you

 

[Schlogg]

Can you be a bit more specific about your file? Are the '|' tabs or what?
When you say 'without carriage return' on each line, how do you determine the end of a record?

Cheers,
Paul J.

 

[PC201]

Hello Schlogg,

"|" is a field separator. You can say “tab” or “comma”.
When I open this .txt file with FMP. FMP puts “123|abc|jim john|23 apple drive|Los angles|ca|91801” into a single field. What I need is split them into its own field
I've also precreated the field and try to import but FMP doesn't recongize the "|" as field separator

Never mind about the carriage return.


Thank you...

 

[Schlogg]

PC201,
If your '|' were real tabs or commas, then you could import the file into FMP. However, FMP does not let you define your own field separator.
If this is a one-off job, then I'd be getting the file into Excel or Access and changing the '|' to tabs.
If it's a repeating job, you need to re-think how you get the file into that format and make it tab- or comma-delimited.
There is another option which is not recommended if you are new to FMP. You can import the records into a single field and then parse the contents into the desired fields by using a combination of the Position and Middle (NB not MiddleWords) functions.
e.g. Set Field (FirstName,
Middle(BigField, Position(BigField, "|", 1, 1) + 1,
Position(BigField, "|", 1, 2) - Position(BigField,"|",1,1)))
This extracts the text between the first two "|".


Cheers,
Paul J.

 

[PC201]

Schlogg,

This is not a one-off job, so I might have to ues the Position & Middle function. I wanted to avoid that route but I have no choice. I hope & wish that next version will have bit better import & export function so I can work with bigger systems.

Many thanks.

 

[itsisnofun]

PC201

If this was a csv file then filemaker on import will break that as fields and set them to different fields. This is not a problem for filemaker to handle i think you need to look at your txt file again.

Mike

 

[lebisol]

open your file with Notepad or some other simple text editor...looking at CSV file in Excell can "show" things formated differently then they really are...

> need more info?
:: don't click HERE ::