IonicWind Software

IWBasic => Database => Topic started by: Brian on June 16, 2015, 02:14:56 PM

Title: Getting row count of CSV file
Post by: Brian on June 16, 2015, 02:14:56 PM
Hi,

I've just been messing about with a CSV file which had five lines, with no header. For some reason,
it would only load three lines. I messed about with the file, and came to the conclusion there must
be a "hidden" code in there which was stopping it from loading the last two lines, as copying the
data to a new file seemed to work

The CSVFileView program from Nirsoft had no problems at all loading the file. I am using the
Microsoft text/csv driver

So, is it possible to do a pre-load row count of the csv file using SQL, and then comparing
that number with the lines actually loaded to my listview? And possibly creating an error
message to say that the load was incomplete?

I suppose I could do a row count of the csv file before I attempt to load it, but that could
take a while if it was a large file

I am using this code to do the business, and also creating a schema.ini for each load:

SUB DataGridFillgah(connection:POINTER,sqlStatement:STRING,windowHandle:WINDOW,dataGridID:INT)
   recordSet=dbExecSql(connection,sqlStatement)
   conLVColumnListSet(windowHandle,dataGridID,dbListColumns(connection,"",recordSet),100,1)
   colCount=dbGetNumCols(recordSet)
   rowNames=NEW(STRING,colCount)
   colStat=NEW(INT,colCount)
FOR loop=1 TO colCount
   dbBindVariable(recordSet,loop,#<STRING>rowNames[loop-1,0],#<INT>colStat[loop-1])
NEXT loop
   rowNum=0
   conDrawOff(windowHandle,dataGridID)
WHILE dbGet(recordSet)
   CONTROLCMD windowHandle,dataGridID,@LVINSERTITEM,rowNum,#<STRING>rowNames[0,0]
FOR loop=2 to colCount
IF #<INT>colStat[loop-1]<>-1
   CONTROLCMD windowHandle,dataGridID,@LVSETTEXT,rowNum,loop-1,#<STRING>rowNames[loop-1,0]
ENDIF
NEXT loop
   rowNum++
ENDWHILE
   conDrawOn(windowHandle,dataGridID)
FOR loop=0 TO colCount
   CONTROLCMD windowHandle,dataGridID,@LVSETCOLWIDTH,loop,-1|-2
NEXT loop
   dbFreeSQL(recordSet)
RETURN
ENDSUB

Many thanks,

Brian
Title: Re: Getting row count of CSV file
Post by: LarryMc on June 16, 2015, 10:20:28 PM
Maybe this will work

QuoteSQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:
int no_recs
SELECT COUNT(*) AS no_recs FROM table_name;
Title: Re: Getting row count of CSV file
Post by: Brian on June 17, 2015, 02:08:57 AM
Thanks, Larry,

I hadn't seen that one!

Brian