Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    semicolon separator in Excel 2002 (xp)

    How can I save a csv file with semicolon separator in Excel 2002?

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: semicolon separator in Excel 2002 (xp)

    That would violate the 'csv' part - comma separated values and you wouldn't then get the correct result next time you open the file in Excel. Since these are plain text files, you could open them in Word and do a search/replace. I'd still be inclined to save them with an extension other than csv, though.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: semicolon separator in Excel 2002 (xp)

    If you do a find and replace commas with semicolons, any text values with commas will be munged. Guess how I know? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: semicolon separator in Excel 2002 (xp)

    Let me guess ... how about 'been there done that"
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: semicolon separator in Excel 2002 (xp)

    If you set your system to use the semi colon as the list separator (Control panel, regional settings) then XL uses that iso the comma.

    You'll experience the great advantage <g> that you now have to use the semi colon as the separator in your formula's as well....
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: semicolon separator in Excel 2002 (xp)

    I had a client recently that demanded a semicolon-delimited "CSV" format for their data AND no quote marks please. Ugh.

    After much wasted time trying to remove all semi-colons from the content prior to export and then writing code to open the text file and search replace commas to semi-colons they finally realized that this just wasn't going to work without wrecking their data.

    Hurrah! We're going to use standard CSV files right? No! They want pipe-delimited now. Still no quote marks. I think the inability of their importing software to handle the text-delimiting quote marks means that they cannot use a field delimiter that could possibly exist in the content itself. Thus, they chose the oddball pipe character: |

    So I'm curious about what software wants these semi-colon delimited files. Is this a trend?
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: semicolon separator in Excel 2002 (xp)

    <hr>So I'm curious about what software wants these semi-colon delimited files. Is this a trend?<hr>
    Don't know about trend, but it is certainly in use here.

    for the record we receive tab delimited data that we need to change to pipe delimited. this is done by a mixture of Jan Karel and DoryO's methods:

    a) change list seperator in regional settings to pipe.
    [img]/forums/images/smilies/cool.gif[/img] open submitted data.txt (tab delimited) file in excel.
    c) enter a pipe in column CL (the system it's going to will reject it unless there are at least 90 columns - can't cope with adding them on itself)
    d) save as csv
    e) open in word, find and replace quotes with nothing and save as a .txt

    As to what software/system, the initial stage is an oracle database that was designed in house, but the chap who wrote it has long been gone, and no-one knows the answer to the question, "why pipe delimited?" To cap it all, when the data leaves that database for the next one (also oracle) it goes as tab delimited.

    Whilst we can understand the pipe if you're going to use character delimited - it's just the least likely to show up anywhere in the data - what we don't understand is why character and not tab. Our best guess is that it's a hangup from an older system that the programmer used in his youth where that was the way things were.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: semicolon separator in Excel 2002 (xp)

    I don't think that it would be extremely difficult to do this with VBA. It you would upload a sample workbook that shows what your data looks like and a description of what you want to export, I'd be happy to take a look.
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: semicolon separator in Excel 2002 (xp)

    Thank you for all of you for your help.

    Finally I opted for the change in the regional settings and it worked very well for my application. The software requesting this kind of file is a EDP system working with Mapics.

    Again thank you for your assistance.

    Marcel.

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: semicolon separator in Excel 2002 (xp)

    I can hazard a fairly good guess as to the choice of this format. If the exported file ends up in a database, the single quote character is a "special" character in SQL queries, and its inclusion in tables can create merry hell. I know this one from experience, with names like O'Hara -> runtime error :-(

    The choice of the pipe character rather than a tab means that the end-of-cell is clearly visible in a text editor, and errors are much less likely when making changes in same. Tabs are invisible, and will vary in screen whitespace width. These problems are exacerbated by viewing with wordwrap and in variable width fonts.

    Alan

  11. #11
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: semicolon separator in Excel 2002 (xp)

    >>If the exported file ends up in a database...

    I should hope the exported file would be IMPORTED into a database. In which case, the import process merely USES the quotation marks and field separator (comma or pipe or tab) to tell it how to chop up the records into fields. These characters are never meant to be stored as data in the resulting database.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: semicolon separator in Excel 2002 (xp)-MULTI-Re: sem

    I think you miss my point WRT the single quote characters. They are not used as delimiters, but end up as part of the data - for instance in a last_name table column as O'Hara, O'Brien, O'Grady. If they then appear in this form in generated SQL queries, the syntax of the query is broken, since a quote is actually a string delimiter in the SQL language. I don't think their requirement for "no quotes" had anything to do with their DB import capabilities. They're not using quotes as cell delimiters - they just don't want them messing up their SQL.

    As far as the *cell* delimiter goes, the only reason for selecting a screen-visible character that I can imagine is if they wanted to do something direct within the text file, prior to import to the DB. Otherwise, why bother - just stick to a tab? Their choice of semicolon seems to have been an oversight, its unsuitability discovered in retrospect. But since a comma is fairly common in textual fields, it's easy to guess why they didn't want CSV.

    Alan

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sem-MULTI-Re: sem-MULTI-Re: sem-MULTI-Re: se

    This is just an example to qualify the kind of problems that might arise.

    Spreadsheet: 3 cells
    [Cell] O'Brien [Cell] John T. [Cell] B.Sc, MBS, PhD [Cell] ....

    - Problem with CSV export/import to database: 3 cells -> 5 fields
    [Field] O'Brien [Field] John T. [Field] B.Sc[Field] MBS[Field] PhD [Field]

    - Problem with SQL query: select * where L_NAME = <selection>

    OK if <selection> is Jones:
    select * where L_NAME = 'Jones'

    NOT OK if <selection> is O'Brien:
    select * where L_NAME = 'O'Brien' -> Error in SQL query - Brien not known command, mismatched ' marks.

    Alan

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

    Re: se

    Alan,

    Export from Excel to .csv depends - on my PC - on the list separator set in Windows:
    <UL><LI>Cells are separated by the Windows list separator (comma for e.g. US setting, semicolon for e.g. Dutch setting)
    <LI>Text cells containing the list separator are exported surrounding by double quotes. So if the comma acts as list separator, B.Sc, MBS, PhD is exported as "B.Sc, MBS, PhD". This will be imported to a single field in a database.[/list]The problem with quotes has to be solved in the database, for example:

    strSelection = "O'Brien"
    strSQL = "SELECT * FROM tblImport WHERE L_Name = " & Chr(34) & strSelection & Chr(34)

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: se-MULTI-Re: se

    Hans,

    All quite true, but the "customer" might already have established their own standards for import into their own DB software and are therefore simply expressing same in their requirements for "no quotes" and a pipe separator. Moreover, they may be using their own flavour of SQL that works on the same restrictions (an older version of Oracle maybe). My point was simply that the specifications for the exported spreadsheet text file are entirely the business of the customer, not the supplier of same.

    Alan

Posting Permissions

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