1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Query CSV Files

Discussion in 'T&T - Scripting' started by Joe Caverly, Jan 16, 2011.

  1. Joe Caverly

    Joined:
    Aug 28, 2009
    Messages:
    638
    Likes Received:
    6
    This is a batch file to query Comma Separated Value Files, using the plugin available from http://sites.google.com/site/jlcprogrammingstuff/home/tcc/csv-plugin

    An environment variable, containing comma separated values, can be parsed using the @csvParse function.

    Code:
    ::--------------------------------------------------------------------+
    ::  Open a Comma Separated Value file,                                |
    ::  and display values from records, using the CSV Plugin             |
    ::  Joe Caverly                                                       |
    ::  January 2011                                                      |
    ::                                                                    |
    :: USAGE: cvstest csvfile RecordNumber FieldNumber                    |
    ::                                                                    |
    :: EXAMPLE: csvtest.btm csvfile.csv 1 6                               |
    ::                                                                    |
    :: Note that record 0 is the record to contain the field names        |
    ::--------------------------------------------------------------------+
    ::
    :: Uncomment the following lines to create a CSV File
    ::echo Company,Address,City,State,Zip,Country > csvFile.csv
    ::echo JP Software,P.O. Box 328,Chestertown,MD,21620,USA >> csvFile.csv
    ::echo PowerBASIC Inc.,2061 Englewood Road,Englewood,FL,34223,USA >> csvFile.csv
    @setlocal
    @echo off
    ::--------------------------------------------------------------------+
    :: Load the CSV Plugin                                                |
    ::--------------------------------------------------------------------+
    if not plugin csv plugin /l csv
    ::--------------------------------------------------------------------+
    :: Designate a file to use for this batch                             |
    ::--------------------------------------------------------------------+
    if not exist %1 goto nofile
    set csvName=%1
    ::--------------------------------------------------------------------+
    :: Display Structure                                                  |
    ::--------------------------------------------------------------------+
    echo Structure for CSVFile: %csvName
    echo Number of CSV records: %@csvRecordCount[%csvName]
    echo Field  Field Name
    do FieldNumber=1 to %@csvFieldCount[%csvName]
      screen %_row 0 %@format[5,%FieldNumber]
      ::--------------------------------------------------------------------+
      :: Field Name                                                         |
      ::--------------------------------------------------------------------+
      screen %_row 7 %@csvGetField[%csvName,0,%FieldNumber]
      echo.
    enddo
    echo.
    ::--------------------------------------------------------------------+
    :: Record Number to get                                               |
    ::--------------------------------------------------------------------+
    set ValidInput=N
    if %2 le %@csvRecordCount[%csvName] set ValidInput=Y
    if %2 gt 0 set ValidInput=Y
    if %ValidInput eq N set Record2Get=1
    if %ValidInput eq Y set Record2Get=%2
    if %Record2Get gt %@csvRecordCount[%csvName] goto ROOR
    echo.
    echo Record Number: %Record2Get
    ::--------------------------------------------------------------------+
    :: Display the record                                                 |
    ::--------------------------------------------------------------------+
    echo %@line[%csvName,%Record2Get]
    ::--------------------------------------------------------------------+
    :: Field Number to get                                                |
    ::--------------------------------------------------------------------+
    set ValidInput=N
    if %3 le %@csvFieldCount[%csvName] set ValidInput=Y
    if %3 gt 0 set ValidInput=Y
    if %ValidInput eq N set Field2Get=1
    if %ValidInput eq Y set Field2Get=%3
    if %Field2Get gt %@csvFieldCount[%csvName] goto FOOR
    ::--------------------------------------------------------------------+
    :: Display a field                                                    |
    ::--------------------------------------------------------------------+
    echos Field %Field2Get is` `
    echo %@csvGetField[%csvName,%Record2Get,%Field2Get]
    ::--------------------------------------------------------------------+
    :: Parse a CSV environment variable                                   |
    ::--------------------------------------------------------------------+
    echo` `
    echo Parse a CSV Environment String
    set theCSVString=JP Software,P.O. Box 328,Chestertown,MD,21620,USA
    echo %theCSVString
    echo Field 2 is %@csvParse[%theCSVString,2]
    goto eoj
    ::--------------------------------------------------------------------+
    :: Problem opening the database                                       |
    ::--------------------------------------------------------------------+
    :abort
    echo Database is in use, or another error occured opening the database.
    goto eoj
    ::--------------------------------------------------------------------+
    :: File does not exist                                                |
    ::--------------------------------------------------------------------+
    :nofile
    echo File does not exist.
    echo USAGE: dbstruct x:\data\mycsv.csv
    goto eoj
    ::--------------------------------------------------------------------+
    :: Field Out Of Range                                                 |
    ::--------------------------------------------------------------------+
    :FOOR
    echo Field Out Of Range (Max. %@csvFieldCount[%csvName])
    goto eoj
    ::--------------------------------------------------------------------+
    :: Record Out Of Range                                                |
    ::--------------------------------------------------------------------+
    :ROOR
    echo Record Out Of Range (Max. %@csvRecordCount[%csvName])
    ::--------------------------------------------------------------------+
    :: End Of Job                                                         |
    ::--------------------------------------------------------------------+
    :eoj
    if plugin csv plugin /u csv
    endlocal
    
    Joe
     

Share This Page