Solved

Import CSV Files

Posted on 2014-09-22
5
1,296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 15

Expert Comment

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

Expert Comment

by:pcelba
ID: 40339195
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
ID: 40339713
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
ID: 40341956
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

Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

695 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