Import CSV - Type mismatch only occassionally

[bzac]

Hi Programmers,
There is module to read CSV file and update an access table. CSV file contains two fields (First is Date & Time and the second is Quantity)in each line, usually 24 0r more lines. The date field is inputing into a string variable and then converting it into date format. While doing this, occassionally a type mismatch error is coming, because while inputing date into a string, it is getting values like ":00:00" or "06/21/01 03:00:0006/21/01 03:00:00" and when trying to convert it raises error.

Please help me to solve this and put some idea to trap this error. Following is a part of my code:-

::::::::::::::::::::::::::::

'READING CSV FILE
Open "\DFS\dfsprd.IN" For Input As #1 'Open file for input
Dim xDate As String
Do While Not EOF(1) 'loop until end of file
xDate = ""
Input #1, xDate, AQty 'read data into two variables

' Tdate = CDate(Trim(xDate))
' ADate = FormatDateTime(Tdate, vbGeneralDate)
ADate = FormatDateTime(xDate, vbGeneralDate)
If adoHrActRS.RecordCount <> 0 Then
'find the record if exist else addnew
Call RstCheck
Else
'add recordds as new
Act_HrAdd
End If
Loop
Close #1
End If

'close the recordset
adoHrActRS.Close
Set adoHrActRS = Nothing
End Sub

 

[woyler]

How are you defining the file structure? Are you using a Import Spec? You may have to pull the data in by cursor position rather then using a delimited character. ie:

Do Until EOF(#FILE)
Line Input #FILE, sLineFeed
'assign values
MyDate = Mid$(sLineFeed, 1,17)
MyQuantity = MId$(sLineFeed,18,2)
Loop

 

[bzac]

Thanks woyler.
I am not using an Import Spec. I will try it in your way, hope that will work.
Once again thank you very much for giving an idea.

 

[bzac]

After I put a filestructure, then also the type mismatch is coming. Please help me, urgent!

HrConnect 'connect Hr_Source and create a RecordSet for start & end date-time

If adoHrActRS.RecordCount <> 0 And Not adoHrActRS.BOF Then
adoHrActRS.MoveFirst 'Hr-Source table Record set
End If
'READING CSV FILE
Open &quot;\DFS\dfsprd.IN&quot; For Input As #1 'Open file for input
Dim xDate As String
Dim Textline
Do While Not EOF(1) 'loop until end of file
' xDate = &quot;#12:00:00 AM#&quot;

'---------------------
' xDate = &quot;&quot;
' Input #1, xDate, AQty 'read data into two variables
'---------------------
Textline = &quot;&quot;
Line Input #1, Textline
'assign values
xDate = Mid$(Textline, 1, 19)
AQty = Mid$(Textline, 21, 11)
' Tdate = CDate(Trim(xDate))
' ADate = FormatDateTime(Tdate, vbGeneralDate)
ADate = FormatDateTime(xDate, vbGeneralDate)
'xdate=dateadd(&quot;h&quot;,1,adate)
Label7.Caption = ADate
If adoHrActRS.RecordCount <> 0 Then
'find the record if exist else addnew
Call RstCheck
Else
'add recordds as new
Act_HrAdd
End If
Loop
Close #1
End If

'close the recordset
adoHrActRS.Close
Set adoHrActRS = Nothing
End Sub

 

[bzac]

Here is bit of my CSV file:-

06/25/2001 13:00:00,0.000000
06/25/2001 14:00:00,0.000000
06/25/2001 15:00:00,0.000000
06/25/2001 16:00:00,0.000000
06/25/2001 17:00:00,0.000000
06/25/2001 18:00:00,0.000000

 

[MichaelRed]

your orig code is more-or-less o.k. ---- except ----check xdate isdate before attempting to coerce:

If (IsDate(xDate) Then
[tab]ADate = FormatDateTime(xDate, vbGeneralDate)
Else
[tab]ADate = SomeDate
End If

Since the dates in the file are ALL the same (from your example) and happen to all be &quot;on the Hour - each Hour&quot;, you can cheat (except for the first record) by saving the last date/time and making the next date just the prev. + 1 Hr if xDate doesn't compute to a date:

(before reading the first record)
ADate = Date(Now)[tab]'Initalize ADate to Midnight of today

If (IsDate(xDate) Then
[tab]ADate = FormatDateTime(xDate, vbGeneralDate)
Else
[tab]ADate = DateAdd(&quot;h&quot;, 1, SomeDate)
End If
[tab]SomeDate = ADate



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jjames]

bzac,

your problem seems to be with the data. you refer to a Quantity field in your file. Although your sample data does not show it, you may be getting quantity values that are greater then 999 and these may be stored in your file as 1,000 or 2,452. The comma would mess you up if this is the case.

 

[bzac]

Thank you very much for the responses. I think my problem is somewhat solved. CSV file process was in a loop. I was using another C++ application with Shell to import data and create CSV. As it was in a loop(usually 40 to 50 times), I had to put &quot;shell wait&quot; to wait for the application to finish creating the CSV. After putting a wait to complete, at present it is working perfectly OK.

Thank you very much.