|
Subject:
|
double quotes in data - giving error in storage
|
|
Posted By:
|
lawsoncobol
|
Post Date:
|
8/9/2006 12:09:09 AM
|
Hi friends,
My project : reading a text file , storing the data in CSV file and then importing to MS access database
previously i had problem that my data contains comma . so the data was getting splitted while storing so i figured it out, like this
Data Input - 98,789
If InStr(strField, ",") <> 0 Then strField = Chr(34) & strField & Chr(34)
Char(34) = "
Data Output = "98,789
Stored in CSV and Database as 98,789
But now my problem is my data is
O"KEANE, JAMES
Data input = O"KEANE, JAMES
If InStr(strField, ",") <> 0 Then strField = Chr(34) & strField & Chr(34)
Data output = "O"KEANE, JAMES"
Stored in CSV and Database = Field 1 = OKEANE
Field 2 = JAMES"
i have used trim the space
I want it to be stored as O"KEANE, JAMES in Field 1
Can anybody help me with this ?
Thanks and Regards
Lawson, COBOL
|
|
Reply By:
|
lawsoncobol
|
Reply Date:
|
8/10/2006 3:56:19 AM
|
hi friends
i figured it out by myself
i divided the string into two eliminating double quotes and then concatinating two string and finally adding double quotes again to
whole new string and it works fine
Data input = O"KEANE, JAMES
If InStr(strField, Chr(34)) <> 0 Then
strfield1 = Trim(Mid(strField, 1, InStr(strField, Chr(34))))
' Data Output = O"
strfield2 = Trim(Mid(strField, InStr(strField, Chr(34)), 15))
' Data Output = "KEANE, JAMES
strField = strfield1 & strfield2
' Data Output = O""KEANE, JAMES
End If
If InStr(strField, ",") <> 0 Then strField = Chr(34) & strField & Chr(34)
If strField = "" Then strField = ""
'Data Output = ""O"KEANE, JAMES""
Stored in CSV as = O"KEANE, JAMES
Thanks and Regards
Lawson, COBOL
|
|
Reply By:
|
lawsoncobol
|
Reply Date:
|
8/21/2006 2:55:45 AM
|
Hi friends better way to handle any kind of character in the data is call the Para " Remove_any_char " by command Call Remove_any_char(Field)
Field - which has to be filtered
Char (34) = "
Sub Remove_any_char (strField)
Dim i As Integer
Dim strField1 As String
For i = 1 To Len(strField)
If Mid(strField, i, 1) = Chr(34) Then GoTo NextChar
strField1 = strField1 & Mid(strField, i, 1)
NextChar:
Next i
If strField1 <> "" Then
strField = strField1
strField = Chr(34) & strField & Chr(34)
End If
End Sub
Above code removes " in the data present anywhere
like wise we can check for
If Mid(strField, i, 1) = "-" Then GoTo NextChar
or
If Mid(strField, i, 1) = " " Then GoTo NextChar <- checks for space
or
If Mid(strField, i, 1) = "(" Then GoTo NextChar
Thanks and Regards
Lawson, COBOL
|
