Append from 1

[Jerim65]

I am introducing an 'Import' function into my app to satisfy a user request.

The user wants to supply an xls spreadsheet - I have only ever imported a text file before so I am experimenting.

I have created a temp.dbf with the 4 required fields - one of which is ideally a memo field.

I have tried

APPEND FROM (importfile ) XLS - with or without TYPE

I get no records.

So to get _something_ I have converted the xls to csv and 'appended type csv' into another temp table with a c(240) field rather than a memo.

I have then scanned each record of the first temp table and Replaced the chr string into the second table's memo field.

All works fine - I see what I need to see in the new table.

But

1 The user wants to be able to 'import' xls files
2 The 3rd party app the table is used in requires a Memo field rather than a chr field .

I want to do the processing in the minimum code of course.

Has anyone any comments about how to Append from xls?

Regards

Coldan

 

[MrDataGuy]

This works for me.

CREATE CURSOR qTemp ( Field1 c(15), Field2 C(20), Field3 C(15), Field4 C(250), FieldM  M)

APPEND From c:\Temp\Data2.xls TYPE xls
REPLACE ALL FieldM WITH Field4

BROWSE normal

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[MrDataGuy]

I want to do the processing in the minimum code of course."

May I suggest the goal of the clearest code possible? I have send minimum code and sometimes it can be a bear to understand, maintain etc. I even do not care to have fast code to start with. Give me understandable and code that returns the right answer over minimal and fast code any day. e.g. Get the correct answer and then only if needed make it fast (or minimal).

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Jerim65]

I said "I want to do the processing in the minimum code of course." - see below.

I agree with "May I suggest the goal of the clearest code possible?

I was really looking for possible reasons why my import is not working.

As often happens to me, others do tests and say they work but my examples do not.

My code is 'jumping through hoops' to get what my user wants and I am sure it could be much more efficient or simpler or...

I shall keep trying!

Thanks

Coldan

This is frustrating as I'm no longer young and trying very hard to create useful applications.

 

[Jerim65]

Further to my last message - can an import type xls import directly into a memo field?

Thanks
Coldan

 

[alvechurchdata]

It's very odd that you're getting no records at all coming in. You quote [TT]APPEND FROM (importfile ) XLS - with or without TYPE[/TT] as the command, are you putting brackets around the name of the file because that will make Fox do a name substition and it will look for the file name in a variable named "importfile".

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Olaf Doschke]

First of all vfp does not support any XLS, and second: No, memo fields are neither exported not imported. You have to do more than an APPEND or IMPORT in your situation.

The best way would be to rather use the oledb provider for Excel Sheets to treat the xls as a table, then you might also be able to fill the memo directly. Nevertheless you would perhaps end up with more code than Anthony gave you with the temporary import into a txt field. You'll only need a more advanced solution, if values go beyond 254 chars.

It's not unusual to do an import in to steps, especially with CSV, as that really has no option of defining types. Excel is a bit better, but Excel is no database.

Bye, Olaf.

 

[Jerim65]

Thanks everybody for your wisdom.

The code I used is

importfile  = Getfile('xls'))

Create a temp table with the c (240) field.
APPEND FROM (importfile ) type XLS
create a temp table with memo in place a c(240)
Scan
Replace fields
Endscan

further processing

I am happy that it is clear what the code is doing and my user is happy to get the job he wanted done.

Regards

Bryan

 

[Olaf Doschke]

As you obviously don't accept both C() and Memo field in the same table and want the final destination table to only have the memo:

Instead of Scan with single Inserts/Replaces you can

Insert Into temp2 Select * From temp1

as a faster and more elegant solution.

For example this test worked:

Create Cursor temp1 (iid i, aText C(10))
Insert into temp1 values (1,"hello")
Insert into temp1 values (2,"world")

Create Cursor temp2 (iid i, aText M)
Insert into temp2 Select * from temp1

Bye, Olaf.

 

[Jerim65]

Thanks Olaf,

Worked perfectly in my app..

Coldan