Import From Excel crash

[T17Rax]

While teaching myself more of VFP 9, I wondered how to attain data into a DBF from an XLS.
Using the APPEND FROM ... .csv delimited worked but not quite what I was after.

- without going into too much programming and coding, I was hoping a simple import query would do the job.
Tried it, and VFP had crashed.

I tried using the wizard, (as if to debug at breakpoints so-to-speak) to see where it appears to go wrong and crash.
I think it may be something to do with the handling of data? While the wizard completed the task, it had gave me a result of:

The text is exactly what appears in the table per column.

I noticed it appeared to be a common problem across this forum site but I hadn't found a result.
Is there anyone that knows anything about this problem?

A conclusion is simply a place where you got tired of thinking.

 

[Olaf Doschke]

XLS is the binary (as you see from your screenshot) Excel format, it's not csv, you can't import that via Text File, you need to specify TYPE XLS.

Bye, Olaf.

 

[Olaf Doschke]

In regard of the import wizard: In step 1 you kept file format at "Text file", that's not working for XLS.

Bye, Olaf.

 

[Mike Lewis]

You should be able to use the Import Wizard for this. In Step 1 (Identify Data), make sure you select Microsoft Excel 5.0 and 97 from the File Type dropdown. Then follow the prompts. You should see your actual data in Step 2.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

While teaching myself more of VFP 9...

If you want to teach yourself VFP9, DO NOT use the Wizards.
They do not help you learn how to develop in VFP.

Instead use the VFP language to accomplish what you want to do.

There are a number of ways to import Excel data into a VFP Data table.
If you want to do the whole task within VFP, my personal recommendation would be to learn how to Automate Excel through VFP commands.
* You would, through Automation, open the data into an Excel Object
* Within the Excel Object select the Rows & Columns you want
* If needed, format those Row & Column cells as desired
* Do a Save As... of the data you wanted into a CSV format.
* Close the Excel Object
* Then import the CSV data into your VFP Data table with the APPEND command.

That general process will work with older versions of Excel as well as the newer ones.

If you were interested in going this route, you might want to consider getting a copy of:

Microsoft Office Automation with Visual FoxPro​

http://www.hentzenwerke.com/catalog/autofox.htm

Good Luck,
JRB-Bldr

 

[Mike Lewis]

If you want to teach yourself VFP9, DO NOT use the Wizards.

Good advice.

If you want to do the whole task within VFP, my personal recommendation would be to learn how to Automate Excel through VFP commands.

Often, that would be the best way to handle it. In fact, often it will be the only way.

But, in many cases, you can do the job perfectly well with the IMPORT command or APPEND FROM. We don't know whether that would be the best solution in this case, but you shouldn't dismiss the possibility of using those commands. It will be a much simpler approach than learning the intricacies of Excel Automation.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[T17Rax]

Sorry I forgot to say that I had entered the source as "Excel 5.0 and 97", not text file and it had came back as binary as Olaf has said.

Just to be sure, I had done it again yet this time, it crashed with this:

Jrbbldr - I wouldn't mind giving the code a go, but learning how code works, I must be able to write a simple IMPORT FROM query.
I gratefully appreciate your info and will look into the link.

I wasn't sure if it was a bug in VFP itself or whether I was going wrong somewhere. I've tried to write the command with IMPORT FROM ... TYPE XLS numerous times but it just seems to crash - hence the wizard route.

You'll notice that I'm using VFP 8 - it still does the same on VFP 9 however version 9 is broken at the moment so other than a working copy of VFP 9 being at home, this is the most access I have during the day.

A conclusion is simply a place where you got tired of thinking.

 

[JRB-Bldr]

but learning how code works, I must be able to write a simple IMPORT FROM query.

I can't speak for the others, but I can't even remember when I last used the IMPORT FROM command.

I wouldn't let your learning process get hung up with getting the IMPORT FROM command working.
There are generally 1/2 dozen or more ways to get any particular task accomplished in VFP - we each have gotten comfortable with our own approaches while leaving the 'door open' to learning new and possibly better ways.

On a more general note I'd recommend that you define a task and then try either that which you understand and/or that which you are advised to try.
Then when you get that 'under your belt' you know one way to accomplish a task.
After that you can try a variety of the other approaches.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

I had entered the source as "Excel 5.0 and 97", not text file and it had came back as binary

The screen shot you showed (in your first post) indicates that you specified the source as a text file, not an Excel file. That's why you saw binary data in the wizard.

But the fact that you got a C000005 error suggests that the problem is more likely to be caused by some sort of corruption. I doubt that it's a bug in the Import Wizard. Nor is it likely that there is a bug in the IMPORT command.

I suggest you focus on the getting the IMPORT command to work. It's not that complicated. The Import Wizard is really just a wrapper for the IMPORT command. I suggest you also try the command with different XLS files, including a very simple one with, say, just a couple of rows and columns.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vgulielmus]

I agree with Mike Lewis and I have two hypothesis.
1) The Excel file is an xlsx Excel 2007+ file
2) The Excel file is a binary xls file generated by exporting from Excel 2007+ and choosing Excel 97 from Save as type.
These kind of files are known to crash the VFP Import command.
If this is the case, you can :
a) Choose to export (save as) to Excel 95.
b) Also you can try the Importing functions from my blog (the links is in my signature)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

 

[Mike Lewis]

If this is the case, you can :
a) Choose to export (save as) to Excel 95.

I agree. And if you have that control over how the file is saved, consider saving it as "CSV (comma-delimited)". That format is usually a better bet with IMPORT and APPEND FROM.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

Choose to export (save as) to Excel 95.

That is certainly an option if you are wanting to do some of the preliminary work OUTSIDE of VFP.
You can most certainly go into Excel, open the intended file and manually do a SaveAs... into a format that would be easier to bring into VFP.
Then go into VFP and IMPORT or APPEND the data.

My suggested approach would be done wholly within VFP - having VFP perform the 'run' of Excel via Automation.

Alternatively Vilhelm does have an interesting approach with his:

ImportFromXlsx 2.0​

http://praisachion.blogspot.com/2016/01/importfromxlsx-13.html

Your choice.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

I second Mike, your Step 2 Screenshot would have delimited file and fixed length options disabled, if you'd have chosen XLS format in Step 1.

From the follow up suggestion I'd say you best go with Vilhelms Import tool. Simply but not only because the author is here.

Bye, Olaf.

 

[T17Rax]

I'll work harder on the import command.
I've used the append from ... .csv and that works okay but I wanted to be able to see if there was any difference between import and append - import places the field headers on the field row where as append places the field headers inside the table cells - so I've heard. Only ever seen what append does though so I can't really say much.



A conclusion is simply a place where you got tired of thinking.

 

[Olaf Doschke]

Both IMPORT and APPEND look similar IMPORT FROM file.xls TYPE XLS or APPEND FROM file.xls TYPE XLS, the big difference is, IMPORT creates a new table (or it can, when sepcified with further clauses), APPEND does append data into an existing table, the current workarea is the destination of APPEND and if no bf or cursor is open there, APPEND errors.

Bye, Olaf.

 

[AndrewMozley]

My suggested approach would be done wholly within VFP - having VFP perform the 'run' of Excel via Automation.

I agree with this suggestion. Found Tamar's book, Microsoft Office Automation with Visual FoxPro, particularly helpful.

If you want to see a very simple example of the use of automation, the following example should populate and display an Excel sheet.

*  Very simple example of Excel automation from VFP
#DEFINE xlCenter -4108

*  Create Excel as an object with a single workbook with one sheet on it.
oExcel = CREATEOBJECT("Excel.Application")
oWorkbook = oExcel.Workbooks.Add()
oSheet = oWorkbook.WorkSheets(1)

oRange = oSheet.Range("A1")
oRange.value = "Hello World"
oRange.font.size = 14

*  Put some arithmetic statements on the sheet
FOR I = 1 TO 10
   oRange = oSheet.Range("B" + LTRIM(STR(I+2)))
   oRange.Value = I
   oRange = oSheet.Range("C" + LTRIM(STR(I+2)))   
   oRange.Value = "*"
   oRange = oSheet.Range("D" + LTRIM(STR(I+2)))
   oRange.value = ROUND(RAND() * 100,0)
   oRange = oSheet.Range("E" + LTRIM(STR(I+2)))
   cSuffix = LTRIM(STR(I+2))
   oRange.Value = "=B" + cSuffix + "*D" + cSuffix
   NEXT I

*  Format the columns
oSheet.Columns("C:C").ColumnWidth = 2
oSheet.Columns("C:E").HorizontalAlignment = xlCenter

*  Give a block of cells a yellow background
oRange = oSheet.Range("E3:E12")
oRange.interior.color = 65535 

*  Make the Excel window visible.
oExcel.visible = .T.

This example is only for exporting data to Excel; You would use appropriate instructions on an Excel object to open an existing workbook and then to read the contents of its cells.

Andrew