Solved

Import CSV Files

Posted on 2014-09-22
5
1,024 Views
Last Modified: 2014-09-25
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
0
Comment
Question by:campbme
  • 2
5 Comments
 
LVL 15

Expert Comment

by:Insoftservice
Comment Utility
Why you are not using load() .
It would solve all your issue.
Please provide sample csv file and db structure
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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

0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
Comment Utility
Pavel surely has given a good answer already, without looking into details.

Here a few things about your code: You can do much better without our help. This must give you several errors which will tell you what do do.


1. SELECT * FROM myCursor WHERE ROW > 4
Will result in a workarea alias named "query", which is empty, as myCursor is empty.
This line of code is totally useless
2. USE IMPORT EXCLUSIVE
will then open import.dbf exclusive You wouldn't need to do this with each loop iteration, only once before starting the loop. My guess is this fails in the second iteration, as import is already open exclusive, and even if your vfp code has a table open exclusive, and not any other code or user running in parallel, you can't open it again, exclusive is exclusive, even to yourself. Since you have the query before this line, the current workarea will be the alias "query" and so you will try to open IMPORT.DBF exclusive again, which will error.
3.  APPEND FROM (m.xx_fileName) TYPE DELIMITED WITH CHARACTER ,
will import into import.dbf form the csv file
4.  SELECT * FROM m.xx_filename INTO TABLE LDY
should error, because you can't query a csv file, VFP SQL-Select only works on DBFs.
5. DISPLAY MEMORY LIKE myCursor*
A cursor is not a memory variable. It's a dbf with TMP file extension, a file, which is virtualised in memory, as long as it doesn't get too large, but in the end it's a file, not a memory variable, to look into a cursor you use BROWSE, or display it in a grid, as you do with any DBF.

If you don't want row 1-4 from each CSV file, you have to address that after each APPEND, not beforehand. This can't work at all and I'm puzzled what you really want to achieve with your code at all, besides reading in the 24 CSV files.

Maybe you want:

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"
    APPEND FROM (m.xx_fileName) TYPE DELIMITED WITH CHARACTER ,
ENDFOR
BROWSE

Open in new window


Bye, Olaf.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Creating and Managing Databases with phpMyAdmin in cPanel.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now