semicolon separator in Excel 2002 (xp)

Topic

New Reply

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

Reply | Quote

Replies

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
[Fmr MS MVP - Word]

Reply | Quote

If you do a find and replace commas with semicolons, any text values with commas will be munged. Guess how I know?

Reply | Quote

Let me guess … how about ‘been there done that”

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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 that you now have to use the semi colon as the separator in your formula’s as well….

Reply | Quote

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?

Reply | Quote

[indent]

---

So I’m curious about what software wants these semi-colon delimited files. Is this a trend?

---

[/indent]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.
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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 =

OK if is Jones:
select * where L_NAME = ‘Jones’

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

Alan

Reply | Quote

Alan,

Export from Excel to .csv depends – on my PC – on the list separator set in Windows:

• Cells are separated by the Windows list separator (comma for e.g. US setting, semicolon for e.g. Dutch setting)
• 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)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote