<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
The errors I was seeing when trying to import the DIF file was
caused by the line feeds without the carriage returns along with
them it seems. When I looked at the data in Notepad++ there was a
CR,LF at the each cells data except for the long description that
had a couple of LF's by themselves in the middle of it. Once I
changed those to a ~ using the "replace" in excel the errors went
away. That solved my biggest problem which was just getting the data
into filepro so I could manipulate it.<br>
<br>
Now that the data is imported I'm seeing something weird in a few
places that wasn't showing up, or at least I didn't see it before
the import that looks like this "─". When I look at the data in
Notepad++ and I select show all characters I can see all of the
LF's, CR's and spaces(which look like little orange dots I guess)
but if I look at the space where the "_" is showing up in the
imported data it's just shows a totally blank space in Notepad++. I
know nothing at all about Base64 but just playing around I converted
a space to Base64 and got this "IA==" and when I converted one of
the spaces that wound up turning into the "_" I got this "oA==" if
that means anything at all.<br>
<br>
This the data before the import...<br>
"Keep your fish healthy and thriving by testing your aquarium
water's NO3 levels regularly with this easy-to-use API NITRATE TEST
KIT for freshwater and saltwater aquariums. Testing water parameters
weekly helps prevent invisible water problems that can be harmful to
<b>fish. When</b> left uncorrected, high nitrate in aquarium water
can lead to a build-up of organic pollutants and poor fish health.
Testing is fast, easy, and <b>accurate. The</b> API NITRATE TEST
KIT for freshwater and saltwater aquariums tests for harmful nitrate
levels from 0-160 ppm. This product comes with one nitrate bottle,
one capped glass test tube, one instruction manual and one color
card. The API NITRATE TEST KIT for freshwater and saltwater
aquariums Test Kit can be used in freshwater and saltwater
aquariums." <br>
<br>
this the data after the import...<br>
"Keep your fish healthy and thriving by testing your aquarium
water's NO3 levels regularly with this easy-to-use API NITRATE TEST
KIT for freshwater and saltwater aquariums. Testing water parameters
weekly helps prevent invisible water problems that can be harmful to
<b>fish.─When</b> left uncorrected, high nitrate in aquarium water
can lead to a build-up of organic pollutants and poor fish health.
Testing is fast, easy, and <b>accurate.─The</b> API NITRATE TEST
KIT for freshwater and saltwater aquariums tests for harmful nitrate
levels from 0-160 ppm. This product comes with one nitrate bottle,
one capped glass test tube, one instruction manual and one color
card. The API NITRATE TEST KIT for freshwater and saltwater
aquariums Test Kit can be used in freshwater and saltwater
aquariums."<br>
<br>
If I had a clue what those spaces were that are being converted into
"_"' I could just replace them with actual spaces before importing
the data I think. Maybe what I should try now is first changing the
LF's to the "~" then just use the CLEAN in excel to see if that
removes them but I'd still be curious at to what they actually are.<br>
<br>
Mike<br>
<br>
<br>
<br>
<br>
<br>
<br>
<div class="moz-cite-prefix">On 8/29/2017 5:54 PM, Brian K. White
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:39e34277-605a-7a78-ab4a-abeab591382f@aljex.com">On
8/29/2017 2:31 PM, Mike Fedkiw via Filepro-list wrote:
<br>
<blockquote type="cite">I'm using DIF files for importing and when
the import file has line feeds it tells me that it's not a valid
DIF file. I'm pretty sure it's line feeds and not carriage
returns causing the issues for me because when I did a search
for Ctrl+J and it didn't find anything.
<br>
<br>
Just to test it out, I used excels CLEAN and remove all of the
CR's and LF's before importing the data which worked and I
didn't get any errors. The problem is now everything is lumped
together into one big paragraph which is was trying to avoid in
the first place.
<br>
<br>
If I added an "~" or something unique to the beginning and the
end of the cells with the line feeds before saving it as a DIF
for importing, and I'm telling filepro that the ("~" or
whatever) is the field marker, is it still going to give me the
not a valid DIF file error if that field still contains line
feeds. And if it that actually works, will the line feeds
actually show in filepro after the import.
<br>
<br>
If this enables me to import the data into the memo with all of
the line feeds intact that would be great although I'm sure I'll
be needing to change them to something else before exporting if
filepro is going to be stopping the export after the first LF
because it'll recognize it as an end of field marker similar to
CR's. *Robert Helbing* posted a nice example of how he goes
about doing that though so that'll be a big help, :)
<br>
<br>
<br>
* Or maybe pre-processing the data is the most practical way to
go after all.
<br>
<br>
I'm always pre-processing the data in the worksheets I receive
from my vendors anyway so one more step isn't going to be an
issue.
<br>
<br>
* If there are CR's or LF's or both within the cells, then how
ARE the records delimited, if not by CR? Maybe you don't need to
do anything but adjust the record delimiter option on the import
command line in processing. That would be much better than
cobbling together some external pre-processing steps.
<br>
<br>
I just save the XLS file as a DIF after I sort out and arrange
the fields for the import. I always know how many columns are in
the import file so I just do an END after the last one when
doing the import, I'm not actually looking for delimiters.
<br>
</blockquote>
<br>
fp is looking for a delimiter.
<br>
<br>
I never use dif so I don't know whether lf or cr are supposed to
be valid within a field, and the format incorporates some special
way of delimiting the records, or if it internally encodes the
conflicting data within cells, and maybe fp is not importing fully
correctly, or if fp is importing correctly and it's excel that's
creating a technically invalid dif export.
<br>
<br>
What I do know is that if you are controlling the export, then you
can arrange it so that the the cells contain only LF's for new
lines embedded within cells, and then either CRLF or only CR are
for the record delimiter on export, save as csv, and then in fp
you can use
<br>
import ascii foo=/path/to/foo.csv r=\r f=, o=" c="
<br>
and you will have embedded newlines in cells no problem.
<br>
<br>
Given a sample csv file like this (the ^M are really CR's, and the
line breaks are LF's, the records end with CRLF, the LF are
"invisible" here except for the fact that they cause line breaks):
<br>
<br>
This csv file was exported from LibreOffice on Windows, but I only
have filepro on linux, so the code may need adjusting for filepro
on windows.
<br>
<br>
/tmp/crlf_exported.csv:
<br>
"row1 field1 line1
<br>
row1 field1 line2
<br>
row1 field1 line3","row1 field2 line1","row1 field3 line1"^M
<br>
"row2 field1 line1","row2 field2 line1
<br>
row2 field2 line2","row2 field3 line1"^M
<br>
<br>
<br>
<br>
@keyi If: '*** import csv w/ embedded linefeeds ***
<br>
Then: r = "0"
<br>
getcr If:
<br>
Then: import ascii csv=/tmp/crlf_exported.csv r=\r f=, o="
c="
<br>
If: not csv
<br>
Then: close csv ;end
<br>
If:
<br>
Then: r = r + "1" ;msgbox "row"<r<"cell 1 =
\""&csv(1)&"\""
<br>
If:
<br>
Then: goto getcr
<br>
<br>
<br>
┌───────────────────────────────────┐
<br>
│ row 1 cell 1 = "row1 field1 line1 │
<br>
│ row1 field1 line2 │
<br>
│ row1 field1 line3" │
<br>
└─────────────────── Press Enter ┘
<br>
<br>
<br>
┌─────────────────────────────────────┐
<br>
│ row 2 cell 1 = "row2 field1 line1"" │
<br>
└───────────────────── Press Enter ┘
<br>
<br>
There is still a little bug to figure out. In the 2nd row an extra
" appears in the 1st field. I don't know why but if it were me
this would just be an ordinary sort of thing to track down by a
little more trial & error.
<br>
<br>
The basic functionality is proved, that:
<br>
<br>
* A field can be imported with embedded LF's properly, by the fact
the the msgbox for the first row has the right contents for the
1st field. It didn't chop off at the first lf, nor did it go
beyond the field delimiter into the next field.
<br>
<br>
* The entire record breaks at the right place, by the fact that
the msgbox for the 2nd row has -generally- the right contents for
the first field of the 2nd row. There is an extra quote in there,
but it still didn't break the record on any previous linefeeds.
<br>
<br>
The extra quote character might be from the extra LF in the CRLF
line endings? It was saved with normal windows CRLF line endings,
but in fp by specifying r=\r, I told fp to only look for a CR, and
so the LF might be being ignored automatically by fp, or it might
be appearing as a character in the last cell, or who knows what.
<br>
<br>
That's just a detail still to be worked out, and I'm sure it works
fine once you clear up that last detail one way or another.
<br>
<br>
Having to work out things like this by systematic experiment and
elimination is just what it takes to program anything at all. I
had to do several things just to get this example this far.
<br>
<br>
The first time, I tried import word instead of import ascii,
because generally for csv, it's slightly more convenient because
you don't have to specify the r= f= o= c=. It's like it should
have just been named "import csv". But that didn't work. It broke
the records on the linefeeds, because I'm on linux and that is the
default record delimiter on any of the unix variants.
<br>
<br>
Then, my file was in /tmp/crlf_exported.csv, and I had that path
in quotes on the import line, and that didn't work. It tried to
import from /u/global/appl/fpmerge/tmp/csv_exported.csv. I found
in the help files that the examples showed the filename without
quotes. Sure enough without the quotes it worked.
<br>
<br>
That's just what it takes to get any job done ever anywhere.
<br>
<br>
Maybe DIF can't handle embeddded linefeeds while csv can, or maybe
you could try SLK instead of DIF, or maybe DIF works equally well
as csv, and you just need to figure out the detail the same way I
just did for csv.
<br>
<br>
</blockquote>
<br>
</body>
</html>