Import of Excel files that contains CRLF into the cells

StefanG — Fri, 04 Apr 2014 08:31:49 GMT

I have an Excel file that contains CRLFs into the cells.

In EG 4.1, when this file is imported, in the resulted dataset CRLFs are replaced with blanks.

However, in EG 5.1, when same file is imported, in the resulted dataset CRLFs are replaced with dots.

For example:

In Excel cell there is the following string:

(((

(ISP_GROUP1_SDESC IN ("A_Internet","A_Internet_FO","B_VPN","C_Frame Relay", , "B_VPN_Layer2") AND PRICE_PLAN_TYPE_LDESC NOT IN ("Fixed Internet over Fixed Voice"))

PRICE_PLAN_TYPE_LDESC IN ("Fixed Internet over Fixed Voice")

)

AND HFM_SEGMENT_SDESC = "TFSG110"

AND MASURA IN ("TD_ISP","CB_ISP","TA_ISP","OHP_CB_ISP_M1"))

(COD_ACCOUNT="E8049000" and COD_2="TFSG110" and COD_1="MVCB_ISP")

)

When the cell is imported in EG 5.1, the string becomes (partial output):

(((.(ISP_GROUP1_SDESC IN ("A_Internet","A_Internet_FO","B_VPN","C_Frame Relay", , "B_VPN_Layer2") AND PRICE_PLAN_TYPE_LD

Notice the dot after the first three open parenthesis that replaces the CRLF in the original string.

As the intention is to use the initial Excel file to build a SQL phrase, the dot which appears leads to syntax errors.

Is there any way to circumvent the problem (other that eliminating CRLFs from original file)?

Thank you

Stefan

Re: Import of Excel files that contains CRLF into the cells

RW9 — Fri, 04 Apr 2014 08:46:19 GMT

Hi,

Sorry, I can't think of a workaround other than as you say, remove CLRF from the original file. Maybe have a small VBA macro to do it on save?

I wouldn't recommend Excel for what you are trying to achieve either. What you appear to be doing is using Excel to store some sort of metadata or part code and then read that in and create code from it. Excel's strength and weakness is its flexibility. If you are set on going forward with that kind of process I would suggest you need a lot more functionality to get it working. Code checking, validation etc. which you could build into the Excel file in VBA. Its one of the problems I am facing at the moment as we have base SAS and want to store partial code/metadata in SAS datasets, however the fsedit window is useless for data entry. I believe that SAS do software for things like this, though there is a cost involved. Alternatively you could develop a database yourself. I saw at phuse a chap using RDF/OWL to store metadata for instance.

topic Import of Excel files that contains CRLF into the cells in SAS Enterprise Guide

Import of Excel files that contains CRLF into the cells

Re: Import of Excel files that contains CRLF into the cells