Results 1 to 15 of 15

Thread: [RESOLVED] comma value in csv file

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Resolved [RESOLVED] comma value in csv file

    Big problem...
    i need to save a recordset value in a csv with comma delimited.
    but....

    in one or more value, have a comma in string!!!!
    similar:

    ...,price_title,...
    ...,145,55,....

    in this case is a price with a decimal number

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: comma value in csv file

    This looks like numeric data. When comma is decimal point, normally "CSV" data is written using semicolons as field delimiters.

    The alternative is to use periods as decimal points to conform to "Earth standard" interchangeable data.

    One way to do that is to use the legacy Str$() function which is locale-blind by accident (always assumes U.S. English conventions, almost identical to invariant-locale formatting). Cheap and easy in VB6. Of course you have to deal with that when reading the data later. For that you can use the legacy Val() function.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: comma value in csv file

    Export each field with quotation marks
    Downside: Every Field in the CSV is a String

    EDIT: Easy solution: Don't use comma as field-delimiter. In such cases i use the pipe-symbol |
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: comma value in csv file

    For text based data files I always use a TAB as the delimiter.

  5. #5
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: comma value in csv file

    Datalist:
    ID STR PRICE
    1 ABC,11 12,345
    2 S2 17
    3 S3 25

    CSVFILE:
    ID,STR,PRICE
    1 ,"ABC,11","12,345"
    2 ,S2,17
    3 ,S3,25

    TAB SPLIT:
    ID STR PRICE
    1 "ABC,11" "12,345"
    2 S2 17
    3 S3 25

  6. #6
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: comma value in csv file

    You can use ado to read csv file ,insert or edit,delete

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: comma value in csv file

    I generally use Tab delimiters as well.

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: comma value in csv file

    Tab makes the MOST sense when working with text data.
    Sam I am (as well as Confused at times).

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: comma value in csv file

    Quote Originally Posted by SamOscarBrown View Post
    Tab makes the MOST sense when working with text data.
    Not really. At least in my opinion.
    reason: TAB is a non-printable character, and there is always a smart-ass trying to edit such files in a Texteditor, blowing the file to smitherens, just because he can‘t see the delimiter
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: comma value in csv file

    Quote Originally Posted by xiaoyao View Post
    You can use ado to read csv file ,insert or edit,delete
    That's all fine and dandy (and quite possibly dandy and fine) but it still doesn't address the problem at hand, which is how to deal with CSV data when the data also has a comma in it somewhere.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    Addicted Member
    Join Date
    May 2012
    Location
    42.787034,-81.176367
    Posts
    130

    Re: comma value in csv file

    In English, the decimal point is a period or full stop, which is called un point in French.

    But in French, the decimal symbol is a comma: une virgule.

    Joe

  12. #12
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: comma value in csv file

    What format is expected by the program which reads this data back in?

    If you control both programs (writer and reader) then you have a great deal of flexibility. For example ADO Recordset data can be saved in XML format and later opened. See:

    Save Method

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: comma value in csv file

    if you want to keep .csv then enclose fields that could contain commas in quotes
    if you decide to use some other field separator, then the file is no longer .csv (comma separated values)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Re: comma value in csv file

    Quote Originally Posted by westconn1 View Post
    if you want to keep .csv then enclose fields that could contain commas in quotes
    if you decide to use some other field separator, then the file is no longer .csv (comma separated values)
    nice tips!

  15. #15
    PowerPoster
    Join Date
    Jan 2020
    Posts
    3,746

    Re: comma value in csv file

    Quote Originally Posted by westconn1 View Post
    if you want to keep .csv then enclose fields that could contain commas in quotes
    if you decide to use some other field separator, then the file is no longer .csv (comma separated values)
    Datalist:
    ID STR PRICE
    1 ABC,11 12,345
    2 S2 17
    3 S3 25

    CSV FILE format:
    ID,STR,PRICE
    1 ,"ABC,11","12,345"
    2 ,S2,17
    3 ,S3,25

    TAB SPLIT:
    ID STR PRICE
    1 tab "ABC,11" tab "12,345"
    2 S2 17
    3 S3 25

    this iS the format,you can try,put data in excel,save as csv or split txt file.

Posting Permissions

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



Click Here to Expand Forum to Full Width