Importing C.S.V Files 1

[tforr]

Hello, please can anyone confirm whether it's easy enough to import a c.s.v. file into a Visual FoxPro database table.

Thanks and kind regards,

Tom.

 

[tforr]

Now that I have a dbf file that mirrors my csv files I need to update a program written in VFP 6.0. The dbf files looks exactly like the csv file however it doesn;t look like the dbf file within the program. Certain fields are required and some are not. How do you reccommend me updating the new table. I has been suggested updating the table column by column. If this idea is what you also suggest, What code shall I use.

Thanks all,

Thomas

 

[Mike Gagnon]

tforrest

If you use the code I suggested above (ie. Save your CSV file to a dbf), you could then easily loop through the dbf abd making the appropriate updates using SCAN...ENDSCAN.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[tforr]

Ok, So will this SCAN...ENDSCAN scan through the dbf files and update the fields that are named the same.

Does this require lots of codeing (SCAN....ENDSCAN)

Cheers,

Thomas

 

[Mike Gagnon]

tforrest

Not a lot of coding. If you are sure that all the fields you need to update have the same name and the fields you don't want to update do not, you could us. The trcik is to determine how you are going to locate the correct record in both tables. Let us assume you have a unique identifier (key field in both tables that are the same). You could use:

USE myTable1.dbf shared again in 0 && The one you want to update
SELECT myTable1
SET ORDER TO 1 && Key field
USE myTable2.dbf shared again in 0 && The one that contains the updates.
SELECT myTable2
SCAN
	SCATTER MEMVAR MEMO
	lcKey = myTable2.keyfield
	SELECT myTable1
	IF SEEK(lcKey)
		GATHER MEMVAR MEMO
	ENDIF
ENDSCAN

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[tforr]

OK this looks good but before I go any further let me recap.

I have exported employee data from a dos based program into CSV format. I have then created a table within vfp 6.0 with the same fields as the spread sheet and appended the data to this table. The aim is to update the table in a vfp program but the fields in the program are called different names. If this idea of updated the table this way cannot be achieved then I will have to input the records manually.

Shall I go back to the table I created in VFP 6.0 and rename the fields that i'm interested in and rename them to the same fields in the program and then use the scan....endscan command you suggested or is it advisable to just input them manually. I have made a backup of all tables involved for testing purposes so it doesn't matter if the data is incorrect.

Thanks for your time Mike,

Regards,

Thomas.

 

[keepingbusy]

Hi Mike

Tagging on the end of this thread... The original suggestion you made about importing an XLS file with the following code which has been changed relating the actual files I use:

#Define xlDBF4 11
Set Step On
oExcel = Createobject("excel.application&quot
oExcel.displayAlerts = .F.
oWorkbook=oExcel.Workbooks.Open("d:\cdx\files\newfile.xls&quot
With oWorkbook
.SaveAs("d:\cdx\files\newfile2.dbf",xlDBF4)
Endwith

The code runs but when you try to open the DBF (newfile2.dbf) I get an error message File Access Denied. I've had a look to make sure its not read only etc and cant find any reason why this is happening

Any suggestions?

Lee
(VisFox 6)


Alone we can do so little, together we can do so much

 

[keepingbusy]

Just one other thing Mike, I've tried to open the DBF in Excel (Office 2000 Version) and the message "newfile2.dbf is locked for editing by another user. Click notify to open a read only copy of the document and receive notification when the document is no longer in use"

Lee



Alone we can do so little, together we can do so much

 

[tforr]

Also when I attempt to run the scan....endscan, I receive a nesting error.

 

[DSummZZZ]

Shall I go back to the table I created in VFP 6.0 and rename the fields...

If you rename the pertinent fields to match the filed names of the destination table, you will be able to use mgagnon's scatter/gather method within a scan/endscan.
If you don't rename them, you can still use a scan/endscan construct but you will have to specifically state the fields you want to update. Something like this:

USE SourceTable IN 0
USE TargetTable IN 0 INDEX fullname
SELECT SourceTable
SCAN 
   SELECT TargetTable
   SEEK SourceTable.lname + SourceTable.fname 
   REPLACE FirstName WITH SourceTable.fname
   REPLACE LastName  WITH SourceTable.lname
   .
   .
   .
ENDSCAN

-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[tforr]

Ok Dave I will give it a go. I do however have another question (as allways lol). When I attempt to modify my tables structure I've noticed that the table designer window is different when compared to other table designer windows I normally use (different layout with less functions). I want to be able to give the fields a caption (the same as the destination table).

Cheers,

Thomas

 

[DSummZZZ]

The 'other' tables are probably part of a database. If that is the case, they have the capability of having captions, comments, and longer field names (>10 characters) which your standalone tables won't have.
If the database tables have field names longer than 10 characters, you will have to use the REPLACE commands.


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[justamistere]

I think that once the Excel file is saved-as you need to "Quit" Excel application Object.

#Define xlDBF4 11
***---| I'm not sure why the trace window was opened
***---| with "SET STEP ON", usually its for debugging.
***---| For Debugging I type in 2-lnies
***---| SET DEBUG ON
***---| SET STEP ON
Set Step On

oExcel = Createobject("excel.application&quot

***---| I always set the Excel Automation object to "Visible"
***---| so I can Close/Quit it manually if there are errors.
***---| It's hard to close an application that's not visible.
oExcel.Visible = .T.

oExcel.displayAlerts = .F.
oWorkbook=oExcel.Workbooks.Open("d:\cdx\files\newfile.xls&quot
With oWorkbook
.SaveAs("d:\cdx\files\newfile2.dbf",xlDBF4)
Endwith

***---| Quit the Excel-Object application.
***---| The first line may be optional
oExcel.Workbooks(1).CLOSE(1,'',0)
oExcel.Quit

 

[keepingbusy]

Hi justamistere

What you suggested worked fine with no lock up etc. The only problem now is that when you try and open up the table (newfile.dbf) an error message shows stating:

"Field name is a duplicate or invalid"

Have you heard of or seen this before?

Lee

Alone we can do so little, together we can do so much

 

[baltman]

You can get that error when a field name starts with a number, or if a name gets cut down to 10 chars and becomes duplicate.

Brian

 

[Mike Gagnon]

justamistere


#Define xlDBF4 11
***---| I'm not sure why the trace window was opened
***---| with "SET STEP ON", usually its for debugging.
***---| For Debugging I type in 2-lnies
***---| SET DEBUG ON
***---| SET STEP ON
Set Step On

For debugging I only use 1 line, I never use SET DEBUG ON. The fact that I left a set step on in the code only proves that I test the suggestions I make before posting them ;-). But the fact that I did not complete the code is a bad habit that I have, in that I assume that the original poster will complete the code on his own using the partial solution I suggest. I don't like posting complete solution as it tends to make for lazy programmers.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[keepingbusy]

Hi Baltman

Regarding your post,is there anyway around this?

Lee

Alone we can do so little, together we can do so much

 

[baltman]

I use the IMPORT ... TYPE XLS command, which generally imports the headers as row 1 and all of the data as text fields.

I then use a FOR X = 1 to afields() loop to grab the header values out of the 1st row and validate them, EVAL() and validate the data for rows 2 through N and then build a CREATE TABLE and append the data into the new structure or ALTER TABLE RENAME COLUMN A to FieldName etc.

Unfortunately there's really a fair amount of code needed to validate for me, but your situation might be more straight forward.

Brian

 

[keepingbusy]

Hi Baltman

Thank you for your posts. We have now been able to resolve this issue by adopting the following method (For anyone Interested)
The original Excel 2000 file is opened, updated and then a row is inserted at the top.
We then save this file as a Comma Seperated File (CSV)
An app is then run in VisFox Version 6 and the piece of code shown is run:

USE NEWFILE5 EXCL
ZAP
APPEND FROM NEWFILE.CSV FIELDS TITLE, PRICE ;
TYPE DELIMITED WITH ","

Mike Mgagnon suggested somewhere to use a predefined table which we have now adopted but can add the additional fields we required by using Ramani's (I think!) suggestion to ALTER TABLE ADD COLUMN etc previously mentioned.
This was an easy way to import the original Excel file, ensure the all the information was included and not have any problems identifying the field names

May I just say a big thanks to all those who contributed to all my questions in this particular thread for which I'm sincerely grateful.

Kindest regards to all
Lee (KB)

Alone we can do so little, together we can do so much