Dbf to Xls

[Rick Lipkin]

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

[Enrico Maria Giordano]

Right alignment of string containing digits is automatic in Excel:

Code: Select all  Expand view

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

EMG

[Enrico Maria Giordano]

Otherwise, if you want left alignment:

Code: Select all  Expand view

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

EMG

[Rick Lipkin]

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]

Enrico

Is there a method like :

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

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

Rick Lipkin

[Enrico Maria Giordano]

Code: Select all  Expand view

#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]

PERFECT !!!

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

THANK YOU !!

Rick Lipkin

[Enrico Maria Giordano]

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

EMG

[Rick Lipkin]

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]

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

[Enrico Maria Giordano]

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.

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]

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  Expand view

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]

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]

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]

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