?
Solved

Import CSV Files

Posted on 2014-09-22
5
Medium Priority
?
1,360 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 30

Accepted Solution

by:
Olaf Doschke earned 2000 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 30

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

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