Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to trim blanks within MS Visual FoxPro VIEW

Posted on 2011-03-08
5
Medium Priority
?
1,573 Views
Last Modified: 2012-06-27
Hi Experts,

I have problem with extracting data from FoxPro to Excel.

1. I have defined FoxPro VIEW with ALLTRIM function to remove blanks from text fields;
2. Then within Excel I use ADODB.Connection and ADODB.Recordset to get data from FoxPro VIEW;
3. Then I use "CopyFromRecorset" to copy data from recordset to excel.

BUT the problem is that there is BLANKS at the end of text fields after they are copied to excel.

It seems ALLTRIM has not done its work. Any ideas.
0
Comment
Question by:Krukanens
  • 2
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
CaptainCyril earned 2000 total points
ID: 35070720
Text Fields in FoxPro are fixed width. Even if they are trimmed they will be padded with space. If the field is empty, it would be SPACE(1) times the field width.

Exporting Memo to Excel does not work.
0
 

Author Closing Comment

by:Krukanens
ID: 35070819
Thanks. Then i will TRIM at the Excell side.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 35071029
It seems you are satisfied with negative answer...

Nothing against your decision but did you try to convert character columns into varchar data type?

E.g.:

SELECT CAST(TRIM(Col1) as varchar(20)) Col1, CAST(TRIM(Col2) as varchar(20)) Col2 FROM SomeTable

0
 

Author Comment

by:Krukanens
ID: 35079361
Hi, pcelba

Did you mean CAST inside FoxPro VIEW. I tried but unsuccessfully because it seams there is no CAST function and VARCHAR data type in FoxPro 6.0 SP4 I am using.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 35081413
Yes, VFP 6 does not support CAST but ADODB.Connection can access data using VFP OLE DB provider which is derived from VFP 9 and it should work.

The problem could be the fact you are defining the View in VFP 6 DBC. In such case CAST cannot work.

You may also use a query from Excel directly.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Did you know there are services out there that can turn an Instagram feed into an RSS feed? I found some interesting exclusive Instagram content which I wanted to follow without signing up for yet another social media account. RSS to the rescue!
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question