[AccessD] converting csv to table

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




More information about the AccessD mailing list