I have a CSV file generated from an Excel spreadsheet that has the $ symbol in several data columns.

When I read the file with WebFOCUS, I get the following error:

(FOC229) ERROR READING EXTERNAL DATA FILE: NEWFUND
Symbol '$' outside of text field is illegal : line 1

If I enclose the data columns in double-quotes, the file can be read without errors.

I can't get Excel to save the CSV file with double-quotes.

Any ideas?

how about reading the file as a single record, really long record; and in the master ,ctran the $ out to a blank before you begin,
or STRIP out the $
TABLE FILE it out to a text file,
and write a master as a DFIX file, with FIELDNAME=DELIMITER ,ALIAS=',' ,USAGE=A1 ,ACTUAL=A1 ,$

This message has been edited. Last edited by: susannah,

Hi Francis,

You can force Excel to enclose these fields within double quotes by adding a comma at the end of the value within the cell. My guess is that this is going to cause you more problems though?

Apart from suggesting using a macro in your normal.dot for Excel so that you can call it to bound each cell in double quotes and save as a .csv file, the only other questions that springs to mind are -

Do you have to use csv format?
Would XML be a possibility?

Good luck

T

We receive an Excel spreadsheet that must be read by Data Migrator to load the data into a SQL Server table.

We're converting the Excel spreadsheet to CSV. The $ sign in the text must stay.

I haven't played with XML files - does Excel export the data AND the markup? How easy is it to create a Master?

Cheers.

I've added the XML adapter via the WebFOCUS server console.

I've saved the spreadsheet as an XML file. No DTD was created.

I am attempting to create a synonym for the xml file. The server console is hanging - either because the DTD does not exist or I have to do more than simply add the XML adapter. The documentation on this is quite unclear.

F, did you try reading the csv via a master that was SUFFIX=DFIX rather than SUFFIX=COM
and put in the field DELIMITER=',' at the end of the master?
that avoids having to SET PCOMMA=ON which does who-knows-what under the covers. old csv files had a $ as the end-of-record indicator, which might be why you're having the problem, but if you read it as a DFIX , you might avoid all that, reading the file as if it were, say, pipe-delimited, just you're using a comma instead of a pipe.

Perfecto!!!

Susannah, that worked fantastically.

In our case, there would never be a comma in the data, so the comma works as delimiter.

Thanks a bunch.

Francis.

F or T, is the xml adapter an extra cost item? do you happen to know?

Easy fix found is always nice.

However I found this macro a while ago that could help.

It does not make any distiction between numerics and text but wraps everything in double quotes
so you need your master the have an actual of Axx and a usage of what you need.


This macro surrounds each field with quotation marks, doubling any quotation marks found in the cell text:

Public Sub OutputQuotedCSV()
Const QSTR As String = """"
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

nFileNum = FreeFile
Open "File1.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," &QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub

The source was http://www.mcgimpsey.com/excel/textfiles.html#csvwithquotes

They have a couple of other examples where you can write an output file using an alternate
delimiter or generate a fixed length file.

JG, thanks for the info. I'll keep it for reference.

If I were a betting man (and I'm not) I would have won a substantial amount on the chances of JG having a macro up his sleeve!!

Another one for the archive ... Thanks JG