Importing customer list from QuickBooks

[Baylah]

I ran into this a few years ago and thought it was just me...but as it turns it is only partially me!

Quickbooks in their wisdom that I don't understand has their export feature set up to export a customer infomration where the CITY STATE ans ZIP are all in one field. A few examples are below

APPLE VALLEY, CALIFORNIA 92307

PHOENIX, AZ 85040

SAN DIEGO, CA. 92101

MESA, AZ. 85201

Is there a way in FileMaker where I can somehow tear this data apart and put it in to 3 seperate fields, City, State, Zip?

I have tried exporting from QB into an Excel file as well as a CSV file but I get the same results.

I could really use some help on this one! I can;t imagine having to seperate 2,000 records manually, and importing into FM this way would be so lame.

Any help would be greatly appreciated.

Thanks,

Steve

[sbg2]

Try:

City = Left ( CityStateZip; Position ( CityStateZip ; "," ; 1 ; 1) -1 )

State = Trim(Filter(Right ( CityStateZip ; Length(CityStateZip) - Position ( CityStateZip ; "," ; 1 ; 1) -1 ); "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ "))

Note: a space should be included with the letters in the filter function. I have included one in the above function but this BB tends to add extra spaces when I copy and paste.

Zip = Filter(Right ( CityStateZip ; Length(CityStateZip) - Position ( CityStateZip ; "," ; 1 ; 1) -1 ); "0123456789-")

I would suggest you set up a sample file first to check for any errors.

P.S. Quickbooks does some really funky things with addresses. It tries to outsmart you and can be quite frustrating IMO. For example try typing the following in as an address:

Address =

MyCompany

123 Any Street

Attn: ABC123

Something More

City = Grand Cayman

Country = British West Indies

What you end up with is

Address =

MyCompany

123 Any Street

Attn: ABC123

Something More

City = Grand Cayman British West Indies

If you get a chance check out FileBooksLink http://www.filebookslink.com/ . Rather than exporting from Quickbooks and then importing into FMP you can access QB directly from FMP. I have been using it for a while now and the plug-in cost has been more than payed for from the time we have saved. The developers/community are also very helpful in getting you up and running.

Edited May 9, 2007 by Guest

[Baylah]

SB,

I have tried the calculations you suggested and the City and Zip come out perfect, for the state I get a ? as a return in the Zip field.

I simply cut and pasted the calculations from your post so I don't think there are any type-ohs.

If you have a minute would you check out the attached file and see if you can the problem.

Thank you very much for your effort so far. I would not have known how to do this at all. I need to read up on these functions becausae just by looking at the code I do not really understand what is going on.

Additionally, Do you know anything about the FMbooks Connector plug in that has been advertised on this forum lately? I downloaded the demo and seems to be a great deal easier to set up than FileBooks. I was planning on trying to incorporate that into a solution I am working on right now.

Thanks again for your help. I hope you have another couple of minutes to check out the attached. Bill to 3 is the combo field in the attached CSV file that I am trying to extract the info from.

Steve

Addresses.zip

[Fenton]

Two problems. First, you left "state=" inside the calculation; that was just the name of the field; remove it and the calculation works.

The other is more serious. The "CityStateZip" place in the .csv file is NOT always in the same place (nor Address). If there's an extra bit in the address, such as "C/O Joe Blow", then they bump the Address and CityStateZip over one column. In other words, it's usually their column "Bill to 3", but in the case above, C/O Joe Blow is Bill to 2, the address is bumped to Bill to 3, and CityStateZip is bumped to Bill to 4.

It's not a huge problem, because apparently they only use the Bill to n fields for this. The fields below are not also bumped out of place.

So what you'll need to do is to Import those Bill to fields, at least 2, 3 and 4 (and possibly 5) into temporary fields. Then you'll have to go through them, on each record, and see which have data, and if so, what. Possibly the "which have data" will be enough to know. But I imagine it would be easy enough to identify the Zip at the end (5 digits at the end). Then move the data into proper fields.

You could, to save space, just import into the real fields, then switch the data around using Variables to hold them. Or import into temporary fields, then clear them when all is well (or not).

Test for CityStateZip in Bill to 3:

Let ( [

wCount = WordCount (Bill to 3);

rWord = RightWords (Bill to 3; 1);

rNum = Filter ( rWord; "0123456789" );

lenZip = Length ( rNum )

];

wCount > 2 and lenZip = 5 and rWord = rNum

)

[Baylah]

Two problems. First, you left "state=" inside the calculation; that was just the name of the field; remove it and the calculation works.

DOH! I can't beleive I did that. I was running around like a mad man trying to get the kids ready for school and me ready for work, etc. blah blah blah! Thanks for pointing out this obvious goof.

Man...Qbooks sure doesn't make it easy do they!

Thank you very much for your help. This is huge.

Do you have an opinion on the FMbook Connector plug in?

Thanks,

Steve

[Fenton]

I've not used (either) plug-in. There are 2 plug-ins however, and you've named the 2nd. sbg2 said "FileBooks", which is an xml-based plug-in. Productive Computing has an "FM Books Connector" plug-in, similar in function, but does not require that you know xml. It seems a little cheaper. I've not looked at it, nor do I know how the volume pricing would work out for you.

[sbg2]

I think you need to add a - (dash) to the filter and change the lenZip check to greather than 4. Otherwise zip codes with the extra 4 digits are returned as false.

Let ( [

wCount = WordCount (Bill to 3);

rWord = RightWords (Bill to 3; 1);

rNum = Filter ( rWord; "0123456789-" );

lenZip = Length ( rNum )

];

wCount > 2 and lenZip > 4 and rWord = rNum

)