Importing .csv

[radon]

Though I will most likely not use the code you provided I am a little interseted in how to write SQL statements. I noticed that I got an error when I tried to use your code with my copy of my database that is in .csv format and when I debugged the code this line was highlighted:

db.Execute strSQL, dbFailOnError

was I not supposed to simply copy the code into the program or were there parts that I needed to rewrite to fit my example?

This is a side note though, I really need to fix the code to work with my copy of the database that is in excel format.

 

[radon]

Sorry for the continual posting but I want to keep you guys updated so that we don't run circles. I created a fourth column in the temploadxls table since the error I kept getting was about how the data being imported needed one. I figured that I could see what was in this fourth column. There wasn't anything so is there a work around to this or am I stuck with an unused but somehow necesary fourth column?

 

[datAdrenaline]

It is VERY difficult to write direct copy/paste code ... especially if we don't he your db ... also, the line that errored out can easily be effected by your inputs... so ... consider the code posted as AIR CODE, which means that I did my best to make it Copy/Paste, but you may have to tweak it ... and I can help tweak it with more info ... like for this case, what was the value of strSQL when that line errored out.

 

[Mutdogus]

I don't know if you got arround your date problem yet but I wrote this a long time ago. All you have to do is wrap your date in it and it comes out a date that MS Access likes.

I had to account for more than just a . in my dates but this should work for your date problem too.

Public Function ParseDate(field As String) As Date

'Date: September 14, 2005
'Feel free to use this code in its entirety
'If you find a bug and fix it, please let me know
'at wdmutespaugh@gmail.com

'Purpose:
'Used to get rid of the SubSeconds that appear on the end
'of some dates.
'Make sure that you are passing dates into this function as a String
'Type variable. DO NOT PASS A DATE TYPE.  IT WILL BUG!

On Err GoTo ErrHandler

Dim specialchar As Long
Dim fieldposition As Long
Dim examinechar As Variant
Dim fieldlength As Long
Dim fieldholder As String

'This is used to count the special characters
specialchar = 0
'This is used to loop through the field character by character
fieldposition = 1
'This is used to determine the maximum number of times to loop
fieldlength = Len(field)

'Does the loop from 1 to the max length of the field
For counter = 1 To fieldlength
examinechar = Right(Left(field, fieldposition), 1)

    If examinechar = "/" Then
    specialchar = specialchar + 1
    End If
    If examinechar = ":" Then
    specialchar = specialchar + 1
    End If
    
    If specialchar = 4 Then
    fieldholder = Left(field, (fieldposition + 2))
    ParseDate = fieldholder
    Exit Function
    End If
    
    fieldposition = fieldposition + 1

Next

Exit Function

ErrHandler:
Resume

End Function

 

[datAdrenaline]

Wow ... thats a lot of code! ... it seems the expression I provided would do just fine... Unless you can not garentee that there is a sub-second appendage ...

From the immediate window ..
? IIf(Len("10/16/2008 22:30:05.1"), CDate(Left("10/16/2008 22:30:05.1", InStr(1,"10/16/2008 22:30:05.1",".") - 1)), Null)
10/16/2008 10:30:05 PM

However the following will raise an error because the CDate() will be evaluating a ZLS and trying to covert that to a date, which it can't do.
? IIf(Len("10/16/2008 22:30:05"), CDate(Left("10/16/2008 22:30:05", InStr(1,"10/16/2008 22:30:05",".") - 1)), Null)

So ... we could add a test for the "." by wrapping the InStr() portion with an IIf() ... or .. go with a UDF that can handle various inputs ...

Public Function fTrimSubSeconds(varVariant As Variant, _
                                Optional strSubDelimiter As String = ".") As Variant
'Used to trim sub-seconds off of a string that looks like a date
'This function expects input that is a valid date, other than the
'sub-seconds appendage, plus the delimiter of the subseconds MUST
'be unique in the date string ...
'
'Example expected string format:
' mm/dd/yyyy hh:nn:ss.tt
    
    Dim strTemp As String
    
    'Lop of the ".tt".  Note that Split() can not accept a Null as a passed
    'parameter, thus the concatenation to varVariant.  Also, since my expr
    'directly references an element returned by Split(), Split() has to have
    'at least ONE character to ensure the reference element exists.
    strTemp = Split(varVariant & " ", strSubDelimiter, , vbTextCompare)(0)
    If IsDate(strTemp) Then
        fTrimSubSeconds = CDate(strTemp)
    Else
        fTrimSubSeconds = Null
    End If
        
End Function

{Note: Air Code ... so take that however you with! }

 

[Mutdogus]

My dates were 1/1/2006 11:10:33:255 and 12/12/2007 12:11:34:255 so I had to account for a whole bunch of possiblities and basically it had to be 2 char to the right of the 4th special character. And also I had a normal date in there sometimes as well, 1/1/2006 11:10:33 and 12/25/2007 08:30:25.

 

[datAdrenaline]

I figured as much since code like that is often born out of need! ... ok ... since we're (I) playing a bit ... here's my go of it, again .. It definately more flexible than my first suggestion ... I know your's has suited your needs well, so this is mere for FYI to show other methods for doing stuff ...

Public Function fTrimSubSeconds(varVariant As Variant, _
                                Optional strSubDelimiter As String = ".") As Variant
'Used to trim sub-seconds off of a string that looks like a date
'This function expects input that is a valid date, other than the
'sub-seconds appendage, plus the delimiter of the subseconds. The
'sub-seconds are expected to be at the end of the full date/time
'passed
'
'Examples of expected string format:
' mm/dd/yyyy hh:nn:ss.tt
' mm/dd/yyyy hh:nn:ss:tt
' mm/dd/yyyy hh:nn:ss tt
    
    Dim aTemp() As String
    Dim strTemp As String
    
    'Lop of the sub seconds if required
    If IsDate(varVariant) Then
        fTrimSubSeconds = CDate(varVariant)
    Else
        'Split the text at the subsecond delimiter
        aTemp = Split(" " & Trim(varVariant), strSubDelimiter, , vbTextCompare)
        
        'Drop the LAST element, which is supposedly the sub seconds
        If UBound(aTemp) > 0 Then ReDim Preserve aTemp(UBound(aTemp) - 1)
        
        'Join the elements back together and return the result
        strTemp = Join(aTemp, strSubDelimiter)
        If IsDate(strTemp) Then
            fTrimSubSeconds = CDate(strTemp)
        Else
            fTrimSubSeconds = Null
        End If
    End If
        
End Function

Testing from the immediate window:
? fTrimSubSeconds("1/1/2006 11:10:33 255"," ")
1/1/2006 11:10:33 AM

? fTrimSubSeconds("12/12/2007 12:11:34:255",":")
12/12/2007 12:11:34 PM

? fTrimSubSeconds("1/1/2006 11:10:33",":")
1/1/2006 11:10:33 AM

? fTrimSubSeconds("1/1/2006 11:10:33:255",":")
1/1/2006 11:10:33 AM

? fTrimSubSeconds("1/1/2006 11:10:33.255")
1/1/2006 11:10:33 AM

....

 

[Mutdogus]

I like it much better than mine, faster and more elegant. Less code.

I only learned about Split() a year ago and as you can tell I wrote this one in 2005.

Thanks.

 

[datAdrenaline]

Thanks for the compliment! ... I love the Split() and Join() functions they are one of the best additions that came in with the VB6 engine (A2000) ...
...
As an aside, I have SO many User Defined Functions that I have carried over from MANY YEARS gone by ... I am too scared to 'optimize' some of them because they work!! ... but most definately they could be improved upon! ... ...