Mutiple Line Field adding line breaks

[Kristie]

Hi,
I have a multiple line field that is working well on the model and is loading into the SQL table but there are characters between each line that are making it not work so well when linking Access tables or excel queries: It looks like two line breaks are added between lines in the field and so it is truncating the multiple lines in Access. (and adding a merged field in Excel).

Any ideas on how to get around this? thanks!!!

[Data Kruncher]

I'm not certain as to what's causing Excel merge cells, but Access might be seeing an ASCII value that it's not expecting for that data type and will truncate the field contents when it encounters that ASCII value.

I suspect that it's either ASCII 10 or 13, and that brings up a bit of an interesting finding that maybe Gareth or Ken can shed some light on. This may have come up before here; I don't recall.

I thought that a possible solution might lie in replacing all occurrences of ASCII 10 and 13 with a space, but when I built the appropriate calculated field Monarch didn't return the results that I expected.

As a test, I used the formula Asc(Chr(1)) and tested each value from 1 to 20. Interestingly, the formula returned the test value in all cases, except for 10 and 13. In those cases, it returned zero. :confused:

Sorry Kristie, this doesn't get you any further ahead at the moment.

[OllyInMunich]

Hello Kristie,

In Monarch, under Options, Input, there's an option to tell Monarch to "ignore unused print control characters (0-31)". Try that first.

More diagnosis can be found using the utility from:http://www.datawatch.com/_support/downloads_updates.php called "Examine".

HTH,

Olly

[Kristie]

Thanks, I set that option and will see how it plays out with tomorrows data load.

[Kristie]

I set the option to ignore unused print control characters which didn't work. I downloaded the "Examine" tool but honestly I cannot figure out how to work it. Any ideas?
thanks

[Data Kruncher]

You have to run Examine from the DOS command line. Before doing so, it might helpful to copy both the Examine.exe program and your report file to a c:\test folder, and rename the report to simply report.txt

Now click your Windows Start button, select run and key "cmd" and hit Enter.

Type:

c:
cd \test
examine report.txt

hitting Enter for each line. The Examine program will ask you where to send the output. Just hit Enter to display it on the screen.

That said, I'm not certain that this will reveal any useful insights for you. What does Examine report back to you?

Kruncher

[Kristie]

Hi,
Yes this does not tell me much that I can understand: :)

Ascii 87 W = 44572
Ascii 88 X = 9579
Ascii 89 Y = 83860
Ascii 90 Z = 12585
Ascii 91 [ = 2
Ascii 93 ] = 9
Ascii 95 _ = 881
Ascii 97 a = 435
Ascii 98 b = 15
Ascii 99 c = 80
Ascii 100 d = 19
Ascii 101 e = 758
Ascii 102 f = 341
Ascii 103 g = 25
Ascii 104 h = 47
Ascii 105 i = 62
Ascii 106 j = 11
Ascii 107 k = 12
Ascii 108 l = 63
Ascii 109 m = 401
Ascii 110 n = 417
Ascii 111 o = 121
Ascii 112 p = 14
Ascii 113 q = 4
Ascii 114 r = 710

[Grant Perkins]

Hmm.

The examine output is showing you all the characters (visible and invisible) in the file and giving a count.

For completeness, which version of Monarch are you using?



Grant

[Kristie]

Sorry for the delay. I am using version 10.5 of Monarch.
thanks again for your insight.

[Grant Perkins]

Hi Kristie,

The Examine output you posted looks like it is only part of the file - either that or you have very unusual file contents.

If you let it run its output to a text file we should be able to see more. Somewhere it will tell us how many of each text format control character there are in the file. It provides a reference point for the scale of the problem, especially if we know how many records there are on the input and output sides.

I read your initial post to mena that you are creating a table in Monarch and then exporting it to an SQL database. From there you are linking Access and Excel to the SQL data when producing queries. Is that correct? So what you are seeing is a problem with the SQL query within Access or Excel when you link to the previously extracted data. Is that correct or have I misunderstood?


Grant.

[Kristie]

Here is the entire examine output:


D:\Monarch\Examine>EXAMINE 20DSP002.TXT
Press 'Y' or 'y' & <Enter> to send to printer
or just <Enter> to display on screen.

Ascii 10 LF = 263449
Ascii 12 FF = 4391
Ascii 13 CR = 267838
Ascii 32 SP = 15584290
Ascii 33 ! = 403
Ascii 34 " = 1273
Ascii 35 # = 12012
Ascii 36 $ = 3656
Ascii 37 % = 1273
Ascii 38 & = 2824
Ascii 39 ' = 1248
Ascii 40 ( = 4908
Ascii 41 ) = 4300
Ascii 42 * = 42838
Ascii 43 + = 5937
Ascii 44 , = 152158
Ascii 45 - = 70559
Ascii 46 . = 306602
Ascii 47 / = 327254
Ascii 48 0 = 1157281
Ascii 49 1 = 915407
Ascii 50 2 = 429801
Ascii 51 3 = 247903
Ascii 52 4 = 263490
Ascii 53 5 = 241497
Ascii 54 6 = 286585
Ascii 55 7 = 224493
Ascii 56 8 = 217726
Ascii 57 9 = 322829
Ascii 58 : = 17253
Ascii 59 ; = 189
Ascii 60 < = 78
Ascii 61 = = 1334
Ascii 62 > = 222
Ascii 63 ? = 356
Ascii 64 @ = 399
Ascii 65 A = 545222
Ascii 66 B = 112720
Ascii 67 C = 389602
Ascii 68 D = 320481
Ascii 69 E = 606215
Ascii 70 F = 120728
Ascii 71 G = 89079
Ascii 72 H = 233880
Ascii 73 I = 416640
Ascii 74 J = 32886
Ascii 75 K = 127938
Ascii 76 L = 286276
Ascii 77 M = 172203
Ascii 78 N = 354211
Ascii 79 O = 413244
Ascii 80 P = 196014
Ascii 81 Q = 8846
Ascii 82 R = 420091
Ascii 83 S = 386175
Ascii 84 T = 413659
Ascii 85 U = 129227
Ascii 86 V = 144189
Ascii 87 W = 44572
Ascii 88 X = 9579
Ascii 89 Y = 83860
Ascii 90 Z = 12585
Ascii 91 [ = 2
Ascii 93 ] = 9
Ascii 95 _ = 881
Ascii 97 a = 435
Ascii 98 b = 15
Ascii 99 c = 80
Ascii 100 d = 19
Ascii 101 e = 758
Ascii 102 f = 341
Ascii 103 g = 25
Ascii 104 h = 47
Ascii 105 i = 62
Ascii 106 j = 11
Ascii 107 k = 12
Ascii 108 l = 63
Ascii 109 m = 401
Ascii 110 n = 417
Ascii 111 o = 121
Ascii 112 p = 14
Ascii 113 q = 4
Ascii 114 r = 710
Ascii 115 s = 704
Ascii 116 t = 405
Ascii 117 u = 36
Ascii 118 v = 10
Ascii 119 w = 15
Ascii 120 x = 2
Ascii 121 y = 355
Ascii 122 z = 3
Error! Mismatched CR/LF combinations.
Use MSRP to match CR's to LF's.

This file does not contain any extended characters.
27460135 Total characters counted.


I am putting the data in an SQL table and then using excel to query the table for only the records needed. But the multiple lines are not showing even though they are there just with a break in just that field not the rest of the line.

Thanks again!

[OllyInMunich]

Hello Kristie,

27 MB of text shouldn't be too big, so I don't think it's a file size issue.

The Examine output is prompting you to download MSRP (Monarch String Replacement Program, I think) from the Datawatch downloads page again. You don't need to.

Just run the Monarch Utility and select the "prepare files for Monarch..." button, then once you've selected the input and output filename, check the option replace all occurrences of a string, and I think I'd try two routes:

a) get rid of LF (\10)

b) replace \13 with \13\10, then replace \10\10 with \10 to trim the duplicate LFs.

Experiment, and you should find a fix fairly easily...

Best wishes,

Olly