用逗号分隔多个逗号分隔的csv文件
我试图在访问中导入逗号分隔的csv文件。我遇到的问题是其中一列“金额”在数据本身中有逗号,例如“1,433.36”。这些数据中总会有逗号。用逗号分隔多个逗号分隔的csv文件
我怎样才能导入成功?
样本数据:
sjonn,one,"1,855.9"
ptele,two,344.0
jrudd,one,334.8
在此先感谢
将该文件另存为制表符分隔的文本文件并导入。
我无法控制我收到的文件 – user793468 2012-02-24 19:40:28
您可以在Excel中打开它并将其保存为其他格式 – Paul 2012-02-24 19:56:47
我正在设计一个用户界面,使用户可以选择我正在说的文件,然后在Access Table中显示它,是的,我可以打开它并以不同的格式重新保存它,但是我将遇到所有其他复杂问题,例如:将用户的计算机保存在哪里,用户是否有一个特定的文件夹,用户是否有权访问该特定的文件夹等。因此,寻找一个不同的解决方案 – user793468 2012-02-24 20:19:46
使用输入读取文件处理的报价为您
Dim f1 As String
Dim f2 As String
Dim f3 As String
Open "d:\test.txt" For Input As #1
Input #1, f1, f2, f3
Debug.Print f1, f2, f3
Input #1, f1, f2, f3
Debug.Print f1, f2, f3
Close #1 '
给
sjonn one 1,855.9
ptele two 344.0
如果DoCmd.TransferText
不为你工作,那么你可以定义一个方法,这样做“手动” :
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set objFile = fs.GetFile("import.txt")
Set objFileTextStream = objFile.OpenAsTextStream(1, 2)
objFileTextStream.skipLine 'if the file contains the header
Do While objFileTextStream.AtEndOfStream <> True
strLine = objFileTextStream.ReadLine 'read a line
strLinePart = split(strLine,",") 'Split the line using the , delimiter
firstField = strLinePart(0)
secondField = strLinePart(1)
thirdField = strLinePart(2)
strSQL = "INSERT INTO myTable Values('"& firstField &"','"& secondField &"','"& thirdField &"')"
conn.Execute strSQL
Loop
objFileTextStream.Close: Set objFileTextStream = Nothing
Set fs = Nothing
conn.Close: Set conn = Nothing
我曾经遇到过这个问题,这是另一种方法,也许会有帮助,但是它的分割线本身,即你必须使用这种方法 其还假定它包含在一个命名为模块
''Perfoms a smart split that takes care of the ""
Public Function SmartSplit(Str As String) As Variant
''New collection
Dim Quote As String
Dim Delimiter As String
Dim MyString As String
Dim Sample As String
Dim StrCollection As New Collection
Dim Array_1() As String
Dim HasSeenQuote As Boolean
Dim index As Long
Quote = "" & CStr(Chr(34))
Delimiter = "" & CStr(Chr(44))
HasSeenQuote = False
Array_1 = Split(Str, Delimiter)
For index = LBound(Array_1) To UBound(Array_1)
Sample = Array_1(index)
If Module1.StartsWith(Sample, Quote, False) Then
HasSeenQuote = True
End If
''We append the string
If HasSeenQuote Then
MyString = MyString & "," & Sample
End If
''We add the term
If Module1.EndsWith(Sample, Quote, False) Then
HasSeenQuote = False
MyString = Replace(MyString, Quote, "")
MyString = Module1.TrimStartEndCharacters(MyString, ",", True)
MyString = Module1.TrimStartEndCharacters(MyString, Quote, True)
StrCollection.Add (MyString)
MyString = ""
GoTo LoopNext
End If
''We did not see a quote before
If HasSeenQuote = False Then
Sample = Module1.TrimStartEndCharacters(Sample, ",", True)
Sample = Module1.TrimStartEndCharacters(Sample, Quote, True)
StrCollection.Add (Sample)
End If
LoopNext:
Next index
''Copy the contents of the collection
Dim MyCount As Integer
MyCount = StrCollection.Count
Dim RetArr() As String
ReDim RetArr(0 To MyCount - 1) As String
Dim X As Integer
For X = 0 To StrCollection.Count - 1 ''VB Collections start with 1 always
RetArr(X) = StrCollection(X + 1)
Next X
SmartSplit = RetArr
End Function
''Returns true of false if the string starts with a string
Public Function EndsWith(ByVal Str As String, Search As String, IgnoreCase As Boolean) As Boolean
EndsWith = False
Dim X As Integer
X = Len(Search)
If IgnoreCase Then
Str = UCase(Str)
Search = UCase(Search)
End If
If Len(Search) <= Len(Str) Then
EndsWith = StrComp(Right(Str, X), Search, vbBinaryCompare) = 0
End If
End Function
''Trims start and end characters
Public Function TrimStartEndCharacters(ByVal Str As String, ByVal Search As String, ByVal IgnoreCase As Boolean) As String
If Module1.StartsWith(Str, Search, IgnoreCase) Then
Str = Right(Str, (Len(Str) - Len(Search)))
End If
If Module1.EndsWith(Str, Search, IgnoreCase) Then
Str = Left(Str, (Len(Str) - Len(Search)))
End If
TrimStartEndCharacters = Str
End Function
''Returns true of false if the string starts with a string
Public Function StartsWith(ByVal Str As String, Search As String, IgnoreCase As Boolean) As Boolean
StartsWith = False
Dim X As Integer
X = Len(Search)
If IgnoreCase Then
Str = UCase(Str)
Search = UCase(Search)
End If
If Len(Search) <= Len(Str) Then
StartsWith = StrComp(Left(Str, X), Search, vbBinaryCompare) = 0
End If
End Function
@HansUp有模块,它是一个扩展之前,首先拆分字符串成线那个问题。我已使用示例数据更新了上述问题 – user793468 2012-02-24 21:41:19
请确认:如果金额字段包含逗号,则该值用双引号括起来。否则,金额值**不会被引号包围。 – HansUp 2012-02-24 21:52:40
@HansUp是的,正确的 – user793468 2012-02-24 22:01:29