Solved

Import CSV Files

Posted on 2014-09-22
5
1,095 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
ID: 40338602
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
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Creating and Managing Databases with phpMyAdmin in cPanel.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

17 Experts available now in Live!

Get 1:1 Help Now