Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Importing by caiage return


bigzilla
 Share

Recommended Posts

I have a list of customers that I got from a web site and copied and pasted into a Word document. All of the information came over separated by carriage return as shown.

 

First & Last Name

Company Name

Street Address

City, State & zip code

Phone

Email

Website

 

Is there a way to get this information into FileMaker8 into 10 separate fields? Like NameFirst, NameLast, etc?

 

Thanks for you help.

 

Bill

Link to comment
Share on other sites

I am quite sure LaRetta also meant to ask this:

 

How are the First and Last names separated? And the same question regarding City, State & ZIP code.

Link to comment
Share on other sites

I have three more questions before I could provide a solution for you:

 

1) Are there instances that one of the lines is missing? Could you have a name and no Company name?

 

2) What separates one 'record-to-be' from another? I assume you have two hard returns there? Could there be less or more?

 

3) How convinced are you that the data does NOT include Mr. (etc) at the beginning. Could the data contain a middle name/initial? And what about, PhD. or MD at the end?

 

LaRetta smiley-smile

Link to comment
Share on other sites

Hello again LaRetta,

 

All names are exactly the way as shown below and seperated as shown.

 

John Doe

Premier Industry Inc.

1139 Countryside BLVD Ste 113

Denver, CO 30356

813-855-8000

e-mail:

Website:

 

David Fitz

American Seals Corp

114 Shoal Place

Pueblo, CO 30386

800-774-2220

e-mail: d.fitz@mindsp.com

Website: AHFits.com

 

You will notice that "e-Mail:" and "website:" are included in the field. If there is a way to eleminate the "e-Mail:" and "website:" names from all records that would be great. But as I said, all information is just as shown above. After looking at over 500 records they are all the same. The only place they differ is more than half do not have an email or website address. There are no Mr. Mrs etc.

 

Thanks LaRetta,

 

Bill

Link to comment
Share on other sites

Hi Bill,

 

I just want to make sure we give you what you need. Your respsonses have been perfect. No problem eliminating the email and website portions. The consistency will make the parse easy. What vs. of Microsft Word are you using?

 

I must leave and won't be back until this evening. If someone else wants to step in, they may (of course). Otherwise, I'll write your script tonight. smiley-smile

 

LaRetta

Link to comment
Share on other sites

I would suggest making the text "import friendly" by editing the file in word (MAKE A BACKUP COPY FIRST!!). I am assuming the Mac version of Word has the same features as the Windows version, but Im not 100% sure as I dont run a Mac.

 

- MAKE A BACK-UP of the Word file!

- In Word go to Edit>Replace

- Select the More button

- In the Find field use the Special button to select "manual line break" this is "^l" on my version.

- In the Repalce field type "|" (without the quotes, this is a pipe character)

- Click the Replace All button

 

This results in most of your fields having a pipe between fields and two pipes where a record break is need.

 

- do a second Replace All where find = "||" (two pipes) and replace with a "^l" (manual line feed).

- save the file as a Text file but use the .tab extension (otherwise you will have problems with commas).

 

now your records should be seperated by carriage returns for easy import into filemaker. To seperate the fields I would start with using the susbstitute function to Substitute a Return for each pipe. Then MiddleValues(MyTextWithReturns; 2;1) would equal the Company Name.

Link to comment
Share on other sites

Hi Bill,

 

Well there were many approaches. You still haven’t indicated whether this is a one-time thing or an on-going process. And I don’t know if this needs to be user-friendly or whether only ONE power-user will be doing it. We could have also (possibly) used the clipboard but I didn’t want to do that (although the process would have certainly been easier).

 

I don’t believe in releasing import data directly into my main solution. I have seen and experienced too many horror stories. I prefer to use an intermediate file (attached ImporterFile). Once the data is confirmed as correct by the User, simply pull (from its main table) into the proper tables. So this demo is an independent file. Different versions of Word have the Replace options listed differently. And I don’t know Mac Word at all (I didn’t even know there WAS such a thing). Because of these differences and because of wanting this process to be the easiest for Users, I decided to handle everything within FM.

 

So your ‘export’ from Word will be very simple:

 

1) Open Word document.

2) Select Save As

3) Specify PLAIN text.

4) Open ImporterFile

5) Click Import & Parse

 

A few additional concerns:

 

I know you said there was no Mr. etc. but might there be a double first name, ie, Peggy Sue or Billy Bob? Might there be possible double last names, ie, Van Damne or Van Buren? Because of these concerns, the import pulls First & Last into ONE field called FullName first. Auto-Enter calcs fill FirstName and LastName. After the import, User should view the FullName next to the FirstName and LastName fields and make sure every change is correct. You can eliminate all the 2-word names. Create a calculation (result is number = WordCount ( FullName) > 2. Then just search this calc for a 1 and check manually.

 

Current parse assumes as follows: If the number of words is uneven (for instance, contains 3 words) then the script assumes most likely a double last name. If there are four words (Peggy Sue Van Buren), it splits 50/50. If 5 words, three will be placed in LastName. I did the same thing with City, State Zip (although that is probably overkill). It imports into one field and then auto-enter splits them into their proper fields. But in case the State has more than 2 letters, etc., this ‘audit trail’ might be important for verifying the parse.

 

Last point: I can NOT figure out how to many it multi-user by using only globals and GetField(). I believe I could given time. This intermediate process is a protective measure and a user-friendly approach. The data would need to be moved on. BTW, you don't need to open the Word doc and Save As first. It includes the export txt file. So you can just open ImporterFile and click button to see how it works. I hope it is helpful.

 

UPDATE: It was late. And I had forgotten to trim the eMail and Website. I had also left the serializing in reverse order. It would have worked as reverse serial but I changed it anyway. Opps!

 

LaRetta smiley-smile

Link to comment
Share on other sites

Very Nice LaRetta.

 

Heres an example where I had 5K records already entered some what like the word example. Only it was a mess. A lot of scripting in v5 (now converted)

See Attfached.

Link to comment
Share on other sites

Hi LaRetta,

 

Thank you so much for your help and sample file, it worked great. Out of an initial import of 538 records there were only four I needed to change. Again, thanks for the help; I'm always amazed at the things you guys know!

 

Chopper I didn't get your post until today and I thank you as well.

 

Bill

Link to comment
Share on other sites

Hi Bill, glad it worked for you. Were the four you changed because of double name or was it something I didn't account for in the parse? These are the times I learn the most and if there was something more I should have considered, I'd appreciate knowing it. smiley-smile

 

LaRetta

Link to comment
Share on other sites

Hi LaRetta,

 

One did not have a first and last name and it picked up the company name in the first and last name fields. The other three had to do with a zip code that started each new set of names. So each time there was a new zip code it would be listed as shown below and it messed up the first record after each zip.

 

60606

 

John Doe

Chicago Steel and Feel

etc.

 

60626

Jim Doe

American Boy

etc

 

Thanks again LaRetta, you're the best!

 

Bill

Link to comment
Share on other sites

LaRetty you go girl. You do good work and It's so nice to have a friend back sipping coffee at the ol Cafe!

 

Harry

Link to comment
Share on other sites

Hi Bill,

 

I didn't mention that the parse expected 7 lines of data with one blank line between (as you laid out). I originally started at the bottom and planned to reverse parse until it came to the company/name fields (assuming if you were missing a line, it would be there). But I realized no matter WHERE I started, if you were missing a line, I wouldn't know where. But I could have place-marked eMail and Website and City/State by their pattern. I know you said the data was consistent but it's been my experience that consistency is consistently inconsistent.

 

I should have imported, checked the field for data patterns, presented the User with the results laid out just like regular address fields, allowed User to make changes, THEN assigned the serial and parsed from THAT. But I still didn't know if this was a one-time thing or on-going process (and I might have been over-killing anyway). I was hoping cSerial would tell the story - which it does. cSerial turned the multiple lines into one record and any breaks would have been obvious but I never told you that either (see Import layout). Thanks for the experience. I can NEVER get too much experience when it comes to parsing. smiley-smile

 

Harry, old friend! Thanks. It's good to be back as well. And I don't care what ANYONE says about you ... I like ya! Don't ya just hate that? ROFLMAO!!

 

LaRetta smiley-wink

Link to comment
Share on other sites

Hi LaRetta,

 

I intended to mention that our importing from the Word doc would be ongoing. We import between 4 to 6 zip codes per week ranging between 450 to 700 records. Right now they all come from the same source and will be relatively clean like the last set of records.

 

Once we move away from these and go to other sources for the information I guess we could have a problem all over again. But what you gave us sure works great now.

 

Thanks again LaRetta,

 

Bill

Link to comment
Share on other sites

Hi, bigzila!

If you will have FM file with one text field for each row as in word file, i think you can easy get your goal.

One of ways of having FM file is: copy contents of word file to excel, then import it to FM.

In FM you can do with this file what you want.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use