Importing CSV with Line breaks

[Lune]

I'm a real beginner at this...I am trying to import a csv file into an Orders database.

I have a csv file (in a format which I cannot change) from an online shop. The file contains Name, Address etc on 1 line and then a new line for each item ordered (containing item, quantity, option, price etc.). The number of lines in the csv file is variable and depends on how many items have been ordered.

I would like the Order no, Name, Address etc and line 1 of the order on the first record, Order no, Name, Address etc and line 2 on the 2nd record and so on...

How do i script the import to keep going until there are no more order lines? How do i alter the import mapping in the script for each new line when importing a single csv file?

The mapping imports each new line on the csv file into a new record so the Order Number, Name, Address etc are in a different record to the order lines.

Thanks in advance!

[Lee Smith]

Hi Lune,

First of all, please update your [color:"blue"] profile to show what plaftform, Operating System and version of FileMaker you are using. The link is just below your name on the left.

Can you attach a sample of your text file just as you receive it. Sometimes it is easier to deal with text manipulation in a text editor such as BBEdit prior to import.

Lee

[Fenton]

Yes, as Lee says, please let us know which platform. It's helpful on these text format questions. But I think this one's mostly straight FileMaker. The main problem is the variable number of items. The secondary problem is that this "flat" text structure needs to be in FileMaker as a relational structure. You may not know what that means; so we'll tell you, 'cause you're going to need it.

Once again, we don't know what version of FileMaker, so I'm going to use terms from 7. If you have 6 then translate "table" to "file" (7 can have multiple tables in one file).

You need at least 3 tables, possibly 4 : People, Invoices (opt.), Invoice Line Items, and, in this case, InvoiceImport. I'm assuming 1 Invoice is what's in one .csv text file, for one person. Perhaps you don't care about Invoices, and only care about the lines? That's a little simpler.

In the simplest scenario, you'll import the .csv text file, as is, into InvoiceImport, each line becoming a record. You can drop your text file on FileMaker to create this first table. Just leave the fields as f1, f2, etc.; because you don't know what they are.

The first line will be a Person record. Set its fields into the People table, that is, create a record and set the fields (Name, Address, etc.). Create an auto-entered incrementing serial ID field. Set that into a global field.

Back in InvoiceImport, go to the next line. It is probably an Invoice Line, but we are going to have to check them sometime, so let's start now.

You need to be able to differentiate between a People line and an InvoiceLine. Either the people line or the invoice line MUST have a field (column), in a known location, which ALWAYS has recognizable data; something like a zip code. It cannot fail to be recognized or everything breaks down. It doesn't have to be "constant," but its "pattern" must NOT be in the same place on the two different kinds of lines.

If it is a pattern that FileMaker can't recognize easily, but that can be matched with "grep," then you might do this ahead of time in the text file, as Lee mentioned. But FileMaker can do a lot, with the right calculation.

The Invoice line goes into its table. The loop continues. The next line is either another line, or a person.

Yes, this is a little beyond the beginning level, but it is not as bad as it sounds. Upload your text file and we'll see.

[Lune]

Aopolgies for the delay getting back to you - been away! Many thanks for the advice. I will upload a sample file in a short while.

Profile updated too.

[Lune]

Been a while - minds have been changed and left me with a slighlty different problem to solve...

I have a table with a number of fields containing quantities in stock. The Fields are named with the stock configuration e.g. CM1, CM2, CM3, CW1 etc. and contin a number.

In a separate table for Orders, I have created a pop up list with a custom value list in one field, "Size Profile" containing the stock configurations, and a "quantity ordered" field. I want to write a script to check the quantity in stock and return a result (OK to process or not). How do i script to check the relevant field in the Stock table - the record and field that it should check against will vary with each order. Can I calculate which field to check against using the contents of the field from the order? If so How do I script it?

i have a relationship between the two tables based on the Product codes.

Thanks for the help previously - just about got my head round it when the goalposts were moved!

Chris Lunn.

[Fenton]

[There are 2 ways to think about this problem. One is to put together calculations to check the configurations. I don't even know how many there are. In any case, I don't think like that. I prefer to solve problems with relational thinking. It's my bias, and I'll answer accordingly.]

You shouldn't really have "fields" with a fixed stock configuration. That's really still "spreadsheet thinking." A database would have 1 field, StockConfig, which would have your "codes," CM1, CM2, etc.; then a Quantity field for the number.

It would actually be another table, a "join" table, "Product_Configurations," with 1 record for each unique Product/configuration combination, and the Quanity in stock.

It is not terribly difficult in version 7 to have join tables, and also read data from the "parent" table, in this case Products. The thinking is this: Every size of a product is a unique entity; they are the actual items you have stock quantities of, not the "Product," which is a concept, not an actual physical item (except as the sum of all its related items).

All other info about the Product, which all configurations share, remains 1 in the Products table. It's really the same as what you're doing now, just proper relational form.

It seems to me that on an Order, on any particular line item of that order, the Product can only be of 1 stock configuration. This structure would match the structure of the Product_Configurations table. If the structure matches, and a Product-configuration key matches,* you have only to subtract the quantity on the line item from the related quantity in stock.

*Or one of them new-fangled multiple-key relationships -/

[Fenton]

Here's a little example file. It's fairly simple to set up. It took more time to build the "Windows" script than anything else -)

ProductSizes.zip