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?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 20, 2006, 05:15 PM
susannah
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,
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
June 21, 2006, 03:39 AM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
June 21, 2006, 10:12 AM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 21, 2006, 10:37 AM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 21, 2006, 11:11 AM
susannah
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.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
June 21, 2006, 11:35 AM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
June 21, 2006, 12:34 PM
susannah
F or T, is the xml adapter an extra cost item? do you happen to know?
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
June 21, 2006, 12:55 PM
<JG>
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