在导入到access数据库之前清理CSV数据

rvpgvaaj  于 2023-05-11  发布在  其他
关注(0)|答案(1)|浏览(146)

是否有一个很好的公式来正确地清理CSV数据,以便正确地将其导入到Access数据库?
长话短说,我确实有一个相当无聊的每月任务,每月分析大约3600+行数据。数据是直接从服务器上提取的,在接收数据之前,我无法控制数据格式。唯一给我带来麻烦的字段是一个混合的日期/时间字段。不幸的是,存储在服务器上的数据包括毫秒,当检索时,它以以下格式(24 h)出现:

M/D/YYYY HH:MM:SS:MSMSMS

这会导致导入到Access数据库时出现Access错误。随后,数据需要以以下格式(24小时)在报告中提供:

DD MMM YYYY HHMM

根据我的理解,最佳的方法是尽早将字符串转换为正确的日期/时间字段。理论上,我可以使用Excel通过字符串操作来清理数据,但如果可能的话,我更喜欢尽量减少使用两个Office程序。我只想把它导入Access,根据需要查询,并使用VBA一次性打印报告。我也很确定一定有比字符串操作更好的方法。
也请让我知道,如果我是完全屁股倒退。我正在学习Access。
感谢您的关注,我感谢任何帮助和投入。

slsn1g29

slsn1g291#

有一个很好的表达式可以只提取日期/时间:

TextDateTime = "6/7/2023 13:45:34:123456"

? Left(TextDateTime, InStrRev(TextDateTime, ":") - 1)
6/7/2023 13:45:34

但是,由于最终格式应该是DD MMM YYYY HHMM,因此您可能不会将US区域设置与反向日期格式一起使用,因此将此字符串转换为CDate通常会返回错误的月份日期:

? Cdate("6/7/2023 13:45:34")
2023-07-06 13:45:34

因此,应用一个函数来正确解析它,就像我的CDateUS一样:

? CdateUS("6/7/2023 13:45:34")
2023-06-07 13:45:34
' Converts a US formatted date/time string to a date value.
'
' Examples:
'   7/6/2016 7:00 PM    -> 2016-07-06 19:00:00
'   7/6 7:00 PM         -> 2018-07-06 19:00:00  ' Current year is 2018.
'   7/6/46 7:00 PM      -> 1946-07-06 19:00:00
'   8/9-1982 9:33       -> 1982-08-09 09:33:00
'   2/29 14:21:56       -> 2039-02-01 14:21:56  ' Month/year.
'   2/39 14:21:56       -> 1939-02-01 14:21:56  ' Month/year.
'   7/6/46 7            -> 1946-07-06 00:00:00  ' Cannot read time.
'   7:32                -> 1899-12-30 07:32:00  ' Time value only.
'   7:32 PM             -> 1899-12-30 19:32:00  ' Time value only.
'   7.32 PM             -> 1899-12-30 19:32:00  ' Time value only.
'   14:21:56            -> 1899-12-30 14:21:56  ' Time value only.
'
' 2018-03-31. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function CDateUs( _
    ByVal Expression As String) _
    As Date
    
    Const PartSeparator As String = " "
    Const DateSeparator As String = "/"
    Const DashSeparator As String = "-"
    Const MaxPartCount  As Integer = 2

    Dim Parts           As Variant
    Dim DateParts       As Variant
    
    Dim DatePart        As Date
    Dim TimePart        As Date
    Dim Result          As Date
    
    ' Split expression into maximum two parts.
    Parts = Split(Expression, PartSeparator, MaxPartCount)
    
    
    If IsDate(Parts(0)) Then
        ' A date or time part is found.
        ' Replace dashes with slashes.
        Parts(0) = Replace(Parts(0), DashSeparator, DateSeparator)
        If InStr(1, Parts(0), DateSeparator) > 1 Then
            ' A date part is found.
            DateParts = Split(Parts(0), DateSeparator)
            If UBound(DateParts) = 2 Then
                ' The date includes year.
                DatePart = DateSerial(DateParts(2), DateParts(0), DateParts(1))
            Else
                If IsDate(CStr(Year(Date)) & DateSeparator & Join(DateParts, DateSeparator)) Then
                    ' Use current year.
                    DatePart = DateSerial(Year(Date), DateParts(0), DateParts(1))
                Else
                    ' Expression contains month/year.
                    DatePart = CDate(Join(DateParts, DateSeparator))
                End If
            End If
            If UBound(Parts) = 1 Then
                If IsDate(Parts(1)) Then
                    ' A time part is found.
                    TimePart = CDate(Parts(1))
                End If
            End If
        Else
            ' A time part it must be.
            ' Concatenate an AM/PM part if present.
            TimePart = CDate(Join(Parts, PartSeparator))
        End If
    End If
    
    Result = DatePart + TimePart
        
    CDateUs = Result

End Function

完整代码和文档在GitHubVBA.Date

相关问题