Solved

How to trim blanks within MS Visual FoxPro VIEW

Posted on 2011-03-08
5
1,280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
CaptainCyril earned 500 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 42

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 42

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

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…
How many times a day do you open, acknowledge, or close an IT incident? What’s your process? Do you have a process depending on the incident, systems involved, and other factors? New Relic Alerts gives you options for how you interact with notifica…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Suggested Courses

752 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