asked on

Import CSV Files

Create Cursor myCursor(;
var1 i,;
var2 i ,;
var3 c(21) )

FOR file_index = 1 TO 24
xx_filename =\\WTE\Reports + "xx_filename" + file_index + ".csv"
SELECT * FROM myCursor WHERE ROW > 4
USE IMPORT EXCLUSIVE
APPEND FROM (m.xx_fileName) TYPE DELIMITED WITH CHARACTER ,
SELECT * FROM m.xx_filename INTO TABLE LDY
ENDFOR
DISPLAY MEMORY LIKE myCursor*

I have a few CSV files located in WTE-LDY\Reports. The CSV files are updated daily, and the files are open in Excel. I want to create a VFP 6 program to import the CSV files, put the data into a cursor or table, and append the data to the Ltable. All CSV Files are named with the building #, date, and time, for example, 55_2014-9-17_9-10. Also, there are three digit buildings, like 100_2014-9-17_9-29. The CSV files have data in Column A, Column B, and Column C. I need to only collect data starting on row 5 in Column B and Column C. The data ends on row 13. Also, I need to collect the building # as well. I can collect the building # by the first two or three digit csv file name or by Column B on Row 2. The Column B and Column C data would be stored into the appropriate field name in the Ltable.
Column A Column B Column C Ltable fieldname1 Ltable fieldname2
Row 5 1 5 Blankets Blktamtin Blktdescript
Row 6 2 5 Flat Sheets Shtpamtin Shtpdescript
Row 7 3 5 Fit Sheets Shtbtamtin Shtbtdescript
Row 8 4 0 Spreads Sprdamtin Sprddescript
Row 9 5 42 Bath Towels Btamtin Btdescript
Row 10 6 0 Bea Shop Towels Bstamtin Bstdescript
Row 11 7 2 Pillow Cases Pcamtin pcdescript
Row 12 8 65 Wash Cloths wcamtin wcdescript
Row 13 9 0 Personal Garmet oth21amtin oth21descript
23-2014-9-19-10-50.csv

Insoftservice inso

Why you are not using load() .
It would solve all your issue.
Please provide sample csv file and db structure

Pavel Celba

Following code imports all CSV files from given folder into cursor myCursor. Some updates will be necessary probably.

LOCAL lcPath, lcFileMask, lnCnt, laFiles[1], lnI

Create Cursor myCursor(;
                building N(3,0), ;
                var1 i,;
                var2 i ,;
                var3 c(21) )

lcPath = "d:\download\"
lcFileMask = "*.CSV"

lnCnt = ADIR(laFiles, m.lcPath + m.lcFileMask)

FOR lnI = 1 TO m.lnCnt
  DO ImportFile WITH m.lcPath, laFiles[m.lnI, 1]
NEXT

SELECT myCursor
BROWSE


PROCEDURE ImportFile
LPARAMETERS lcPath, lcFileName

LOCAL lcLine, lnFH, lnI, lnBuilding

lnFH = FOPEN(m.lcPath + m.lcFileName)
IF m.lnFH > 0
  
  lnBuilding = VAL(m.lcFileName)
  
  FOR lnI = 1 TO 4
    lcLine = FGETS(m.lnFH, 1000)
  NEXT
  
  DO WHILE !FEOF(m.lnFH)
    
    lcLine = FGETS(m.lnFH, 1000)
    lnA = VAL(LEFT(m.lcLine, AT(',', m.lcLine)-1))
    
    lcLine = SUBSTR(m.lcLine, AT(',', m.lcLine)+1)
    lnB = VAL(LEFT(m.lcLine, AT(',', m.lcLine)-1))
    
    lcLine = SUBSTR(m.lcLine, AT(',', m.lcLine)+1)
    lcC = ALLTRIM(SUBSTR(m.lcLine, AT('"', m.lcLine)+1))
    lcC = LEFT(m.lcC, LEN(m.lcC)-1)
    
    INSERT INTO myCursor (building, var1, var2, var3) VALUES (m.lnBuilding, m.lnA, m.lnB, m.lcC)
    
  ENDDO
  
  = FCLOSE(m.lnFH)

ELSE
  *-- ERROR: Cannot open file...    
ENDIF

RETURN

Open in new window

ASKER CERTIFIED SOLUTION

Olaf Doschke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Olaf Doschke

Even that would fail, as you can't concatenate strings and numbers.

xx_filename =\\WTE\Reports + "xx_filename" + file_index + ".csv"
should be something like:
xx_filename ="\\WTE\Reports" + xx_filename + TRANSFORM(file_index) + ".csv"

Bye, Olaf.