Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CSV creation (2003)

    Hi,

    I wish to create CSV files programmatically from data in text fields which may contain commas and quotes. Does anybody have a function they could share with me for this purpose?

    e.g., assuming two fields,

    strCSV = strCSV & ConvertToCSV(rs.MyField(1)) & ","
    strCSV = strCSV & ConvertToCSV(rs.MyField(2))"

    I have functions for writing strCSV to a text file.

    The reason for this request is that I am wanting to export an Access database of words and clues into a format which can be read by CrossWord Compiler.

    Thanks for any help.

    Jim.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CSV creation (2003)

    What would you like the function to do? Please try to be specific.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV creation (2003)

    Sorry, I'd tried to be specific :-)

    I have looked in CSV files created by Excel and find that the rules go something like this: If a field doesn't contain a double quote or a comma just put it between commas. If on the other hand it does contain a double quote or comma then also surround it with double quotes and, furthermore, replace a single double quote with a double double quote (ouch - this makes sense to me anyway!)

    The difficulty lies mainly in manipulating the string which is going to be written to the text file because the double quote is used as a delimiter. I know about the use of """" to search for a single " but thereafter my initial attempts to get further were getting messy.

    However I have since come up with what I think is a good idea. I'm going to replace any instances of a single quote with another unique string such as "@#$%" before the work begins and remove these just before writing to the text file. This should work and remove any problems trying to keep track of the right number of double quotes.

    It would take me more time to write about this at present than resolve it I think. I just had wondered if somebody had already faced and resolved this problem. On second thoughts it may not even be possible in the way I had originally envisaged. The optimum function would be one which would take a parameter array where each parameter was a field from the dataset and return a single correct string which could then be written to one line in a csv file.

    However, please don't spend time on it as I'll find a way through before too long and either post the result here or delete my original query.

    Many thanks for your interest.

    Regards,

    Jim.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CSV creation (2003)

    Please don't delete your original question, it would make the thread incomprehensible.

    You could use the Replace function to replace Chr(34) (the code for ") with Chr(34) & Chr(34), and ALWAYS surround the value with quotes, e.g.

    Function FixQuotes(strText As String) As String
    FixQuotes = Chr(34) & Replace(strText, Chr(34), Chr(34) & Chr(34)) & Chr(34)
    End Function

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Auckland, North Island, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV creation (2003)

    Thank you so much for this suggestion. The function below, which incorporates your suggestion, does just what I want.

    Public Function MakeCSV(ParamArray strData()) As String
    Dim intUbound As Integer
    Dim intCount As Integer
    intUbound = UBound(strData)
    For intCount = 0 To intUbound
    MakeCSV = MakeCSV & Chr(34) & Replace(strData(intCount), Chr(34), Chr(34) & Chr(34)) & Chr(34)
    If Not intCount = intUbound Then
    MakeCSV = MakeCSV & ","
    End If
    Next
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •