Converting Excel to DBF

[breezett93]

My Google searches only brought up websites trying to get me to download their software to convert it for me. I would prefer a more secure way through FoxPro.

I also tried exporting through Excel, but I couldn't find a .dbf format.

Thanks.

 

[DSummZZZ]

You can't directly export Excel to a dbf. You have to export or save it as CSV, then create a dbf and import it.


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[JRB-Bldr]

It depends on what level (version) of Excel you are working with.

If you are working with Excel 2000 or 2003 AND the Excel file is a .XLS file, you have the ability to use Excel itself (no VFP involved) and do a SaveAs... into the DBF4 (dBase IV)(*.dbf) format

Or, again if you are using one of those versions, you can use VFP to Automate Excel to:
1. Open the Excel workbook
2. Select what you want
3. Do a SaveAs... into the DBF4 (dBase IV)(*.dbf) format
NOTE - you will have a 16,383 row/record limitation with these methods

For the above, you might want to look over the FAQ's

* How to bypass the limitation of 16,383 of Excel faq184-2617​

* Excel - How to do Automation from VFP faq184-4428​

If, you are using Excel 2007 or 2010 and/or an .XLSX file, then you will need to either:
1. Use Excel itself (no VFP at this stage) to save off the file as a CSV file
2. Or use VFP and Automate Excel to save off the file as a CSV file
And then follow Dave Summers advice above.

In addition to the Excel version you are working with, how you approach this can also largely depend on whether this is just a one-time project or something that needs to be done on an on-going basis.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

In my version of Excel, I can simply choose File / Save As, then [tt]DBF3 (dBASE III)(*.DBF)[/tt] as the file format. After a couple of warning messages, the file is saved as a DBF which can be opened in VFP. The contents of the first row become the field names, and the other rows contain the data as expected.

Alternatively, you can create the DBF as usual in VFP, then use [tt]APPEND FROM ... TYPE XL8[/tt] to import the Excel file.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[breezett93]

Thanks for all the replies.

Using APPEND FROM TYPE XL8 gave an error saying file format is invalid.
Using IMPORT FROM also did not work.

I am using Fox Pro 6 and Excel 2013.

What's the best way to get the csv converted with the highest accuracy?

 

[Mike Lewis]

You say the XL8 file format is invalid. But then you say you are trying to convert a CSV. Which is it? If you use TYPE XL8, you need to soecify a workbook saved in native Excel format (in Excel 97 or later). To convert a CSV, you need to save the worksheet as a CSV from within Excel.

And what exactly is the error message? As far as I know, "File format is invalid" is not a standard VFP error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[breezett93]

Error Message

I was able to get the APPEND command to work but it only imported 1 column. From what I read about APPEND, this isn't normal.

 

[Scott24x7]

Hi Breezett,
I have had many frustrating moments with this as well. Interestingly, (maybe others have different experience here) but I was recently tussling with the same issue, and I tried saving my Office 2013 Excel file as a "Excel 97-2003" workbook, which is what XL8 is supposed to work with, and it didn't. So you may be having a similar issue.
There are some alternatives. I really don't recommend CSV, as it is incredibly problematic, as any , (comma) character anywhere in any data will throw the whole import off. One thing you can do is save from Excel using TAB DELIMITED, and then import that file using the TAB as a delimiter. This is better particularly in Excel data, as the probability of a TAB character is VERY low, since typically TAB takes you to the next field in Excel (when interacting with it). So I would HIGHLY recommend if you have to out put the Excel file first, choose this format instead of CSV.

That said, there is an EXCEPTIONAL thread on this where Vilhelm-Ion Praisach has provided an excellent tool for importing XLSX (and XLS) file into VFP. The thread is: thread184-1755524
And all the credit goes to Vilhelm. His solution is incredible.



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

>I really don't recommend CSV, as it is incredibly problematic, as any , (comma) character anywhere in any data will throw the whole import off.
No, because text values wil be within string delimiters. The problem VFP has with CSV import is linebreaks within texts and no way to append into memo fields.

The "Excel 97-2003" workbooks are compatible with Excel 97-2003, but not with VFP APPEND, but I have the faint memory of an Office fix for that, eg Save as excel5 via automation with the xlExecl5 = 39 file type. But it would only be half a solution anyway.

You may use the Microsoft.ACE.OLEDB.12.0 oledb provider to read execl as a database (eg see

http://www.foxite.com/archives/import-through-xlsx-0000251072.htm)

Or give this a try:

http://praisachion.blogspot.de/2015/06/append-from-xlsx.html,

which is extracting the xml files embedded in a xlsx and parses them.

Bye, Olaf.

 

[Mike Lewis]

One other possibility:

Is it possible that this is an XLSX file (as opposed to an XLS)? If so, then APPEND FROM and IMPORT definitely won't work. XLSX is a newer format that VFP does not support. If that's the case, the solution is to save it as XLS from within Excel.

And if you do that, you might as well save it straight to a DBF from within Excel, and avoid the need for the import entirely?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

The disadvantage to saving to DBF from Excel and then managing that is, unless you write some serious Office Automation, you have to manually address ever Excel file then. That may be ok for something casual, but if you have a repeated process, it becomes very labor intensive, and prone to human error.

I had extensive problems with trying to save to old format XLS and import with XL5 or XL8 from current (Office 2013, Office 2016) files.

Olaf, the second you list is Vilhelm's solution that I referenced before with Thread184-175524 and my be a better source, as there were some problems with the code in some of the other locations that is stored in. Your other reference though looks rather interesting. It might be slightly cleaner method from a code perspective, but also might be a little messier with the ODBC involved. I guess that's more a preference issue.

Either way, unless you're dealing with an old version of Excel, bringing excel file data into VFP is tricky at best. If you need this often, I highly suggest finding a way to do it without the conversion taking place within Excel.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[dbMark]

Automation from within VFP has been mentioned here and that is probably the best option, though it requires more coding, control over the spreadsheet file formats, handling different Excel file versions, etc. For example, a user may password protect a submitted file. Or protect a range. Or read/write workbooks, worksheets and cells, etc. Any number of unexpected scenarios. Not simple or easy but it is the way to handle the widest range of scenarios. Which is why so many use packages developed for that purpose.

* For example, to create XLSX file and unProtect just a range of cells
LOCAL oExcel as Excel.Application
oExcel = CREATEOBJECT("Excel.Application")
oWkBk = oExcel.Workbooks
oWkBk.Add()
oSheet = WkBk.item(1).Sheets(1)
oSheet.Protection.AllowEditRanges.Add("dkfksjdfh",oSheet.range("a1:a4"),"")
oSheet.protect("123456",.t.,.t.,.t.)
oSheet.saveas("C:\Temp\TestFile.xlsx")
oExcel.DisplayAlerts=.F. && so quit won't ask questions.
oExcel.Quit
RELEASE oSheet, oWkBk, oExcel
* Tested - All cells are protected except a1:a4

Other links:
thread184-1684452

https://social.msdn.microsoft.com/F...y-from-vfp-to-excel?forum=visualfoxprogeneral

Does Craig Boyd's SweetPotatoSoftware exist anymore? I was looking for his solutions on this topic.

 

[breezett93]

Olaf, I'll throw out the CSV then. Now I have it in XLS format, not XLSX.

Mike, I would love to save as DBF in Excel; however, I do not see that option in the Save As Type dropdown.

Scott, I looked at your links, but I wasn't sure which page had the info that you were talking about.

 

[Scott24x7]

Brezzett,
Yeah, you are having exactly the same issue I had a couple weeks back. The delimiter routs are always very tricky, because all it takes is 1 errant character to much up everything, or as Olaf mentioned, the need to populate a memo field. I had both issues.
It took a little effort, but we worked out a small problem with the files that Vilhelm provided (Again, also in 2nd link that Olaf mentioned). In the threat I pointed out before, if you look at the second to the last entry has at the foot of Vilhelm's post (look for a paperclip icon) link to download the .prg (also the 5th from last, he has two methods for doing this). Click there, download the code, put it in your project under CODE tab, then make a call to the .PRG as a function.
Essentially what he does is, he opens the XLSX file low-level, then extracts column data as XML, then loads that back into either a table that it creates based on the structure (XLSXTODBF.PRG) or appends to a table structure you have that matches the columns (including Memo fields) (APPENDFROMXLSX.PRG)
It's a very elegant solution and will save you all this heartache.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

Essentially what he does is, he opens the XLSX file low-level, then extracts column data as XML, ....

Scott, I agree that that is a good technique for XLSXs. But Breezett says that they are not XLSXs. At least, I think that's what he says. To be honest, I'm finding it a bit confusing now.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[breezett93]

Okay, I was able to save the file as an Excel 5.0 file. The import was successful. However, the first row is actually the columns from Excel. Is there a way to fix this?

 

[Scott24x7]

If you're importing first to a "staging" table you can just issue:
GO TOP
DELETE

If you know it's always the first row.
And then append it to your existing table.
OR
You can get the RECCOUNT() of the table before the append then:

lnReccount = RECCOUNT()

GO lnReccount + 1
DELETE



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

breezett93, I talked about Excel5, you don't get this from the choices, but it's still there in code, when you automate excel and use the Sheet objects SaveAs() method you can specify file format 39, meaning Excel 5, see

https://msdn.microsoft.com/en-us/library/bb241279(v=office.12).aspx?f=255&MSPPError=-2147217396

Exccel 5 is not in the choices presented by the UI, but the Save or SaveAs VBA commands still support that as FileFormat parameter.

VFP could also cope with Excel 8, but what you save via Excel97-2003 is not XL8. Like any older format it has the XLS file extension but isn't real Excel 8 binary format, nor older of course. As said: Excel versions 97-2003 can cope with that format, but not VFP APPEND.

Bye, Olaf.

 

[JRB-Bldr]

Now I have it in XLS format, not XLSX.

That phrase suggests that this might be a one-time effort and not needed on an on-going basis.
It suggests that your having manually saved the file into the XLS format will work.

However for an on-going process I doubt that you will want to have to manually save the Excel file off into the older format each and every time.

If you need this on an on-going basis you should look into using VFP to Automate Excel and get done what you need to get done.

Good Luck,
JRB-Bldr

 

[breezett93]

Thank you all for the replies. Import and formatting were both successful.