Importing Comma-Separated Data

[J. Williams]

My website uses a form wizard to collect information, ultimately sending the comma-separated data to me via e-mail. Problem is I don't know an easy way to get the information out of the e-mail, into FM.

Information arrives in the following FieldName, Data format:

'OwnerFirst','Jane'

'OwnerLast','Doe'

'OwnerStreetPOBox#','PO Box 123'

'OwnerCity','MyCity'

'OwnerStateZip','CA 123456'

'OwnerPhone','555-555-5555'

Currently, I copy comma-separated data out of e-mail and paste into Word, then change to human-readable format as follows:

OwnerFirst: Jane

OwnerLast: Doe

OwnerStreetPOBox# PO Box 123

OwnerCity: MyCity

OwnerStateZip: CA 123456

OwnerPhone: 555-555-5555

Then convert text to table, copy table and paste into Excel, then copy and paste special (transpose) within Excel so field names are all in first row and field data is in the corresponding cell of the second row (if I don't do this, leaving field names in first column and field data in second column, each field imports as a record), then I drag and drop the resulting Excel file into FileMaker Pro. It's as easy as 1, 2, 3, 4, 5... well, you get the idea. It works, but it's a long drawn out process that I have to suffer through for each individual e-mail data submission.

I'm very new at dealing with importing issues and would appreciate a shove in the right direction.

Advance thanks... JW

[BrentHedden]

Are you going to be using FM6 for this? I ask because there are a few different ways to do this, just depending on which version you're using.

[J. Williams]

I'm testing FM8 and will most likely move there, so FM8 fixes would be great.

Are you going to be using FM6 for this? I ask because there are a few different ways to do this, just depending on which version you're using.

[BrentHedden]

I'm supposing each line is terminated with a carriage return, right? If so, try importing it as a tab-separated file, instead of a comma-separated one. The nice thing about this is that FM treats each line as a separate record. If you import the example you gave into one field, you would have six records. If the data always is the same order (First, Last, etc..), then you could write a fairly simple looping script to go through each record and pull out the needed info, and paste it into the area(s) needed.

Another question - does each file you receive contain only one contact person, or could there be several in the same file?

I've used this same method with geneogical records (GEDCOM files). While it's a bit slow if you've got several thousand records in one file, it's great for smaller chunks of info.

[Raybaudi]

Hi J. Williams

if the form is always the same (always six lines with Owner's data), you can try this one !

Comma-Separated_Data.zip

[BrentHedden]

Very well done, Raybaudi!

Yes, if the data is always submitted as described, and there is only one record sent at a time, this method of text parsing works great. It would be simpler to copy/paste the text than importing it one record at a time.

Now, the question remains - does the information consistantly come to you this way?

[J. Williams]

Thanks for hanging in there with me Brent and Ray!

Ray, okay, that is just too cool!

To answer questions...

Yes, each line is terminated with a carriage return, but the lines need to be fields of the same record, not separate records... am I saying that right? Oh, and yes, just one contact person in each file.

And, yes, the form will always be the same (it actually has more lines, I only provided a sampling) and yes, the information will come to me in a consistent manner.

So now my next question, how do I create that wonderful imported text field, given "text parsing" is not something I'm the least bit familiar with... And, would it be modifiable as I go, in the event I had to add a field to it down the road?

[Raybaudi]

Hi J.Williams

if the last row of your file end with a carriage return, every field has the same (about) calc:

the first one: Middle( ImportedText ; Position( ImportedText ; "," ; 1 ; 1 ) + 2 ; Position( ImportedText ; "¶" ; 1 ; 1 ) - Position( ImportedText ; "," ; 1 ; 1 ) - 3 )

the second one: Middle( ImportedText ; Position( ImportedText ; "," ; 1 ; 2 ) + 2 ; Position( ImportedText ; "¶" ; 1 ; 2 ) - Position( ImportedText ; "," ; 1 ; 2 ) - 3 )

....

the last one: Middle( ImportedText ; Position( ImportedText ; "," ; 1 ; n ) + 2 ; Position( ImportedText ; "¶" ; 1 ; n ) - Position( ImportedText ; "," ; 1 ; n ) - 3 )

So you can add as many fields as you want !

In my example I tinked that the last one hasn't a carriage return; if so the last one becames:

Middle( ImportedText ; Position( ImportedText ; "," ; 1 ; n ) + 2 ; Length(ImportedText) - Position( ImportedText ; "," ; 1 ; n ) - 3 )

That calc can be made with script too...