Apologies if this has been asked before but I couldn't find an answer to my problem.

 

I am importing a list of stock items from various suppliers using a CSV file provided by the supplier.

 

The CSV file contains various fields but my query relates to the 6 individual fields for image urls in the file: URL1, URL2, URL3, URL4, URL5 and URL6. As a key, I am using the field 'Handle'. 

 

When I import the file into FM, everything works fine. My issue comes when I try to export the file to an Output CSV file which I will be importing into a web page.

 

I need to export records containing all the information in the record which is fine but I also need to have a separate row for each URL which appears in the record.

 

The Input CSV file looks something like this:

Handle, Description, Price, Quantity, URL1, URL2, URL3, URL4, URL5, URL6

 

I need my Output CSV file to resemble :

Row 1: Handle, Description, Price, Quantity, URL1

Row 2: Handle,URL2

Row 3: Handle, URL3

Row 4: Handle, URL4

Row 5: Handle, URL5

Row 6: Handle, URL6 

 

Thanks in advance.

 

Mark

4 hours ago, Mark12345 said:

As a key, I am using the field 'Handle'. 

What exactly do you mean by that? AFAICT, you are describing a single flat table with 6 numbered fields (which is not a good structure to have, but that's beside the point for now). What role would a "key" play here?

Also, please edit your profile to reflect your version and platform, so we know what you can use. Recent versions offer more options for exporting to custom formats.

Finally, what do you expect the result to be when one of the URL fields is empty?

 

Hi, thanks for getting back to me.

The 'Handle' field is the unique reference I could use should I need to add a related table(s).

You are correct in that this is a single flat table but should I need to create another table, I could use the 'Handle' field as the relationship join.

If a URL field is empty, this should result in no row being produced. Based on the data structure I have, if URL4 was empty the logic would be that URL5 and URL6 would also be empty and should be ignored.

I've updated my profile too.

 

Thanks for your help.

I see two possible solutions:

1. Find the records you want to export. Loop through the found set and create the CSV you want in a variable. Set a global container field to =

TextEncode ( $csv ; "utf-8" ; 1 )

(or similar, depending on the line-endings you want), then export this field's contents. This requires v.16 or higher.

2. Export the records as XML and use a custom XSLT stylesheet to transform the output to your CSV format. This was the preferred option prior to v.16 and it's still attractive due to not requiring any additions to your file's schema.

Both options require you to construct your own CSV, which is not entirely trivial: text fields that may contain commas and/or carriage returns must be enclosed in quotation marks; in-field quotation marks must be escaped.