Convert Excel spreadsheet contents into dBase4 file

Question

Post reply

Convert Excel spreadsheet contents into dBase4 file

Charlottecb

Hall of Fame

Points: 3932
More actions
November 14, 2006 at 3:12 am

#170382

Hi All,
SQL Server 2000
I would like to be able to convert an excel spreadsheet into a dBase 4 file. Ideally I would be able to do this within a stored procedure so my PowerBuilder app can call it. I would like to pass in as parameters a filename of the excel file to be converted and the filename for the new dbase4 file to be created.
Any suggestions / code?
Many thanks
CCB

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Why SQL2000???

Why not export directly from Excel to DB4?

Did you ever look at the list of formats in Save As menu in Excel???

_____________
Code for TallyGenerator

Charlottecb Hall of Fame Points: 3932 More actions · Answer 2

The reason is that the user may not have Excel on there PC, and I need a way which looks seamless to the user to take Excel format data generated from the app and convert it into Dbase4 (even dBase3 ) might do.

My PowerBuilder app extracts data from the SQL Server db according to the user requirements then saves it as an Excel File using a native PowerBuilder function. I then need to convert that file into DBf format so that it can be used by a digital mapping app.

CCB

Ian Yates SSCoach Points: 19738 More actions · Answer 3

The long, ugly and wrong way would be to use the SP_OAxxx procedures in a SQL stored proc to start MS Excel on your SERVER, somehow get that Excel to open an .xls spreadshhet saved on your client (assuming your SQL account can access a common network location with your client) and then drive the Excel instance to save as .dbf.

The better way is to not make life difficult

Just because PowerBuilder has a native save to Excel funtion doesn't mean you should use it for this purpose. I would suggest that you have PowerBuilder use a common database library (eg ADO) to open a blank .dbf and populate it with data. You could ship a blank .dbf with your app and copy it each time before use.

Alternatively, rather than your app performing the hard work by reading data from SQL and passing it, somehow, to a .dbf, you could instead use DTS to read the data and output it to a .dbf.

vadba SSChampion Points: 11133 More actions · Answer 4

How are you creating the Excel file? If you've got the data in a datawindow, just use the SaveAs method and save it as dBase III.

string ls_filename

ls_filename = ...

dw_1.SaveAs (ls_filename, DBASE3!)

If you've got a blank .dbf, you could create a DTS package to do the conversion, then run DTSRUN.exe from your PB app. Or, you could also create a stored procedure that runs the DTS package, and just call the SP from your app.

Charlottecb Hall of Fame Points: 3932 More actions · Answer 5

Hi,

Thanks for the response. I was doing that. However, there is a limit with the PowerBuilder function (cerrtainly with PB9) in that it can not put out more than 128 columns or a total header width (summing up column lengths) of 4096 bytes. Both of these limits may be exceeded by my export (up to 180 columns and 6000 bytes max), so saving in Excel format gets around these issues.

Any other suggestions greatfully received.

CCB

Ian Yates SSCoach Points: 19738 More actions · Answer 6

Can PowerBuilder access ActiveX/COM objects? (I think it can) If so, try using ADO for your export.

vadba SSChampion Points: 11133 More actions · Answer 7

Just to clarify - you need the exported data to end up as a dBase IV file for use with the digital mapping application? Can I assume that the mapping app requires dBASE IV, and cannot use any other formats?

First of all, if you had Excel installed on the SQL Server, you could create a stored procedure that executes Execl via the sp_OA stored procedures mentioned by a previous poster. You could open the Excel file and save as dBase IV.

Another option is to create the dBase IV file yourself, using the PB FileWrite function. The dBase file format is relatively simple, and can be found online quite easily. It consists of a 32 byte header, followed by one 32 byte record for each field. This is followed by the data. The file is terminated with Asc 26 (Ctrl-Z) as the end-of-file marker. If you need to handle a variable number of columns, you would need to be able to loop through your datawindow column-by-column, and map the PB data type to a dBase IV data type. You may have to alter the column names (I don't recall if the 10-character limit applied to dBase IV field names).

Of course, now that I think about it, maybe that's too much work. The advantage is that you don't need to depend on any other apps or tools.

Look here for a complete description:

http://www.clicketyclick.dk/databases/xbase/format/

Charlottecb Hall of Fame Points: 3932 More actions · Answer 8

Hi,

Even though this is more complex, I think your last scenario is probably the way to go. I do something similar on the corresponding import process where I process the header part of the file and extract the data.

I was hoping to avaiod it but it will be the easiest certainly for end user configuration (ie - no configuration or dependant apps).

Thanks

CCB