May 24, 2022, 02:11:41 AM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


Getting row count of CSV file

Started by Brian, June 16, 2015, 02:14:56 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Brian

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

LarryMc

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;
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Brian

Thanks, Larry,

I hadn't seen that one!

Brian