IonicWind Snippit Manager 2.xx Released! Install it on a memory stick and take it with you! With or without IWBasic!
Started by Jim Scott, December 30, 2006, 07:44:13 PM
0 Members and 1 Guest are viewing this topic.
Def pdb as PointerDef hstmt as intdef error as stringDef NumOfCols as Intdef CLIENT_TYPE as stringdef LAST_NAME as stringdef FIRST_NAME as stringdef SITE_LOC as stringdef MEMBER_NUM as stringdef CHECKIN as DBDATEdef CHECKOUT as DBDATEdef UNIT_NUM as stringOPENCONSOLEpdb=dbConnect("Microsoft Text Driver (*.txt; *.csv)",GetStartPath,"")If pdb <> Null Print "Connection To Database Established" hstmt = dbExecSQL(pdb,"SELECT * FROM SmallFile.csv") error = dbGetErrorCode(hstmt) If Len(error) > 0 Print Print "Error Code: ", error Print "Error Text: ", dbGetErrorText(hstmt) Print EndIf If hstmt <> Null NumOfCols = dbGetNumCols(hstmt) Print "Number of Columns in Dataset = ", NumOfCols dbBindVariable(hstmt,1,Client_Type) dbBindVariable(hstmt,2,Last_Name) dbBindVariable(hstmt,3,First_Name) dbBindVariable(hstmt,4,Site_Loc) dbBindVariable(hstmt,5,Member_Num) dbBindDate(hstmt,6,CheckIn) dbBindDate(hstmt,7,CheckOut) dbBindVariable(hstmt,8,Unit_Num) While dbGetNext(hstmt) <> Null print Len(Client_Type)," ,", Len(Last_Name)," ,", Len(First_Name),_ ," ,", Len(Site_Loc)," ,", Len(Member_Num)," ,", Len(CheckIn),_ ," ,", Len(CheckOut)," ,", Len(Unit_Num) Print Client_Type,",",Last_Name,",",First_Name,",",Site_Loc,_ ,",",Member_Num,",",CheckIn,",",CheckOut,",",Unit_Num Print EndWhile dbFreeSQL(hstmt) EndIf dbDisconnect(pdb)Else Print "Connection Not Established"EndIfPrint "Press any key to exit"Do:Until INKEY$ <> ""CLOSECONSOLEEND
Client Type,Last Name,First Name,Site Loc.,Member#,CheckIn,CheckOut,Unit #Member,ADAMS,WILLIAM,53,296252920,26-Dec-2006,1-Jan-2007,41Member,AMES,ALBION,33,200068864,14-Dec-2006,28-Dec-2006,65
Connection To Database EstablishedNumber of Columns in Dataset = 859 ,0 ,0 ,0 ,0 ,6 ,6 ,0Member,ADAMS,WILLIAM,53,296252920,26-Dec-2006,1-Jan-2007,41,,,,,,,58 ,0 ,0 ,0 ,0 ,6 ,6 ,0Member,AMES,ALBION,33,200068864,14-Dec-2006,28-Dec-2006,65,,,,,,,Press any key to exit
[SmallFile.csv]ColNameHeader=TrueFormat=CSVDelimitedMaxScanRows=0CharacterSet=ANSICol1=CLIENT_TYPE Char Width 255Col2=LAST_NAME Char Width 255Col3=FIRST_NAME Char Width 255Col4=SITE_LOC Char Width 255Col5=MEMBER_NUM Char Width 255Col6=CHECKIN DateTimeCol7=CHECKOUT DateTimeCol8=UNIT_NUM Char Width 255
QuoteThe ODBC Text driver supports both delimited and fixed-width text files. A text file consists of an optional header line and zero or more text lines.Although the header line uses the same format as the other lines in the text file, the ODBC Text driver interprets the header line entries as column names, not data.A delimited text line contains one or more data values separated by delimiters: commas, tabs, or a custom delimiter. The same delimiter must be used throughout the file. Null data values are denoted by two delimiters in a row with no data between them. Character strings in a delimited text line can be enclosed in double quotation marks (""). No blanks can occur before or after delimited values.The width of each data entry in a fixed-width text line is specified in a schema. Null data values are denoted by blanks.Tables are limited to a maximum of 255 fields. Field names are limited to 64 characters, and field widths are limited to 32,766 characters. Records are limited to 65,000 bytes.A text file can be opened only for a single user. Multiple users are not supported.
Def pdb as PointerDef hstmt as intdef error as stringdef CLIENT_TYPE as stringdef LAST_NAME as stringdef FIRST_NAME as stringdef SITE_LOC as stringdef MEMBER_NUM as stringdef CHECKIN as DBDATEdef CHECKOUT as DBDATEdef UNIT_NUM as stringOPENCONSOLEPrint "Looking in " + GetStartPathpdb=dbConnect("Microsoft Text Driver (*.txt; *.csv)",GETSTARTPATH,"")Print "pdb Value Returned = ", pdbIF pdb <> NULL PRINT "Connection Established" hstmt = dbExecSQL(pdb,"SELECT * FROM SmallFile.csv") error = dbGetErrorCode(hstmt) IF LEN(error) PRINT PRINT "Error Code: ", error PRINT "Error Text: ", dbGetErrorText(hstmt) PRINT ENDIF IF hStmt WHILE dbGetNext(hstmt) dbgetdata(hstmt,1,CLIENT_TYPE) dbgetdata(hstmt,2,LAST_NAME) dbgetdata(hstmt,3,FIRST_NAME) dbgetdata(hstmt,4,SITE_LOC) dbgetdata(hstmt,5,MEMBER_NUM) dbgetdate(hstmt,6,CHECKIN) dbgetdate(hstmt,7,CHECKOUT) dbgetdata(hstmt,8,UNIT_NUM) print PRINT CLIENT_TYPE, ", ", LAST_NAME, " ", FIRST_NAME, ", ", SITE_LOC, ", ", MEMBER_NUM, ", ", CHECKIN.day, "/", CHECKIN.month, "/", CHECKIN.year, ", ", CHECKOUT.day,"/",CHECKOUT.month,"/",CHECKOUT.year, ", ", UNIT_NUM print "<-------------->" print LAST_NAME, " ", MEMBER_NUM print "<-------------->" PRINT ENDWHILE dbFreeSQL(hstmt) ENDIF dbDisconnect(pdb)Else Print "Connection Not Established"ENDIFPRINT "Press any key to exit"DO:UNTIL INKEY$ <> ""CLOSECONSOLEEND
[smallfile.csv]ColNameHeader=TrueFormat=TabDelimitedMaxScanRows=0CharacterSet=ANSIDateTimeFormat=dd-mmmm-yyyyCol1=CLIENT_TYPE Char Width 255Col2=LAST_NAME Char Width 255Col3=FIRST_NAME Char Width 255Col4=SITE_LOC Char Width 255Col5=MEMBER_NUM Char Width 255Col6=CHECKIN DateCol7=CHECKOUT DateCol8=UNIT_NUM Char Width 255
Client_Type Last Name First Name Site Loc Member# CheckIn CheckOut Unit#Member AMES ALBION 33 200068864 14-12-2006 28-12-2006 65Member PAUL QUIERST 22 305634213 22-08-2006 01-01-2009 38Member ADAMS WILLIAM 53 296252920 09-12-2006 10-12-2007 41