Solved

How to trim blanks within MS Visual FoxPro VIEW

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

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 41

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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…
When we talk about DevOps toolchains, I sometimes wonder how many people really get what we’re talking about. I don’t know if it’s just semantics or tone or something else, but sometimes I think it just sounds like buzzword sausage. So it’s always …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

26 Experts available now in Live!

Get 1:1 Help Now