Exporting to MYOB headache

[Lee J]

I have been trying to get MYOB to import data which exports from my FM solution.

It seems that a CR is requried at the end of each line exported. and i can't seem to figure out how I can achieve this.

If i manually edit the text file afterwards i can get the system to work, however with over 1000 exported entries this is not good.

Any tips or tricks please.

Thanks

[BobWeaver]

If you're exporting from Filemaker in any text format such as tab or comma delimited, then Filemaker does put a carriage return at the end of each line. Depending on what OS you are using, the end-of-line character can be CR (mac), CRLF (windows/DOS), or LF (unix). Are you moving the data between platforms?

[Lee J]

Both products run under windows.

[SteveB]

If you can't get the export out of FM to do what you need in terms of the CR, you can definately automate the whole process through WinBatch. It will put a CR on the end of each line with virtually no effort

[kraftyman]

Your problem may be that some text fields imported from MYOB have carriage returns. FMP stores carriage returns in a text field, but when it exports them, it puts stupid ASCI 11 characters (vertical tab) instead. So when your text/csv/mer file is exported, the other software chokes on these silly ASC11 characters. Result = BIG pain in the backside

Solution 1 = $$$

If you have a lot of money, you could buy the MYOB ODBC developer kit, which contains ODBC drivers for MYOB and other stuff. Then presumably you could just use Filemaker to read and write to MYOB tables and vice versa.

Solution 2 = $ or just time/friends in right places

Write a very quick and dirty executable, which will parse and remove the ASC11 landmines.

You could do this in VB, VC++, Java as an executable or use VBscript or, Javascript, or use Word/Excel/Access to achieve this purpose. With the latter, it is more messy, but you would just create a macro in an empty file, which could be run with a command line option. Filemaker will be able to launch this for you.

Hope this helps

[Lee J]

I think you have hit the nail on the head re-fields having carriage returns in.

The last field to be exported is a text based field which contains a description of sorts.

I thought I could be cheekiy and create a text calculation field and as part of the equation add a special ASCII 10 character at the end.

However I couldn't achieve this.

I had thought of an external option so will investigate and as mentioned I will look for a C++ programmer who enjoys working for Chips and Beer

[BobWeaver]

If you know that the problem is with carriage returns in one particular field, then simply strip out the return before you export:

ExportField = Substitute(OriginalField,"

[Lee J]

I'll give that ago, the other thought i had was that when I export, I can't do field matches as the import side of MYOB is fixed, so I have to export a series of dummy fields (text) but empty so that they line up in the MYOB import window.

Is it possible these could be causing a problem?,

If so should i perhaps change them to a Numerical field, or a container?

[BobWeaver]

I notice that some programs barf when one or more fields contain no data. For example, if a field is empty, and so there are two tabs in a row, some programs will ignore the second tab and take the data in the next field throwing everything out of whack. They shouldn't do this, but some programs do it. I generally avoid this problem by making sure that there is some dummy data in every field (sometimes even a single space is sufficient).