Export to Excel / Import into MYOB

[Questions King]

Hi all, did anyone face this problem? when I export to Excel(.tab) file and I try to import from my MYOB accounting software and face some error.

I have 100 invoice when I import into MYOB, MYOB just import 100 decription in one invoice and for me?

Any idea????

[QuinTech]

Hi King, it is important to keep in mind that a .tab file, while it opens in Excel by default on many systems, is not really an Excel file. It is just a bunch of data separated with TABs to delimit fields and NEWLINEs to delimit records. It may be that Excel has some special character that delimits records.

Try exporting to several different file types and see if that helps. One that seems to be useful in many situations is .mer . If none of those work, try exporting to .tab then opening that .tab file in Excel and saving it as an .xls file.

HTH,

Jerry

[Questions King]

Hi Jerry, I was attach the file(Export.xls), can you please take a look the file?

(Page 1) --> Export from FMP

(Page 2) --> The result I need

Anyway thank for your reply!

Export.zip

[QuinTech]

So you just need a blank row to separate each row of data? (Please correct me if this is wrong, i'm opening it in OpenOffice and not Excel, which sometimes causes irregularities.)

There may be several complex solutions, but probably the quickest method for you is going to be to script your export like so:

Find all records you wish to export

Sort by index number

Go to first record

Loop

...New record

...Go to next record (Exit after last record)

End loop

Export

Enter find mode

Set index number field to <blank>

Perform find

Delete found records

This script adds blank records right after each real record. The "Sort" step is in there to force new records to be created right after the record in focus, rather than at the end of the queue, which is what would happen if you didn't sort it. Test this out, though; i know it will work in FM6 when you use "Duplicate record" instead of "New record;" but the behavior may not work for you.

Aside from that, i can't really think of any ways in FM to create blank rows between each real row.

J

[Martins]

I

[Fenton]

I can't write any more at the moment, without going stark raving mad. But you might want to look into exporting with XML/XSL, for the simple reason that it lets you put whatever you want in between the rows. Yes, it's more trouble. If you search for Export and CSV there are a few posts.

[QuinTech]

Echo what Fenton says, except the madness part. XML is the way to go.

I don't like to criticize, but if MYOB requires blank rows between records for import, they should htink about modifying their import procedure; i've never heard of an app that requires such a thing, and it's awfully odd.

J

[Fenton]

Good morning; feeling so much better -)

After looking at your Excel file I'd say it shouldn't be difficult to do it with either FileMaker or XML/XSL. There's only 3 fields.

I had another FileMaker idea. Basically I don't much like the idea of creating/deleting masses of records. Though, in version 7 the total limit of records for a table is "64 quadrillion total records over life time of file." I can't even imagine.

But there is really no need to delete records; as you can just delete data. So, you have a table just for this export to MYOB. You create a bunch of empty records in it, with a Loop, twice as many as you'd ever need for the export (could check each time with a script).

Show All Records in the import table; Unsort. Then import the records for the export, with "update existing records" (which are empty), importing data into the fields. Then Show Omitted. Then Import again, same option, but this time only import the ID field. Find for any data in ID. Then Sort by ID, Go To Record ["First"]. You should have 1 full data row, with 1 row with only the ID. Loop again, deleting the ID of every other record (even records; to make them empty again). Export. Find data. Loop (or Replace) again, deleting all the data, ready for the next time.

Yeah, pretty ugly. But it doesn't delete records. Tip: View as Form (speeds up Loops).

This is an XSL stylesheet which does the same. Forgive the length. It's a modification of one of FileMaker's distributed XSL's, and they insist that you include their legal stuff (at least when you post to a public forum :-)

Save the code as a text file, with extension .xsl (not .xls). Don't let it use .xml, or add any crap. There's simpler ways to write it; this one has nice variables and delimiters thingees; but you could just hard-code those, remove the thingees, and the comments.

(P.S. "&#34;&#44;" is "comma quote" to you humans)

-------------------------------------------------------------

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult">

<xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>

<!--

File: FM_MYOB.xsl (my suggested name)

Transforms data in FMPXMLRESULT grammar into a text file with the

specified characters as field and record delimiters.

Note that it is possible to choose field and record delimiters

that would result in an exported file that FileMaker Pro can no

longer import.

===============================================================

Copyright

[Martins]

Thanks to Fenton. I don

[Fenton]

No, I think it's MYOB. I've heard this complaint before; for years. Perhaps they were having trouble with people putting returns into fields, and thought they'd solve that problem by using 2 at the end of a record. Except no other data program exports like that.