read csv file

[gayandnice]

I need to read a csv file, the user selects the file from getfile, so I do NOT know the structure. Does anyone have sample code of reading a csv file into a cursor?

 

[dgrewe]

See if this works, Needless to say this is untested code so you may have to alter it to get it to work

lcFileName = "NameOfCvsFile"
lcDelimiter = ","
*
* Open the file and read one line of data 
lnHandle = FOPEN(lcFileName,0)
         = FREAD(lnHandle)  && Get rid of the header
lcString = FREAD(lnHandle)  && First Data line
=FCLOSE(lnHandle)
*
* Find out the number of fields
lnLen = LEN(lcString)
lnFields = 1
FOR ii = 1 TO lnLen
	IF SUBSTR(lcString,ii,1) = lcDelimiter
		lnFields = lnFields + 1
	ENDIF 
ENDFOR 
*
* Add one column for testing
lnFields = lnFields + 1
lcField = "CFIELD"+TRANSFORM(lnField)
*
* Build a table
CREATE TABLE CVSIMPORT FREE (CFIELD1 c(254))
USE CVSIMPORT EXCLUSIVE
FOR ii = 2 TO lnFields
	lcRun = "ALTER TABLE CVSIMPORT ADD COLUMN  CFIELD"+TRANSFORM(ii)+" c(254)"
	&lcRun	
ENDFOR
*
* Read in the data
APPEND FROM (lcFilename) TYPE CSV
BROWSE FOR !EMPTY(lcField)
*
* if any records show up in the browse window, 
* the data you imported is not consistent in the number of fields or has commas inside the data

David W. Grewe Dave

 

[dgrewe]

P.S.
If you do not want the fields to all be len(254) , add this code at the end

USE CVSIMPORT EXCLUSIVE 
lnFields = FCOUNT()
DIMENSION laFields[lnFields,2]
FOR ii = 1 TO lnFields 
	laFields[ii,1] = ' '
	laFields[ii,2] =  0
ENDFOR
*
SCAN ALL 
	FOR ii = 1 TO lnFields
		laFields[ii,1] = FIELD(ii)
		laFields[ii,2] = MAX(laFields[ii,2],LEN(ALLTRIM(FIELD(ii))))
	ENDFOR
ENDSCAN 
*
FOR ii = 1 TO lnFields
	lcRun = "ALTER table CVSIMPORT ALTER COLUMN " + laFileds[ii,1] + " c(" + TRANSFORM(laFields[ii,2])+")"
	&lcRun
ENDFOR

David W. Grewe Dave

 

[Ed Andres]

Hey Dave,
Couldn't you get the number of fields like this?

lnFields = OCCURS([,], lcString) + 1

Save you about 8 lines of code.

Ed

 

[dgrewe]

Yea, Just did not think of that when I wrote the program

David W. Grewe Dave

 

[gayandnice]

There could be commas within a field, so I could not use this solution :-( I was hoping to try and use oledb but not able to get it working. I know I need a connection string but not sure how to go from there. I am new to VFP and had this challenge thrown at me.

 

[Ed Andres]

What are you connecting to?

Ed

 

[gayandnice]

I have a csv file, and want to use the Excel 12.0 driver (ACE) if they have it on the pc or the Excel 8.0 (JET) driver if they have it.

 

[Steve Bowman]

I have had luck with this sort of thing by opening the csv file via Excel Automation then doing a saveAs type dbf. Then I can open the dbf and do my work. I will post more detail on the code to do this if this post is still active!


Steve Bowman
Independent Technology, Inc.
CA, USA