Dbf to Xls

Rick Lipkin · Post by **Rick Lipkin** » Mon Mar 26, 2007 4:11 pm

Enrico

In my haste to get my problem posted .. I made an error .. the field lengths for the column in question ( reg_no) is C 7 .. any time I run into an alpha-numeric .. here is what happends:

_2306753
200A118_

The _ ( underscore ) represents leeding and trailing spaces .. I do not want the 2nd number behavoir.

I do not understand your "'" syntax ??

Rick Lipkin

Mon Mar 26, 2007 4:20 pm

Right alignment of string containing digits is automatic in Excel:

Code: Select all

oSheet:Cells( nRow, 1 ):Value := "12345"

EMG

Mon Mar 26, 2007 4:22 pm

Otherwise, if you want left alignment:

Code: Select all

oSheet:Cells( nRow, 1 ):Value := "'12345"

EMG

Rick Lipkin · Post by **Rick Lipkin** » Mon Mar 26, 2007 4:42 pm

Enrico

The problem is when I have a mix of alpha and numeric ?? .. The right justification is what I want for all values .. what happends is when there is a mix .. that value gets left justified and adds a trailing blank .. throwing the entire column out of wack ..

Rick

Rick Lipkin · Post by **Rick Lipkin** » Mon Mar 26, 2007 5:00 pm

Enrico

Is there a method like :

oSheet:Columns( "A:L" ):Justify() ..

I tried this but it produced a run-time error ..

Rick Lipkin

Mon Mar 26, 2007 5:03 pm

Code: Select all

#define xlRight -4152


FUNCTION MAIN()

    LOCAL oExcel, oSheet

    oExcel = CREATEOBJECT( "Excel.Application" )

    oExcel:WorkBooks:Add()

    oSheet = oExcel:ActiveSheet

    oSheet:Cells( 1, 1 ):Value = "This is a test"
    oSheet:Cells( 2, 1 ):Value = "This is a long test"
    oSheet:Cells( 3, 1 ):Value = "12345"

    oSheet:Columns( "A:B" ):AutoFit()

    oSheet:Columns( "A:B" ):HorizontalAlignment = xlRight

    oExcel:Visible = .T.

    RETURN NIL

EMG

Rick Lipkin · Post by **Rick Lipkin** » Mon Mar 26, 2007 5:50 pm

PERFECT !!!

Never in a million years would I have figured that one out ..

THANK YOU !!

Rick Lipkin

Mon Mar 26, 2007 5:53 pm

But keep in mind that this is only an alignment and data extracted from the sheet remains the same.

EMG

Rick Lipkin · Post by **Rick Lipkin** » Mon Mar 26, 2007 5:57 pm

Enrico

Our SQL loader apparently looks for 'right justification' .. and when it saw some of the alpha-numeric values shifted to the left with a trailing space it failed ..

I have asked our DBA to re-try the latest conversion .. the file 'looks' GREAT'

I will know later this afternoon..

Thanks
Rick Lipkin

Rick Lipkin · Post by **Rick Lipkin** » Mon Mar 26, 2007 8:00 pm

Enrico

Bad news

.... the loader see's the ( ole ) reg_no column as a double type .. when I ( manually ) take the .dbf table into Excel and then save out as .xls .. the Excel file loads perfectically.

For some odd reason .. Excel formats the reg_no column as numeric even though it containes both charactor and numeric ?? The sql loader is configured a varchar.

Back to square one ..At a loss here.

Rick Lipkin

Mon Mar 26, 2007 8:39 pm

Rick Lipkin wrote:Enrico

Bad news .... the loader see's the ( ole ) reg_no column as a double type ..

Sorry, I don't know what reg_no column is.

Rick Lipkin wrote:For some odd reason .. Excel formats the reg_no column as numeric even though it containes both charactor and numeric ?? The sql loader is configured a varchar.

Then, as I previously said, try to prefix it with a single quote "'".

EMG

Gale FORd · Post by **Gale FORd** » Tue Mar 27, 2007 2:07 pm

You can set the cell format to text before adding the data. Then it will not have mixed numberic and text

I believe you can add the following before you add data.

Code: Select all

oSheet:Range( "A:A" ):Set( "NumberFormat", '@' )
// then the follwing would be text and not numeric
oSheet:Cells( 1, 1 ):Value = "12345"

By the way, here is a good way to find out how to do something in Excel.
Start macro record, perform the action, stop macro, then look at the code that is created.

James Bott · Post by **James Bott** » Tue Mar 27, 2007 3:04 pm

Rick,

Are you just trying to get DBF data into SQL? If so, there are other ways to do it. I think Enrico published a way to directly put DBF records into an SQL file. You could seach the forum for this.

James

Rick Lipkin · Post by **Rick Lipkin** » Tue Mar 27, 2007 9:10 pm

James

Ultimately I want to use SQLRDD on this application and move the .dbf's to MS SQL Server..

I have seen the dbf2sql but that utility does not support MS Sql Server

This app needs to have outside Web data verification and will eventually need to be moved to SQL .. politics and complications need to be over-come first .. the quickest and easiest way to acomplish this task was to replicate the data to the Web Server..

Ron mentioned that SQLRDD was going to be sold seperately in the next release of xHarbour and not specifically bundled with the builder.. I am waiting to see that come about and have a go at moving the back end to SQL with ( minimal ) code changes ..

We'll see
Rick

Rick Lipkin · Post by **Rick Lipkin** » Tue Mar 27, 2007 9:13 pm

Gale

The pre-formatting seemd to work with the column justification of the mixed data .. and it formatted the column to 'text" rather than 'general' as before ..

I have the new table in the staging area for the DBA to test .. we'll see in the morning ..

Thanks .. this has been an interesting learning experience.

Rick Lipkin