Need to insert <alt><enter> into a CSV

[johnlopez2000]

I am creating a report from some data in Matrix. The long and short of it is that I need to write this to CSV, and then launch MSExcel with the data in it.

Where I am having issues is with the need to embed a newline after some data in a given cell.

You can do this manually in Excel by keyin some text, then <alt><enter>. The ASCII code for this is \034

However, when I attempt a substitution of the data delimeter with \034, all i get is the text string with a slash and 034, not the needed <alt><enter>.

Thanks

John Lopez
Enterprise PDM Architect
lopez.john@goodrich.com

 

[lakshmivaragan]

Hi John,
You are goin to save data in .csv files, right ?
Now, try this:

Write the CSV file in such a way that each row in excel is one line in the file. Excel automatically takes care of shifting the next line to its next row.

Take a look at the snapshot below:

set fd [open &quot;MyFile.csv&quot; w]
while {Not End of Data} {
set line [Bla-Bla-Bla] ;### Make a row ###
puts $fd $line ;## write each row one by one
}
close $fd

 

[johnlopez2000]

Thanks for your help!

I think maybe my problem description was not clear enough.

What I need to do is embed a <NL> in a string, so that the result will be a multiline string in a SINGLE cell.

When you are in Excel, if you wish to place &quot;hard returns&quot; in the string content of a single cell, <alt><enter> as a keystroke will accomplish this. I am trying to reproduce this programmatically to get the same result.

Thanks!

John Lopez
Enterprise PDM Architect
lopez.john@goodrich.com

 

[AviaTraining]

Okay, this is getting a bit tricky. I'm assuming that you're simply writing the data to a file with puts commands. In this case, Tcl is actually trying to help us out a bit too much here.

Part of Tcl's platform-independence magic is that it transparently handles the different end-of-line conventions: on Unix, it's a [tt]\n[/tt] (newline/linefeed); on Macintosh (at least OS9 and before -- I'm not sure about OSX), it's a [tt]\r[/tt] (carriage return); and on Windows, it's a [tt]\r\n[/tt] sequence. Tcl's default behavior is that when reading data from a channel (a file, a socket, a pipe, a serial port, etc.), it will accept any of these as an end-of-line indicator. When writing to a channel, Tcl automatically uses the end-of-line convention for that particular platform.

You can change this behavior with the fconfigure -translation option. The translation mode can be one of:
[ul][li][tt]cr[/tt]: Always use [tt]\r[/tt][/li]
[li][tt]lf[/tt]: Always use [tt]\n[/tt][/li]
[li][tt]crlf[/tt]: Always use [tt]\r\n[/tt][/li]
[li][tt]binary[/tt]: Don't mess with the data at all[/li]
[li][tt]auto[/tt]: Accept any EOL on reading; adopt the platform convention when writing[/li][/ul]
If you provide just a single value to fconfigure -translation, it uses that mode for both reading and writing. Alternatively, you can provide a two element list, where the first element sets the read mode, and the second element sets the write mode.

Okay, so how does this tie in to your situation? I'm assuming that you're trying something like this:

puts $fid "1,2,\"Split\nthis\""[code]

And when Tcl sees that embedded [tt]\n[/tt] in your string, it oh so helpfully translates it into a [tt]\r\n[/tt] in the data that it writes -- which isn't what Excel wants to see in CSV data.

The way we need to fix this is to change the translation mode to binary, so Tcl doesn't do that. But then we'll also need to explicity write out the [tt]\r[/tt] at the end of the line of data. (The [b]puts[/b] command will still automatically append the [tt]\n[/tt] for us, unless we use the [b]-nonewline[/b] option.) So, when I tested this out, Excel successfully read the CSV with wrapped lines:

[code]set fid [open data.csv w]
fconfigure $fig -translation binary
puts $fid "1,2,\"Split\nthis\"\r"
puts $fid "1,2,\"Don't split this\"\r"
close $fid

- Ken Jones, President, ken@avia-training.com
Avia Training and Consulting,

http://www.avia-training.com

866-TCL-HELP (866-825-4357) US Toll free
415-643-8692 Voice
415-643-8697 Fax

 

[johnlopez2000]

Ken,

thanks again for the help. I quess I need to do some explaining.

I get a string returned from a query that looks like:

param1,param2,sub1:sub2:sub3,param4,param5

The intent is, per column mapping in Excel:

A=param1
B=param2
C=sub1:sub2:sub3
D=param4
E=param5

Column C will have multirow string consisting of:

sub1\nsub2\nsub3

Because of the way I build the query, I do the following to subsitution:

regsub -all -- &quot;:&quot; [mql print bus $xID select state.name dump &quot;:&quot;;] &quot;\n&quot; y1

this is done iteratively until the full content ofthe file is created. THEN i do open/puts/close

Thanks

John Lopez
Enterprise PDM Architect
lopez.john@goodrich.com

 

[AviaTraining]

Okay.... This is going to be a bit trickier. Because for properly formatted CSV data, any string with embedded newlines needs to be quoted with double quotes. For example, the result for your data needs to look like:

param1,param2,"sub1
sub2
sub3",param4,param5

What we need to do then, is grab the field(s) with colon-separated subfields, put double quotes around it, and substitute all &quot;:&quot; characters with newlines. Although it might be theoretically possible to do all that in a single regsub command with a single regular expression, I don't feel up to putting that beast together.

Instead, I'm going to break it down into two separate substitutions. First, I'll use regsub to locate all field(s) with colon-separated subfields. As the replacement text, I'll put double-quotes around the matched text. Then, I'll use Tcl's string map command to perform the colon-to-newline replacement. (string map was introduced in Tcl 8.1.1. It basically performs simple string substitutions, much like a regsub with no wildcards, but can handle multiple A-to-B translations in a single pass. It's much faster than using regsub for non-wildcarded string substitution. In fact, Tcl 8.4 introduced an &quot;under-the-hood&quot; optimization that basically translated non-wildcarded regsubs at the bytecode level to equivalent string maps.)

# Here's our test data

set data {param1,param2,sub1:sub2:sub3,param4,param5}

# Create a regular expression that will match a field
# containing an arbitrary number of colon-separated
# subfields.

set pat {[^,:]*(:[^:,]*)*:[^,]*}

# Run our regsub command to find all colon-separated
# subfields and double-quote them.

regsub -all $pat $data {"&"} result

# Now map all our colons to newlines.

set final [string map [list : \n] $result]

# Let's look at what we've got

puts $final

If this seems to work for you, it would be best, of course, to stick it all in a procedure to hide the grungy details.

Then, you can take all these massaged strings and write them out to your CSV data file, like we discussed earlier.

- Ken Jones, President, ken@avia-training.com
Avia Training and Consulting,

http://www.avia-training.com

866-TCL-HELP (866-825-4357) US Toll free
415-643-8692 Voice
415-643-8697 Fax