CODEWITHSUNDEEP
ms-accessms-access-2007
user793468
user793468

Reputation: 4976

importing comma delimited csv file with commas in long numbers

I am trying to import a comma delimted csv file in access. The issue i am having is that one of the columns "Amount" has commas in the data itself e.g. "1,433.36". And there will always be commas in this data.

How can I import is successfully?

Sample Data:

sjonn,one,"1,855.9"
ptele,two,344.0
jrudd,one,334.8

Thanks in advance

Upvotes: 1

Views: 7315

Answers (5)

Mnyikka
Mnyikka

Reputation: 1261

I once encountered the problem and this is another method that might help, it however splits the lines themselves, i.e. you must split the string first into lines before using this method Its also assumed that its contained in a Module named Module1

    ''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

Upvotes: 0

Ould Abba
Ould Abba

Reputation: 843

if the DoCmd.TransferText does not work for you, then you can define a method to do that 'manually' :

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

Upvotes: 1

ChrisPadgham
ChrisPadgham

Reputation: 870

reading the file using input handles the quotes for you

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 '

giving

sjonn         one           1,855.9
ptele         two           344.0

Upvotes: 0

Paul
Paul

Reputation: 2515

Save the file as a tab delimited text file and import that instead.

Upvotes: 0

Lost in Alabama
Lost in Alabama

Reputation: 1653

I would change the delimiter to a different character, like a pipe "|".

Upvotes: 1

Related Questions