Oleg_123 at xuppa.com
Oleg_123 at xuppa.com
Tue Oct 14 15:44:56 CDT 2003
Marty, thanks, you are a lifesaver.
> Here are some Access 97 replacement functions
> Not fully tested
>
> Function Split(ByVal Expression As String, Optional Delimiter = " ", _
> Optional limit As Long = -1, Optional Compare As Integer =
> vbBinaryCompare _
> ) As Variant
> '*******************************************
> 'Name: Split (Function)
> 'Purpose: Emulates the A2k/A2k2 Split function
> 'Author: Terry Kreft
> 'Date: December 13, 2001, 02:47:07
> 'Called by: Any
> 'Calls: None
> 'Inputs:
> ' Expression - The string to split
> ' Delimiter - The delimiter to split on
> ' limit - How many terms to return
> ' (Default -1 return all terms)
> ' Compare - How to make the string comparison
> ' for the delimiter
> ' This should be
> ' vbBinaryCompare = 0 (Default)
> ' vbTextCompare = 1
> ' vbDatabaseCompare = 2
> 'Output:
> '*******************************************
>
> Dim varValues As Variant
> Dim lngCount As Long
> Dim intInstr As Integer
> Dim intLenDelim As Integer
> Const ARRAY_LOW_BOUND = 0
>
> On Error GoTo Split_err
>
> varValues = Array()
> If limit <> 0 Then
> lngCount = 0
> intLenDelim = Len(Delimiter)
> intInstr = InStr(1, Expression, Delimiter, Compare)
> Do While intInstr <> 0 And lngCount - limit + 1 <> 0
> ReDim Preserve varValues(ARRAY_LOW_BOUND To lngCount)
> varValues(lngCount) = Left(Expression, intInstr - 1)
> Expression = Mid(Expression, intInstr + intLenDelim)
> intInstr = InStr(1, Expression, Delimiter, Compare)
> lngCount = lngCount + 1
> Loop
> If Len(Expression) <> 0 Then
> ReDim Preserve varValues(ARRAY_LOW_BOUND To lngCount)
> varValues(lngCount) = Expression
> End If
> End If
> Split = varValues
> Split_end:
> Exit Function
> Split_err:
> With Err
> MsgBox .Number & ": " & .Description, vbExclamation, .Source
> End With
> Resume Split_end
> End Function
>
> http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B188007
>
> Option Explicit
>
> Public Function Join(source() As String, Optional _
> sDelim As String = " ") As String
> Dim sOut As String, iC As Integer
> On Error GoTo errh:
> For iC = LBound(source) To UBound(source) - 1
> sOut = sOut & source(iC) & sDelim
> Next
> sOut = sOut & source(iC)
> Join = sOut
> Exit Function
> errh:
> Err.Raise Err.Number
> End Function
>
> Public Function ReadUntil(ByRef sIn As String, _
> sDelim As String, Optional bCompare As VbCompareMethod _
> = vbBinaryCompare) As String
> Dim nPos As String
> nPos = InStr(1, sIn, sDelim, bCompare)
> If nPos > 0 Then
> ReadUntil = Left(sIn, nPos - 1)
> sIn = Mid(sIn, nPos + Len(sDelim))
> End If
> End Function
>
> Public Function StrReverse(ByVal sIn As String) As String
> Dim nC As Integer, sOut As String
> For nC = Len(sIn) To 1 Step -1
> sOut = sOut & Mid(sIn, nC, 1)
> Next
> StrReverse = sOut
> End Function
>
> Public Function InStrRev(ByVal sIn As String, sFind As String, _
> Optional nStart As Long = 1, Optional bCompare As _
> VbCompareMethod = vbBinaryCompare) As Long
> Dim nPos As Long
> sIn = StrReverse(sIn)
> sFind = StrReverse(sFind)
> nPos = InStr(nStart, sIn, sFind, bCompare)
> If nPos = 0 Then
> InStrRev = 0
> Else
> InStrRev = Len(sIn) - nPos - Len(sFind) + 2
> End If
> End Function
>
> Public Function Replace(sIn As String, sFind As String, _
> sReplace As String, Optional nStart As Long = 1, _
> Optional nCount As Long = -1, Optional bCompare As _
> VbCompareMethod = vbBinaryCompare) As String
>
> Dim nC As Long, nPos As Integer, sOut As String
> sOut = sIn
> nPos = InStr(nStart, sOut, sFind, bCompare)
> If nPos = 0 Then GoTo EndFn:
> Do
> nC = nC + 1
> sOut = Left(sOut, nPos - 1) & sReplace & _
> Mid(sOut, nPos + Len(sFind))
> If nCount <> -1 And nC >= nCount Then Exit Do
> nPos = InStr(nStart, sOut, sFind, bCompare)
> Loop While nPos > 0
> EndFn:
> Replace = sOut
> End Function
>
> Oleg_123 at xuppa.com wrote:
>
>>Thanks, I'll declare aRecord as string. Its this part I am having
>> trouble with
>>
>>strSQL = "INSERT table value(" & aRecord(0) & "," & aRecord(8) & ")"
>> con.Execute (strSQL)
>>
>>Where do I specify to what table, and to what fields (in this database)
>> do I want records to be inserted ?
>>Also, Access97 doesn't have split function ? I did that part at home on
>> Access2000
>>
>>
>>
>>>Some ideas:
>>>
>>>I assume what you are saying is that the date is stored in your csv
>>> table as yyyymmdd and access is reading it as mmddyyyy (ie american
>>> date format) and thus your dates are screwed up.
>>>
>>>Using dim aRecord as you have will default the variable to a variant.
>>> (A side note, I always explicity declare, that way I avoid unexpected
>>> surprises if MS decides to change the default behaviour)
>>>
>>>I don't know what split does, but I am assuming it tries to guess what
>>> the variable type is and then stores it as that type. I would first
>>> check your PC date format, but I suspect that you will be stuck with
>>> US interpretation and will have to "manually" force the date to be
>>> read the correct manner.
>>>
>>>First suggestion would be to dim aRecord as a string. This will force
>>> the date (and other fields) to be stored as they appear in the csv
>>> file.
>>> Then you can split the date field using left$/right$/mid$ and
>>> datepart.
>>>
>>>If I am incorrectly guessing what your problem is, maybe someelse will
>>> have a better idea.
>>>
>>>Stuart
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>From: accessd-bounces at databaseadvisors.com
>>>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>>>>Oleg_123 at xuppa.com
>>>>Sent: Wednesday, 15 October, 2003 12:10 AM
>>>>To: accessd at databaseadvisors.com
>>>>Subject: [AccessD] converting csv to table
>>>>
>>>>
>>>>Hey Group
>>>>I have to convert a CSV file to Access table, with one of the records
>>>> changing from yearmonthdate(19980123) to
>>>>monthdateyear(01231998) format.
>>>>So I gues I can't use macro for that since it doesn't recognise
>>>> yearmonthdate format. I wrote the code below yesterday and
>>>>tested it at
>>>>home on diff table and it worked up till including Debug.Print
>>>>aRecord(8)line then I had trouble sending received data to
>>>>table. Now it
>>>>doesn't work at all.. any suggestions ?
>>>>
>>>>
>>>>Dim fs As New Scripting.FileSystemObject
>>>>Dim ts As Scripting.TextStream
>>>>Dim strLine As String
>>>>Dim aRecord
>>>>Dim strSQL As String
>>>>Dim con As New ADODB.Connection
>>>>
>>>>If fs.FileExists("U:\Users\HRIS\oleg\cars testing\trainFROM.csv")
>>>> Then
>>>>
>>>> Set ts = fs.OpenTextFile("U:\Users\HRIS\oleg\cars
>>>>testing\trainFROM.csv", ForReading)
>>>>
>>>> Do Until ts.AtEndOfStream
>>>>
>>>> strLine = ts.ReadLine
>>>> aRecord = split(strLine, ",")
>>>>
>>>> aRecord(8) = Mid(aRecord(8), 5) & Left(aRecord(8), 4)
>>>>
>>>> Debug.Print aRecord(8)
>>>>
>>>> strSQL = "INSERT table value(" & aRecord(0) & "," &
>>>>aRecord(8) & ")"
>>>> 'con.Execute (strSQL)
>>>>
>>>> Loop
>>>>Else
>>>> MsgBox ("file doesn't exist")
>>>>End If
>>>>
>>>>
>>>>
>>>>
>>>>-----------------------------------------
>>>>Get Breaking News from CNN, ABC, NBC, CBS Now.
>>>>http://www.xuppa.com/news/?link=webmail
>>>>
>>>>
>>>>_______________________________________________
>>>>AccessD mailing list
>>>>AccessD at databaseadvisors.com
>>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>>Website: http://www.databaseadvisors.com
>>>>
>>>>
>>>>
>>>_______________________________________________
>>>AccessD mailing list
>>>AccessD at databaseadvisors.com
>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>Website: http://www.databaseadvisors.com
>>>
>>>
>>
>>
>>
>>-----------------------------------------
>>Get Breaking News from CNN, ABC, NBC, CBS Now.
>>http://www.xuppa.com/news/?link=webmail
>>
>>
>>_______________________________________________
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>>
>>
>
> --
> Marty Connelly
> Victoria, B.C.
> Canada
>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
-----------------------------------------
Get Breaking News from CNN, ABC, NBC, CBS Now.
http://www.xuppa.com/news/?link=webmail