Double quotes and carriage returns truncate row from text file

[mokaplanjr]

I have set up a text file DSN and a query that successfully pulls data from the text file. The file is set up with pipe (|) delimiters. This is a read-only situation; the text file will be uploaded via a web form... already set up. It never gets updated by the web application.

The problem is that a field with text (set in the schema.ini file as data type "LongChar&quot sometimes has double quotes as part of the text. It may also have carriage returns. This is the way it comes from the original database and, due the nature of the original database's use, can't be changed.

When looking at the text file directly, the data is all there, but the query cuts a row off when it hits either the double quote (so the phrase 'THE FISH IS 12" LONG' becomes 'THE FISH IS 12' -- then the row ends. The same thing happens with carriage returns.

Anybody out there with experience preventing such "illegal" characters as double-quotes and carriage returns from truncating a text field that comes from a flat text file? Could a different delimiter help (although I chose the pipe because it is the least likely to be keystruck when entered with the original data.

Or would changing the field's data type in the schema.ini make a difference?

 

[alaistair]

I presume you are using <CFHTTP>, if you don't set the textqualifier to anything it defaults to &quot; this could be causing your problem, if you are using nothing to 'wrap' your text fields in the text file then set textqualifier=&quot;&quot; i.e. no character wrapping a text field. This may work!

Cheers,

Al

 

[mokaplanjr]

I set it up initially using an ODBC connection to the text file and basic queries to pull the data out.

But I changed to CFHTTP based on Al's response above. Setting -- textqualifier=&quot;&quot; -- allows the query to ignore the double quotes (hat tip to Al), but when the culprit text field has a carriage return, then the row is truncated and the query moves on to the next record.

I emailed the database folks to find out if they can output the textfile with carriage returns stripped, which would fix the problem, but I would really like to know if it's possible to work with a text file that still has them.

My question to the database people also asked if the output file can have carriage returns within the text field replaced with a preset character or combination to use the ReplaceList function (&quot;<br>&quot; would be nice, now wouldn't it?).

If anybody has any more ideas, drop by. I have donuts.

 

[GunJack]

Hey mokaplanjr,

If it were me, I would write an application in Visual Basic to open the text file and do all of the clean-up in it before CF processes it. This way, you can assure that none of the rows are split up. You could compile the VB program as a .Com object and then run it through CF with the <cfobject> tag.

I don't see anyway to change how the dsn will interpret the file so I think the best way is to just pre-process it.

Hope this helps,
GJ

 

[dbantwal]

Hi,
I am facing the similar problem (field being truncated) due to the single and souble quotes in the string. I did try out the textqualifier=&quot;&quot; but this did not seem to work .
For the records which have single/double quotes, on opening the table shows a Deleted sign (#Deleted!).
I am using a simple plain flat file.
herez the sample
[tblArea.txt]
ColNameHeader = False
CharacterSet = ANSI
Format=Delimited(~)
textqualifier=&quot;&quot;
Col1=aID Double
Col2=aAlternateLongName Char Width 64
Col3=aShortName Char Width 12
Col4=atID Double
Col5=aParentID Double
Col6=aMainsLength Double
Col7=aHoursPerDayFactor Double
Col8=aAZNP Double
Col9=aPopulation Double
Col10=aPropertyCount Double
Col11=aNoConnections Double
Col12=aOccupancyRate Double
Col13=aAvPropHeight Double
Col14=aMaxPropHeight Double
Col15=aMinPropHeight Double
Col16=aInletPress Double
Col17=aAZP Double
Col18=aMainCond Char Width 1
Col19=aMiscUse Double
Col20=aOpUse Double
Col21=aPZT Double
Col22=aDMAType Double
Col23=aHouseholdUse Double
DateTimeFormat='dd/mm/yyyy hh:nn:ss'
Col24=aLastUpdatedDatetime DateTime
Col25=aLastUpdatedUser Char Width 30

The actual data looks like this
2616~BOTTOM'O'TH'MOOR &quot;A&quot;~210-05~6~652~10.34~23.3~75~440~176~0~2.5~240.77~304.26
~0~0~72.813~V~9.744087E-04~1.942609E-03~1~-1~1.52~13/06/01 14:50:53~Sean Greenwo
od~Y~ ~V~06/06/01~06/12/01 12:28:00~06/12/01 12:28:00~ ~ ~ ~06/12/01 12:28:00~0~
~0~0~20/11/01~J Hanley~20~0~~01/01/1900~4~20~~~~~~ ~ ~ ~0

The problem is with the 2nd field (tilde delimited)

Can Any1 help me on this ?
Thanks
deena