Solved

How to trim blanks within MS Visual FoxPro VIEW

Posted on 2011-03-08
5
1,229 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
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

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…
This article describes my battle tested process for setting up delegation. I use this process anywhere that I need to setup delegation. In the article I will show how it applies to Active Directory
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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