分割foxpro表

Question

我确信这是简单的SQL，但我有一个表，其中包含X(当前为3级)的每个级别的多个记录。我基本上想把这个拷贝到csv文件中，每个级别一个。

我得到了选择的SQL，我可以复制它。我也可以做一个选择来获得文件中唯一级别的列表。我不能工作的是如何让foxpro循环到唯一的级别，并提供一个文件名，并只保存相关的记录。

我正在使用扫描循环唯一的记录，但很明显，我所做的这是错误的。

* identify the different LPG report levels
SELECT STREXTRACT(ALLTRIM(group),"|","|",3) as LPG_level FROM &lcFile GROUP BY LPG_level INTO CURSOR levels

TEXT to lcSql1 noshow textmerge pretext 15
   SELECT 
         LEFT(ALLTRIM(group),ATC("|",ALLTRIM(group))-1) as Sim, 
         STREXTRACT(ALLTRIM(group),"|","|",1) as Company, 
ENDTEXT

TEXT to lcSql2 noshow textmerge pretext 15
         time, 
         SUM(as) as Asset_Share_Stressed,
         SUM(as_us) as Asset_Share_Unstressed 
      FROM <<lcFile>> 
      GROUP BY Sim, 
         Company, 
         Fund, 
         LPG_level,
         Output_group, 
         time 
      ORDER BY sim asc,
            output_group asc
      INTO CURSOR bob
ENDTEXT


TEXT to lcSqlgroup2 noshow textmerge pretext 15    
     RIGHT(ALLTRIM(group),LEN(ALLTRIM(group)) - ATC("|",ALLTRIM(group),4)) as Output_group, 
ENDTEXT

    TEXT to lcSql_fund2 noshow textmerge pretext 15
        STREXTRACT(ALLTRIM(group),"|","|",2) as Fund, 
    ENDTEXT

    TEXT to lcSql_level noshow textmerge pretext 15
        STREXTRACT(ALLTRIM(group),"|","|",3) as LPG_level, 
    ENDTEXT

    &lcSql1 + &lcSql_fund2 + &lcSql_level + &lcSqlgroup2 + &lcSql2

    SELECT levels
    SCAN
        COPY TO output_path + lcFilename + levels.LPG_level for bob.LPG_Level = levels.LPG_Level

    endscan

Answer 1

我不知道为什么你有这么多的文本/结尾。您可以构建您的SQL-选择作为一个长语句.只需在每一行末尾使用分号表示语句在下一行上继续(不像在C#中那样；指示语句的结尾).

无论如何，这个简化程序应该能做到您所拥有的。

SELECT ;
      LEFT(ALLTRIM(group),ATC("|",ALLTRIM(group))-1) as Sim, ;
      STREXTRACT(ALLTRIM(group),"|","|",1) as Company, ;
      STREXTRACT(ALLTRIM(group),"|","|",2) as Fund, ;
      STREXTRACT(ALLTRIM(group),"|","|",3) as LPG_level, ;
      RIGHT(ALLTRIM(group),LEN(ALLTRIM(group)) - ATC("|",ALLTRIM(group),4)) as Output_group, ;
      time, ;
      SUM(as) as Asset_Share_Stressed, ;
      SUM(as_us) as Asset_Share_Unstressed ;
   FROM ;
      ( lcFile ) ;
   GROUP BY ;
      Sim, ;
      Company, ;
      Fund, ;
      LPG_level, ;
      Output_group, ;
      time ;
   ORDER BY ;
      sim asc,;
      output_group ASC ;
   INTO ;
      CURSOR bob

SELECT distinct LPG_Level ;
   FROM Bob ;
   INTO CURSOR C_TmpLevels

SELECT C_TmpLevels
SCAN 
   */ You might have to be careful if the LPG_Level has spaces or special characters
   */ that might cause problems in file name creation, but at your discretion.
   lcOutputFile = output_path + "LPG" + ALLTRIM( C_TmpLevels.LPG_Level ) + ".csv"
   SELECT Bob
   COPY TO ( lcOutputFile ) ;
      FOR LPG_Level = C_TmpLevels.LPG_Level ;
      TYPE csv
ENDSCAN 

在这个场景中，我刚刚构建了整个SQL查询并运行它.从这个结果中，我得到了不同的LPG_Level，因此它与您必须使用的结果集的结构完全匹配。注意，在"FROM“子句中，括号中有(lcFile)。这告诉VFP查找表名的变量名，而不是将名为"lcFile“的实际表作为文字。同样的，当我拷贝到CSV文件时.抄送(lcOutputFile)。

宏"&“可以是强大和有用的，但也可以咬你也特别是如果一个文件名路径有一个空格.那样的话你就完了..。试着习惯在这种情况下使用父母。

Answer 2

试一试如下：

FOR curlevel = 1 TO numlevels
    outfile = 'file' + ALLTRIM(STR(curlevel)) + '.csv'

    TEXT TO contents
        blah blah
    ENDTEXT

    = STRTOFILE(contents, outfile)
ENDFOR

你必须调整事物，但这是一种可以使用的技术。

Answer 3

谢谢你的帮助。当我重复使用代码块时，我根据另一个问题的建议使用了文本/尾文本，但是TBH现在有点过分了，所以我可能会在某个时候清理它。

我确实从我团队中了解一些SQL的其他人那里得到了一些帮助，但是这个解决方案看起来与DRapp的非常相似。

贴在下面作为下次的提醒或者帮助其他人。

FOR i=1 TO lnCnt
lcFile = LOWER(output_path + laFiles[i,1])
lcFilename = LEFT(laFiles[i,1],ATC("~main4",laFiles[i,1])-1)

IF file(lcFile) = .F. then
    ERROR "File " + lcFile + " does not exist"
ENDIF

* Status window, tell user which file being processed
WAIT WINDOW (lcFile + ". File: " + ALLTRIM(STR(i)) + " of " + ALLTRIM(STR(lnCnt))) nowait

* identify the different LPG report levels
SELECT STREXTRACT(ALLTRIM(group),"|","|",3) as LPG_level FROM &lcFile GROUP BY LPG_level INTO CURSOR levels

* Build up the select string, starting with the sim number and company
TEXT to lcSql1 noshow textmerge pretext 15
   SELECT 
         LEFT(ALLTRIM(group),ATC("|",ALLTRIM(group))-1) as Sim, 
         STREXTRACT(ALLTRIM(group),"|","|",1) as Company, 
ENDTEXT

* Add the fund
TEXT to lcSql_fund2 noshow textmerge pretext 15
    STREXTRACT(ALLTRIM(group),"|","|",2) as Fund, 
ENDTEXT

* Add the Output group
TEXT to lcSqlgroup2 noshow textmerge pretext 15    
     RIGHT(ALLTRIM(group),LEN(ALLTRIM(group)) - ATC("|",ALLTRIM(group),4)) as Output_group, 
ENDTEXT

* Not actually required but helps clarify the output groups, the report level (e.g. tax-status, LoB, vclass)
TEXT to lcSql_level noshow textmerge pretext 15
    STREXTRACT(ALLTRIM(group),"|","|",3) as LPG_level, 
ENDTEXT

* Add the rest of the select, with the fields and their output names, the fields to group on and the sort order 
TEXT to lcSql2 noshow textmerge pretext 15
         time, 
         SUM(as) as Asset_Share_Stressed,
         SUM(as_us) as Asset_Share_Unstressed, 
         SUM(cogao) as CoGAO, 
         SUM(cog) as CoG, 
         SUM(cope) as CoPE, 
         SUM(cos) as CoS, 
         SUM(cope_wluk) as CoPE_WLUK, 
         SUM(copd_gteed) as CoPD_Gteed, 
         SUM(copd_other) as CoPD_Other,
         SUM(fprl_resid) as FPRL_resid,
         SUM(fprlcosadj) as FPRL_CoS_Adj,
         SUM(woc_res) as WOC_Reserve,
         SUM(bel) as BEL, 
         SUM(sht) as SH_Transfers, 
         SUM(pol_count) as Pol_Count 
      FROM <<lcFile>> 
      GROUP BY Sim, 
         Company, 
         Fund, 
         LPG_level,
         Output_group, 
         time 
      ORDER BY sim asc,
            output_group asc
      INTO CURSOR bob
ENDTEXT

* These are the variables which were created from the text above. This line combines them and runs them as a select statement
&lcSql1 + &lcSql_fund2 + &lcSql_level + &lcSqlgroup2 + &lcSql2

* Loop over the available report levels (previously exported to the cursor called levels
SELECT levels
SCAN
    * From bob (the cursor created above which has everything in it) for the current report level select out the relevant records
    * and save them to the original filename with the level appended in CSV format
    SELECT * FROM bob WHERE bob.LPG_Level = levels.LPG_Level INTO CURSOR temp
    COPY TO output_path + lcFilename + "_" + ALLTRIM(levels.LPG_Level) + ".csv" TYPE CSV
ENDSCAN


* Next file in list
NEXT i