Importing Data Problem

[Kurt Knippel]

quote:

---

Originally posted by Macman:

Greetings!

I've been over this a few times with tech support of two companies, went through the list of plug-ins, and have tried all known solutions without success.

I'm using FM5 to "extend" our DOS based accounting program's sales capabilities and reporting, and importing data from this program requires a bit much; true, it is job security, but who wants these guys calling me when I'm nursing a flu!

Our acct. program cannot export in tab delimited or dbf. It can export comma delimited, but the use of commas in the fields (like company names, etc.) wreaks havoc on importing; one off comma and the whole thing goes off kilter. My solution thus far has been to use Excel to import it as a fixed width text file (which works nicely), but try and expalin that to co workers who have a problem just starting up thier computers.

So here is: is that same "fixed width" import available for FMPro? Has anyone seen a plug-in that might do this? The extra step using Excel's for the birds, and it doesn't do much to help sell FMPro's involvement into the company. Getting another acct. program is not an option (I've tried to get them to use an FM solution without success), and I'm stuck in a very long morning routine.

Any ideas?

---

Well, I am afraid that the problem is not with Filemaker or with Excel, so you are kinda stuck.

Personally I use Excel all the time for data cleanup. I find that it is a fantastic program for that use. I would recommend that you keep using it for that purpose.

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

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Senior Filemaker Developer

http://www.inthescene.com

mailto:[email protected]

=-=-=-=-=-=-=-=-=-=-=-=-=

[danjacoby]

I agree with Kurt on all points.

[Macman]

Greetings!

I've been over this a few times with tech support of two companies, went through the list of plug-ins, and have tried all known solutions without success.

I'm using FM5 to "extend" our DOS based accounting program's sales capabilities and reporting, and importing data from this program requires a bit much; true, it is job security, but who wants these guys calling me when I'm nursing a flu!

Our acct. program cannot export in tab delimited or dbf. It can export comma delimited, but the use of commas in the fields (like company names, etc.) wreaks havoc on importing; one off comma and the whole thing goes off kilter. My solution thus far has been to use Excel to import it as a fixed width text file (which works nicely), but try and expalin that to co workers who have a problem just starting up thier computers.

So here is: is that same "fixed width" import available for FMPro? Has anyone seen a plug-in that might do this? The extra step using Excel's for the birds, and it doesn't do much to help sell FMPro's involvement into the company. Getting another acct. program is not an option (I've tried to get them to use an FM solution without success), and I'm stuck in a very long morning routine.

Any ideas?

[amccallum]

Hi Macman

I agree that the use of Excel is a good move and is relatively easy and reliable... but you can do it all in FMP and even without a plugin... but it will take a little bit of effort.

The file from the DOS package is fixed length correct ? FMP doesn't import fixed length as such, but will import the whole string (for each line) into one field... which is ok because you know how long each section of the string is, where each section starts and where it finishes. So you import the file into FMP (1 text field per DOS line)... then you can either use a number of calculations to parse the text into separate fields or run a script over the newly imported fields to parse the text sections into the appropriate fields. Then you can just click a button and run a script each morning to import the data and parse it, if you don't want to export from DOS... import to Excel... export as comma delimited... import to FMP. There may be a plug in... but I have never used it... never needed to.

Hope this helps. It will certainly test your feelings towards Excel, but stop those phone calls.

Let me know if you need more details.

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

Andrew McCallum

McCALLUM CONSULTING P/L

Sydney, Australia

61 2 9585 8775

61 407 482 609

[email protected]

[Macman]

Yeah, actually I would like to see how that's done... I didn't know FM could parse text... Please! Enlighten me!

[amccallum]

alrighty then here goes...

create a simple fixed length text file to test.. import it into FMP and each line will go into a single field and each line will be a new record.

create some calc fields using the Middle(textfieldname,start char position,length of text) function (eg Middle(importedfield, 1, 20)).... you could also apply this same principle using a script and looping through the records performing the calc and setting the existing fields to the imported values.... you could get even fancier and allow the user to define the start and length of the fields in another file and use it more as a fixed length import utility.

Email me if you want more details or a demo file and I will throw one together for you.

quote:

---

Originally posted by Macman:

Yeah, actually I would like to see how that's done... I didn't know FM could parse text... Please! Enlighten me!

---

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

Andrew McCallum

McCALLUM CONSULTING P/L

Sydney, Australia

61 2 9585 8775

61 407 482 609

[email protected]

[This message has been edited by amccallum (edited July 14, 2000).]