Text export

[DigPeter]

In the text export app, the record delimiter is restricted to cr/lf or lf only.  I want to export fields that contain cr/lf (Ctrl+enter).  How can this be done witout splitting the the field into 2 records?

[Mario]

Î don' understand.

The typical line endings are either:

+ Carriage return/Linefeed (0x13,0x10 - Windows) or
+ Linefeed only (

Code Select Expand

0x10) Linux, Mac.

The text export offers by default these two choices.
Since you cannot type in these binary characters, the  text export offers the special tags listed in the help:

{cr} : 0x13 (carriage return)
{lf} : 0x10 (line feed
{tab} : 0x09 Tabulator

This allows you to combine then as you need.
If you want to delimited your records with the word NEWLINE, just type it in. It's text and the Text Exporter will use whatever you want.

[DigPeter]

I want to export metadata to a csv file so that the selected fields are displayed in Excel in columns, with a data for each selected image file on its own line.
The fields are "File name", "Title" and Description.  The description field has punctuation, including ';' and has a line break (Ctrl+enter). I use Text export with parameters set as sown in the attachment "export.jpg".  The image attachment contains 2 of the selected image files.  The next post contains a 2nd attachment with the 3rd image file and a csv file explaining the problem.

Clearly I am not achieving what I wish.

[DigPeter]

This post contains the other attachment explained in my last post.

[Mario]

CSV files use CRLF as the record delimiter.
If your data contains CRLF this breaks the file format. You cannot use a record delimiter that is part of your data.
You need to remove or replace this it before you write it to the CSV file.

For example:

{File.MD.XMP::dc\description\Description\0|replace:{cr}{lf}==__BREAK__}

replaces the CRLF contained in your description with the word __BREAK__.
After importing it into Excel you can use a Search/Replace in Excel to replace the __BREAK__ with a CR/LF again.

Excel cannot import CSV files which do not use CRLF as the record delimiter.

[DigPeter]

Thanks for the as ever prompt reply.  I am just about to leave for a short break and will try this out when I return. 

What about the first field of each record in the excel including the the pipe field separators and the title and part of the desccription?

[Mario]

Did you check the CSV file you have produced in Notepad? Does it look correct?
I have created a CSV file using your settings and Excel imports it just fine.

Have you tried using a more standard column separator like {tab} ?

[DigPeter]

@Mario
I use Ctrl+enter for a newline.  The variable {File.MD.XMP::dc\description\Description\0|replace:{cr}{lf}==__BREAK__} does not work, because Ctrl+enter is code is {lr} on its own, so by using {lf}==__BREAK_, it can eleiminate the newline.

My descriptions contains normal punctuation. including commas.  As it says on the tin, a csv file will translate these as field delimiters.  It does not recognise {tab}.  But I have found that by creating text with a tab delimiter and copying the text direct into Excel, I get a correct spreadsheet.

[Mario]

Just use {File.MD.XMP::dc\description\Description\0|replace:{lf}==__BREAK__} then.

Excel is VERY picky when it reads CSV files. You need to find a format which works for you. If you use LF or CRLF in your descriptions you will run into problems when you plan to exchange this data with CSV. This is not a fault of IMatch, it's just that CSV has not been designed for this purpose.

[Ger]

Would it be possible to use Mario's replace technique and, after importing in Excel, run a small macro over your selection, replacing the __BREAK__ value with Chr(10):

Code Select Expand


Sub AddCRinCell()
 
  Dim thisRange As Range
  Dim x As Byte
 
  Set thisRange = Range("A1:A6") ' Select the data range you want to add line feeds
  For x = 1 To thisRange.Rows.Count
    thisRange.Cells(x, 1) = Replace(thisRange.Cells(x, 1), "__BREAK__", Chr(10))
  Next x
 
End Sub



Ger

[DigPeter]

Would it be possible to use Mario's replace technique and, after importing in Excel, run a small macro over your selection, replacing the __BREAK__ value with Chr(10):

Code Select Expand


Sub AddCRinCell()
 
  Dim thisRange As Range
  Dim x As Byte
 
  Set thisRange = Range("A1:A6") ' Select the data range you want to add line feeds
  For x = 1 To thisRange.Rows.Count
    thisRange.Cells(x, 1) = Replace(thisRange.Cells(x, 1), "__BREAK__", Chr(10))
  Next x
 
End Sub



Ger

Possibly, but I am not into programming etc.  Thanks for the idea.  I would use find and replace.

[Ger]

Here's a very simple excel macro sheet:

- Copy your data in the excel sheet
- Select the area (cells) you want to replace __BREAK__ with CR
- Click the 'Add CR in Selected Cells' button
Done

If you want to change the search term (currently __BREAK__):
- right-click the 'Add CR in Selected Cells' button
- select 'Assign Macro'
- select 'Edit'
- change the value in the line 'Const ReplaceTerm...'
- close the macro window

Ger

[Mario]

Thanks for helping    

[DigPeter]

Thank you Ger.  I will try that.