Need acutal length (characters) of StringField

[DawnRom]

I have a csv file I am reading in and checking the field length - I am getting errors that say my line is too short based on the error logic I coded. But when I look at the csv file, it is fine.

Attribute VB_Name = "Module2"



Sub ImportCSVFileARDet(filenameCSV, numFieldsINT)
On Error GoTo ImportCSVFile_Err

Const ForReading = 1
Dim fso, f As Object
Dim FileLineString As String
Dim LineOpenQuotePosition As Integer
Dim LineCloseQuotePosition As Integer
Dim tmpQryString As String
Dim i As Integer
Dim cnt As Long, totalCnt As Long
ctr = 0
Dim tmpSQL

ErrMsg = ""

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(filenameCSV)


GlobalMessage = GlobalMessage & Now() & ": Started Processing file " & filenameCSV & " (" & f.Size & " bytes)." & vbNewLine

OpenConnection ' open connection
OBJdbConnection.BeginTrans
msg = "INFO: Started importing file " & filenameCSV
tmpSQL = "INSERT INTO UpdateLog ( UpdateLogDate, UpdateLogEntry, UpdateComplete )"
tmpSQL = tmpSQL & " VALUES ( CONVERT(datetime, GETDATE(), 101), '" & Left(msg, 255) & "', 'I' )"
OBJdbConnection.Execute tmpSQL
OBJdbConnection.CommitTrans
'CloseConnection ' close connection

'Debug.Print Right(filenameCSV, Len(filenameCSV) - InStr(1, filenameCSV, "\stg")) & " - start: " & Now()

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fspenTextFile(filenameCSV, ForReading)
If (f.AtEndOfStream) Then
ErrMsg = "Error: Data File (" & Right(filenameCSV, Len(filenameCSV) - InStr(1, filenameCSV, "\stg")) & ") is empty."
GoTo ImportCSVFile_Err
End If

'OpenConnection ' open connection
OBJdbConnection.BeginTrans
' delete import table contents
OBJdbConnection.Execute "DELETE FROM CSVFileImport"
' delete data table contents
OBJdbConnection.Execute "truncate table ARDtlData"
OBJdbConnection.CommitTrans
CloseConnection ' close connection

cnt = 0
totalCnt = 0

ImportCSVLoop_Start:
On Error GoTo ImportCSVFile_Err2
While (Not f.AtEndOfStream)
If cnt = 0 Then
OpenConnection ' open connection
OBJdbConnection.BeginTrans
End If
cnt = cnt + 1
totalCnt = totalCnt + 1
'Debug.Print Right(filenameCSV, Len(filenameCSV) - InStr(1, filenameCSV, "\stg")) & " - start: " & Now()

tmpQryString = "INSERT INTO CSVFileImport ("
For i = 1 To numFieldsINT
tmpQryString = tmpQryString & "Field" & CStr(i) & ", "
Next
tmpQryString = Left(tmpQryString, Len(tmpQryString) - 2) & ") SELECT "

FileLineString = f.ReadLine

NormalLen = 126
If Len(Trim(FileLineString)) < NormalLen Then
WriteLog True, "Line " & totalCnt & " in " & filenameCSV & "is too short. Will be ignored."
WriteLog True, "Line length is " & Len(Trim(FileLineString)) & " characters but it should be " & NormalLen & " characters."
WriteLog True, "Line is -->" & FileLineString & "<--"
GoTo IgnoreLine
End If


Example of csv file information:

" 0002018"," 03975166"," 002"," 001","-0000406.17","P"," ","PHX","42122 ","41072 ","2007/09/24","2007/09/24"," 140350"
" 0002018"," 03976357"," 001"," 001","-0000228.90","P"," ","TIR","42122 ","41072 ","2007/09/24","2007/09/24"," 140350"
" 0002018"," 03976498"," 001"," 001","-0000845.65","P"," ","MES","42122 ","41072 ","2007/09/24","2007/09/24"," 140350"
" 0002018"," 03978182"," 001"," 001","-0000863.09","P"," ","PEO","42519 ","41716 ","2007/10/26","2007/10/26"," 090607"
" 0002018"," 03979089"," 001"," 001","-0000837.69","P"," ","PHX","42519 ","41716 ","2007/10/26","2007/10/26"," 090607"


It happens randomly.

 

[guitarzan]

Which line is it complaining about, and what length does the error message say it is? Maybe it's a blank line at the end?

 

[DawnRom]

Here is a shot of the error log in SQL:

4/29/2009 2:05:42 PM - Line 11085 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:05:42 PM - Line length is 52 characters but it should be 126 characters.
4/29/2009 2:05:42 PM - Line is -->" 0001893"," 03991205"," 001"," 001","-0000081.36","<--
4/29/2009 2:05:42 PM - Line 11086 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:05:42 PM - Line length is 74 characters but it should be 126 characters.
4/29/2009 2:05:42 PM - Line is -->P"," ","VEG","2610 ","42355 ","2007/12/04","2007/12/04"," 100500" <--
4/29/2009 2:05:48 PM - Line 12823 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:05:48 PM - Line 3575 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:05:48 PM - Line is -->" 0002018"," 04020698"," 001"," 001","-0000785.68","P"," ","MES","42981 ","42268 ","2007/11/28","20<--
4/29/2009 2:05:48 PM - Line is -->" 0001291"<--
4/29/2009 2:05:48 PM - Line 12824 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:05:48 PM - Line 3576 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:05:48 PM - Line length is 116 characters but it should be 126 characters.
4/29/2009 2:05:48 PM - Line is -->07/11/28"," 110550" <--
4/29/2009 2:05:49 PM - Line is -->," 03885122"," 001"," 002","-0000285.01","A","CC","ROM","V6422 ","39997 ","2007/08/06","2007/08/06"," 153048" <--
4/29/2009 2:08:00 PM - Line 21651 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:08:00 PM - Line 25216 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:08:00 PM - Line length is 17 characters but it should be 126 characters.
4/29/2009 2:08:00 PM - Line length is 3 characters but it should be 126 characters.
"P"," ","SLC","80775 ","42155 ","2007/11/20","2007/<--
4/29/2009 2:08:00 PM - Line is -->" 0<--
4/29/2009 2:08:00 PM - Line 21652 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:08:00 PM - Line 25217 in D:\Sunstats iSeries Files\stgardet.csvis too short. Will be ignored.
4/29/2009 2:08:00 PM - Line is -->11/20"," 121907" <--
it should be 126 characters.
4/29/2009 2:08:00 PM - Line is -->9469"," 001"," 001","-0000331.75","P"," ","MES","275887 ","43690 ","2008/02/20","2008/02/20"," 133516" <--
4/29/2009 2:08:00 PM - Line is -->003225"," 03804066"," 001"," 001","-0000790.55","P"," ","CSN","8574 ","39208 ","2007/06/29","2007/06/29"," 125336" <--


And here are those records in the csv file:

Line 3575 is the third one:

" 0002018"," 04012276"," 001"," 001","-0000424.42","P"," ","DRV","42981 ","42268 ","2007/11/28","2007/11/28"," 110550"
" 0002018"," 04013048"," 001"," 001","-0000056.65","P"," ","TIR","42981 ","42268 ","2007/11/28","2007/11/28"," 110550"
" 0002018"," 04020698"," 001"," 001","-0000785.68","P"," ","MES","42981 ","42268 ","2007/11/28","2007/11/28"," 110550"
" 0002018"," 04026808"," 001"," 001","-0000746.13","P"," ","SLC","42981 ","42268 ","2007/11/28","2007/11/28"," 110550"
" 0002018"," 04030039"," 001"," 001","-0000207.82","P"," ","SLC","42981 ","42268 ","2007/11/28","2007/11/28"," 110550"

 

[strongm]

Can you please try changing:

Set f = fspenTextFile(filenameCSV, ForReading)

to

Set f = fspenTextFile(filenameCSV, ForReading, -1)

 

[DawnRom]

I will give that a try - thanks.

 

[strongm]

Any luck?