Solved

How to trim blanks within MS Visual FoxPro VIEW

Posted on 2011-03-08
5
1,107 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 500 total points
Comment Utility
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
Comment Utility
Thanks. Then i will TRIM at the Excell side.
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now