Read DBF file

[Former Member]

Hi, I need to load the contents of a DBF file, any ideas?

I've tried ws_upload but didn't work.

Thanks

[Former Member]

DBF should be supported by this function.

Anyway you may try to convert .dbf to other supportive types like txt by a dbf viewer.

DBF Viewer 2000 is the powerful, compact and easy-to-use viewer and editor for DBF files (Clipper, dBase, FoxBase, Foxpro, Visual Foxpro, Visual DBase, VO, DB2K...).

You can view, edit, sort, use query by example, delete duplicates, create, print database tables and export the data from them to a variety of formats (DBF, TXT, CSV, Excel, HTML, XML, PRG, SQL)

Other viewers may be searched in google.

[reinis_dzenis]

Hi,

At this point WS_UPLOAD is considered obsolete. So you might wana try GUI_UPLOAD which has basically the same functionality, but is more recent and has more functions.

[VXLozano]

An easy option is to transform your .DBF in a plain text file (using Access or Excel, if you have Office installed). It's an easy thing to do, and the file resultant will be uploaded with WS_UPLOAD without problems (and you can do some make-up to the fields if you need it).

The use of GUI_UPLOAD will be wise if your system supports it, because WS_UPLOAD is obsolete and we can expect will dissappear in future versions of SAP/ABAP.

Wish it helps a bit,

Vic

[athavanraja]

Hi,

try this.

this basically converts the DBF file to DAT file and loads the itab.

parameters: filename type ibipparms-path obligatory.

data: begin of input_table occurs 0 .
include structure <table structure>.
data: end of input_table.

include ole2incl .

start-of-selection.
perform load_dbf_file.

*&---------------------------------------------------------------------*

*& Form load_dbf_file
*&---------------------------------------------------------------------*

* Loading local DBF-file
*----------------------------------------------------------------------*

form load_dbf_file.
data: application type ole2_object,
workbooks type ole2_object,
fworkbook type ole2_object,
fsheets type ole2_object,
fsheet type ole2_object,
dsheet type ole2_object,
frange type ole2_object,
frow type ole2_object.

data: return,
l_filename type string,
t_filename like rlgrap-filename.

* Filename conversion
move filename to t_filename.
translate t_filename to upper case.
replace '.DBF' with '.DAT' into t_filename.
if sy-subrc ne 0.
* error - filename extension is not DBF "TODO
exit.
endif.

* check file existence
call function 'WS_QUERY' exporting filename = filename
query = 'FE'
importing return = return
exceptions inv_query = 1
no_batch = 2
frontend_error = 3
others = 4.
if ( sy-subrc ne 0 ) or ( return eq 0 ).
* error - file does not exist "TODO
exit.
endif.

***** Working through OLE (MS Excel) *****
* OLE initialization
create object dsheet 'Excel.Sheet'.
if sy-subrc ne 0.
* error - OLE initialization of MS Excel "TODO
exit.
endif.
* supress messages from MS Excel
call method of dsheet 'Application' = application.
set property of application 'DisplayAlerts' = 0.
* opening DBF-file
call method of application 'Workbooks' = workbooks.
call method of workbooks 'Open' exporting #1 = filename
#2 = 0
#3 = 1.
* remove header line from DBF data
get property of application 'ActiveWorkbook' = fworkbook.
call method of fworkbook 'Sheets' = fsheets.
call method of fsheets 'Item' = fsheet exporting #1 = 1.
get property of fsheet 'Rows' = frange.
call method of frange 'Item' = frow exporting #1 = 1.
call method of frow 'Delete'.
* save as DAT file
call method of fworkbook 'SaveAs' exporting #1 = t_filename
#2 = 20.
* close the book without saving
call method of fworkbook 'Close' exporting #1 = 0.
* ??????? ?????? ?? OLE-??????
free object dsheet.

***** Filling internal table with data *****
refresh input_table.
l_filename = t_filename.
call function 'GUI_UPLOAD'
exporting filename = l_filename
has_field_separator = 'X'
tables data_tab = input_table
exceptions file_open_error = 1
file_read_error = 2
no_batch = 3
gui_refuse_filetransfer = 4
invalid_type = 5
no_authority = 6
unknown_error = 7
bad_data_format = 8
header_not_allowed = 9
separator_not_allowed = 10
header_too_long = 11
unknown_dp_error = 12
access_denied = 13
dp_out_of_memory = 14
disk_full = 15
dp_timeout = 16
others = 17.
if sy-subrc ne 0.
* error - loading DAT file "TODO
exit.
else.
* the loading was successfull "TODO
endif.
***** Delete temporary DAT-file *****
call function 'GUI_DELETE_FILE'
exporting file_name = t_filename
exceptions others = 1.
if sy-subrc <> 0.
call function 'WS_QUERY' exporting filename = t_filename
query = 'FE'
importing return = return
exceptions inv_query = 1
no_batch = 2
frontend_error = 3
others = 4.
if ( sy-subrc ne 0 ) or ( return eq 0 ).
exit.
else.
* error - deleting temporary file "TODO
endif.
endif.
endform. " load_dbf_file

Hope this helps.

Regards

Raja

[Former Member]

Hi,

Req: Upload dbf file to Internal table.

There is a limitation for no of rows to be uploading in excel i.e 65000.

I have a file which has around 1,30,000 records. Is there anyway to load records more than 65000.

[Former Member]

Use OLE. See my cllass:

INCLUDE ole2incl.

CLASS ZDBFOLE DEFINITION.

PUBLIC SECTION.

METHODS:

constructor,

Open importing path type char100

exceptions Error,

Command importing comm type char200

exceptions Error,

EOF exporting result type c,

MoveFirst,

MoveNext exporting eof type c,

get importing field type char30

exporting val type char100

exceptions Error.

PROTECTED SECTION.

Data:

ado_connection type ole2_object,

ado_recset type ole2_object,

ado_comm type ole2_object,

ado_field TYPE ole2_object.

",

" ConnectionString(300).

ENDCLASS. "ZDBFOLE DEFINITION

----

---

• CLASS ZDBFOLE IMPLEMENTATION

----

---

*

----

---

CLASS ZDBFOLE IMPLEMENTATION.

METHOD constructor.

create object ado_connection 'adodb.connection'.

create object ado_recset 'adodb.recordset'.

create object ado_comm 'adodb.command'.

ENDMETHOD. "constructor

METHOD Open.

data ConnectionString(300).

concatenate

'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='

path

';Persist Security Info=False;Extended Properties=dBase IV'

into ConnectionString.

call method of ado_connection 'Open'

EXPORTING

#1 = ConnectionString.

if sy-subrc ne 0.

raise Error.

endif.

set property of ado_comm 'ActiveConnection' = ado_connection.

ENDMETHOD. "Open

METHOD Command.

set PROPERTY OF ado_comm 'CommandText' = comm. "'SELECT * From kladr'.

call method of ado_comm 'Execute' = ado_recset.

if sy-subrc ne 0.

raise Error.

endif.

"CALL METHOD OF ado_recset 'MoveFirst'.

"if sy-subrc ne 0.

" raise Error.

"endif.

ENDMETHOD. "Command

METHOD MoveFirst.

CALL METHOD OF ado_recset 'MoveFirst'.

ENDMETHOD.

METHOD MoveNext.

CALL METHOD OF ado_recset 'MoveNext'.

GET PROPERTY OF ado_recset 'EOF' = Eof.

ENDMETHOD.

METHOD Get.

CALL METHOD OF ado_recset 'fields' = ado_field

EXPORTING

#1 = field.

if sy-subrc ne 0.

raise Error.

endif.

GET PROPERTY OF ado_field 'Value' = val.

FREE ado_field.

ENDMETHOD.

METHOD Eof.

GET PROPERTY OF ado_recset 'EOF' = result.

ENDMETHOD.

ENDCLASS. "ZDBFOLE IMPLEMENTATION

///===========

//Using this class

create object mdbf.

call method mdbf->open

EXPORTING

path = path. "full path to folder of dbf file

// sql command

concatenate

'SELECT KLADR.NAME, KLADR.CODE, KLADR.SOCR'

' FROM KLADR'

' WHERE (((Left(KLADR!CODE,2))= "'

region

'"));'

into sql.

call method mdbf->command

EXPORTING

comm = sql

EXCEPTIONS

error = 1.

if sy-subrc ne 0.

message 'SQL Error (select from kladr.dbf)!' type 'I' .

leave program.

endif.

call method mdbf->eof

IMPORTING

result = eof.

while ( eof = '0' ).

call method mdbf->get

EXPORTING

field = 'NAME'

IMPORTING

val = name.

call method mdbf->get

EXPORTING

field = 'CODE'

IMPORTING

val = code.

call method mdbf->get

EXPORTING

field = 'SOCR'

IMPORTING

val = socr.

call method mdbf->movenext

IMPORTING

eof = eof.

endwhile.

[Former Member]

Thanks Mikhail Lushnikov !

with your's solution, I can read file DBF with Extended Properties=dBase IV but Extended Properties = "Visual FoxPro 3.0" or "FoxPro 2.x" or "FoxBASE+" I can't read.

Can you direct for me ?

I will appreciate that.

Thanks !

[Former Member]

Hi Mikhail Lushnikov !

i viewed ur solution to read file DBF, have U got any solution to write a file DBF?

Node : I can overwrite to file DBF using function module 'GUI_DOWLOAD' but i don't wanna.

I want to open a DBF file ( exist ), implement append, modify and delete records. So if you have any ideas of this request, plz tell me. tks a lot !

[Former Member]

Hi Mikhail !

In CLASS ZDBFOLE, U using "GET PROPERTY OF ado_field 'Value' = val" for get value of field.

I try using SET PROPERTY for set value field but it can't implement.

If U can use SET PROPERTY, Can you direct for me ?

tks a lot !

[Former Member]

It was solved using this:

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

FILENAME = p_p_file

I_BEGIN_COL = '1'

I_BEGIN_ROW = '1'

I_END_COL = '44'

I_END_ROW = '65000'

TABLES

INTERN = T_RESUL

EXCEPTIONS

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

OTHERS = 3.

[Former Member]

I using Visual FoxPro 6.0 for open a file DBF and then, I export it to new file DBF with option file types example "Visual FoxPro 3.0", "dBASE IV", "FoxPro 2.x", "FoxBASE+".

I using ODBC for read file and this solution can read file DBF with file type "dBASE IV", "FoxPro 2.x", "FoxBASE+".

With file type "Visual FoxPro 3.0", I can't read it.

this file type different.

If U have any solution for read this file type, tell me plz.

Thanks ! (^_)