Save Excel File as SemiColon Delimited file

Question

I have ran into an issue where I need to save some Excel data to a semicolon delimited file. (Why is an issue unto itself) And I can not find a way to do this using VB. In excel I can use Import Semicolon delimited files but I can not find a way to save them.

Is there a VB constant or method I can use to save semicolon delimited files without having to write my own save procedure to format the data? such as the xlCSV, xlDIF, xlTextMSDos formats. I would think that since it can inport this format that it should be able to save it.

Answer 1

Hi,

One way around this is to save the Excel sheet as a CSV file.

 If you have comma's in your Excel worksheet text fields then follow them by a space such as

cell A1 having This is some test text, but with a comma then a space character.

You could then look for this comma-space combination in the CSV text file and ignore it and just replace the other comma's with a semi-colon.

 I came up with this.>>

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'Read from C:/testing.csv

Dim sr As New StreamReader("C:\testing.csv")

'Write output to C:\testing2.csv

Dim sw As New StreamWriter("C:\testing2.csv")

'For the current line.

Dim myLine As String

'For the current character.

Dim currChar As String

'For the current comma state ( is it ",*" or ", ")

Dim ccState As String

While sr.Peek >= 0

myLine = sr.ReadLine

For index As Integer = 0 To myLine.Length - 1

currChar = myLine.Substring(index, 1)

If currChar = "," Then

'Try block needed as myLine.Substring(index,2)

'would otherwise read try to read

'past the end of the current line.

Try

ccState = myLine.Substring(index, 2)

'Check to see if current comma is

'followed NOT followed by a space.

If ccState <> ", " Then

'If it is write a semi-colon.

sw.Write(";")

Else

'Otherwise write the current character.

sw.Write(currChar)

End If

Catch

'If last character in a line is reached then

'write the character.

GoTo writeChar

End Try

Else

'Write each character.

writeChar: sw.Write(currChar)

End If

Next

End While

'Close the streamwriter ( also closes the file written to. )

sw.Close()

'Close the streamreader ( also closes the file read from. )

sr.Close()

End Sub

 

Regards,

S_DS

 

Input file for testing (note the 3rd & 5th lines have a space at the end of it ).>>

Test,string,
Second, instance,
3rd situation,
4th object,,
5th,type,,
Hello there

Output file>>

Test;string,Second, instance,3rd situation, 4th object;,5th;type;, Hello there

 

Answer 2

Roger_Wagner wrote:

I have ran into an issue where I need to save some Excel data to a semicolon delimited file. (Why is an issue unto itself) And I can not find a way to do this using VB. In excel I can use Import Semicolon delimited files but I can not find a way to save them. Is there a VB constant or method I can use to save semicolon delimited files without having to write my own save procedure to format the data? such as the xlCSV, xlDIF, xlTextMSDos formats. I would think that since it can inport this format that it should be able to save it.

Hi

xlCSV is the format you should save the file as.  Semi-colon delimited files tend to be for financial data where a comma is often used as a decimal separator.  If you change your regional settings to use the comma for the dec separator then Excel will save it in semi-colon format.

I don't know if that will work for you, but it is the only way I know you can get excel to do it for you.  Other than that, I suspect you might be coding your own save macro.

Richard

Answer 3

Hi Roger,

your recommending us to do something real funny!

Have you ever looked at the / Control Panel / Reginal Language Settings

1. Tab shows for Swiss, and that IS standard in Swiss, the we use a "." (dot) as a decimal separator.

2. can you imagin what can happen if you late (just for savings) change the "." for "," just to make Excel Work

3. There where times, until recently, where we could work with our macros, which catched the "List Separator" somehow as set by Reginal Settings Table, which IS by default set to a ";" at AXA Winterthur. This setup is used since years. I say: "Until recently" because since a few days we have troubles.

4. It migth be one of this hyper super Configuration Manager Clients issues which hase catched up from a Microsoft System Management Server a PATCH for Excel which makes this no longer behaving as a Month ago.

5. We are curetnly trying to isolate the problem

6. Writing Semicolones into CSV Files is no problem as long as you create a new sheet manualy, enter cell values and then do a SafeAs and select CSV. That makes the List Separator read from where it is stored.

7. Doing the same by macros fails. We have a macro writing into cells then do a saveAs, and it writes only "," as List Separator Characters. Very ugly, because doing the same using the GUI of Excel works as it should; it catches the List Separator. BTW: Why is a List Separator there if not supported by Excel anylonger or in any case.

8. we will do a test next to see if we can do automation (i.e. our VB appss controlling Excel) and if that works that is an indicator thatthere are other ways to overcome this lousy problem.

We keep you up to date but would whish to find out how Excel can be coded to read the List Separator and then setup Excel properly to do the same job as if one would use the GUI.

Josef.Stadelmann

@axa-winterthur.ch

---

Sepp

Answer 4

Hi,
we have the following code in a VB.NET 4.0 environment
wkb8 is a object to the curent open excel workbook.
When I do the code below, the message boxes retun in equenmce a "\", ".", ";", ";", "'" all OK
                    Dim lInst As Long = wkb8.Application.LanguageSettings.LanguageID(msoLanguageIDInstall)       ' Sprache für Installation
                    Dim lIDUI As Long = wkb8.Application.LanguageSettings.LanguageID(msoLanguageIDUI)            ' Sprache für Benutzeroberfläche
                    Dim lHelp As Long = wkb8.Application.LanguageSettings.LanguageID(msoLanguageIDHelp)          ' Sprache für Hilfe
                    Dim lExeM As Long = wkb8.Application.LanguageSettings.LanguageID(msoLanguageIDExeMode)       ' Sprache für Ausführungsmodus
                    Dim lUIPr As Long = wkb8.Application.LanguageSettings.LanguageID(msoLanguageIDUIPrevious)    ' Sprache für previous GUI
                    Dim lCreator As Long = wkb8.Application.LanguageSettings.Creator
    
                    MsgBox("The column   separator is " & wkb8.Application.International(xlColumnSeparator))
                    MsgBox("The decimal  separator is " & wkb8.Application.International(xlDecimalSeparator))
                    MsgBox("The list     separator is " & wkb8.Application.International(xlListSeparator))
                    MsgBox("The row      separator is " & wkb8.Application.International(xlRowSeparator))
                    MsgBox("The thousand separator is " & wkb8.Application.International(xlThousandsSeparator))
                    
why does this code not save the file as ";" separated file but saves it as "," separated file 8which we can't use BTW) and which worked 
until a few days ago when we migth have received a patch via the automatic configuration manager client; Now our apps does not work as
before and we have to trouble shoot.
		  wkb8.SaveAs(Filename:=Me.LocalFileName, FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Local:=True)
                    wkb8.Saved = True
                    System.Windows.Forms.Application.DoEvents()

---

Sepp

Answer 5

Can somone more knowledged the I explain the following

Sub Makro3()
'
' Makro3 Makro
'
    Application.UseSystemSeparators = True
'
'    ' was ist im Moment eingestellt (Status der Sprachen)
'    lInst = Application.LanguageSettings.LanguageID(msoLanguageIDInstall)       ' Sprache für Installation
'    lIDUI = Application.LanguageSettings.LanguageID(msoLanguageIDUI)            ' Sprache für Benutzeroberfläche
'    lHelp = Application.LanguageSettings.LanguageID(msoLanguageIDHelp)          ' Sprache für Hilfe
'    lExeM = Application.LanguageSettings.LanguageID(msoLanguageIDExeMode)       ' Sprache für Ausführungsmodus
'    lUIPr = Application.LanguageSettings.LanguageID(msoLanguageIDUIPrevious)    ' Sprache für previous GUI
'
'    lCreator = Application.LanguageSettings.Creator
''
''    ChDir ("C:\Users\C770817\SW-PROJEKTE\SAMPLES")
''
'    MsgBox "The column   separator is " & Application.International(xlColumnSeparator)
'    MsgBox "The decimal  separator is " & Application.International(xlDecimalSeparator)
'    MsgBox "The list     separator is " & Application.International(xlListSeparator)
'    MsgBox "The row      separator is " & Application.International(xlRowSeparator)
'    MsgBox "The thousand separator is " & Application.International(xlThousandsSeparator)
'    Range("A5").Formula = "1'234'567.89"
'    MsgBox "The system separators will now change."
'    Application.DecimalSeparator = "."
'    Application.ThousandsSeparator = "'"
'    Application.UseSystemSeparators = False
    On Error Resume Next
    Kill ("Mappe2.csv")
    Kill ("Mappe21.csv")
    Kill ("Mappe22.csv")
    Workbooks.Add
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "automatic"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "created"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "12.5"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "6.3"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-2]"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "=RC[-4]*RC[-3]"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]/RC[-4]"
    Range("A1").Select
    
'// Fragment 1 OK
    Dim wkb8 As Workbook
    Set wkb8 = ActiveWorkbook
    MsgBox "The column   separator is " & wkb8.Application.International(xlColumnSeparator)
    MsgBox "The decimal  separator is " & wkb8.Application.International(xlDecimalSeparator)
    MsgBox "The list     separator is " & wkb8.Application.International(xlListSeparator)
    MsgBox "The row      separator is " & wkb8.Application.International(xlRowSeparator)
    MsgBox "The thousand separator is " & wkb8.Application.International(xlThousandsSeparator)
    
    Kill ("C:\Users\C770817\SW-PROJEKTE\SAMPLES\Mappe2.csv")
    wkb8.SaveAs Filename:= _
        "C:\Users\C770817\SW-PROJEKTE\SAMPLES\Mappe2.csv", FileFormat:=xlCSV, _
        CreateBackup:=False, Local:=True
        
    wkb8.SaveAs Filename:= _
        "C:\Users\C770817\SW-PROJEKTE\SAMPLES\Mappe2.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False, Local:=True
    
'// Fragment 2 BAD - we get "," as ListSeparators written but Msgbox shows the same
    Dim wks8 As Worksheet
    Set wks8 = ActiveSheet
    MsgBox "The column   separator is " & wks8.Application.International(xlColumnSeparator)
    MsgBox "The decimal  separator is " & wks8.Application.International(xlDecimalSeparator)
    MsgBox "The list     separator is " & wks8.Application.International(xlListSeparator)
    MsgBox "The row      separator is " & wks8.Application.International(xlRowSeparator)
    MsgBox "The thousand separator is " & wks8.Application.International(xlThousandsSeparator)
    
    Kill ("C:\Users\C770817\SW-PROJEKTE\SAMPLES\Mappe21.csv")
    wks8.SaveAs Filename:= _
        "C:\Users\C770817\SW-PROJEKTE\SAMPLES\Mappe21.csv", FileFormat:=xlCSV, _
        CreateBackup:=False, Local:=True
    
'// Fragment 3 BAD - we get "," as ListSeparators written but MsgBoxe shows the same
    Set wks8 = wkb8.ActiveSheet
    MsgBox "The column   separator is " & wks8.Application.International(xlColumnSeparator)
    MsgBox "The decimal  separator is " & wks8.Application.International(xlDecimalSeparator)
    MsgBox "The list     separator is " & wks8.Application.International(xlListSeparator)
    MsgBox "The row      separator is " & wks8.Application.International(xlRowSeparator)
    MsgBox "The thousand separator is " & wks8.Application.International(xlThousandsSeparator)
    
    Kill ("C:\Users\C770817\SW-PROJEKTE\SAMPLES\Mappe22.csv")
    wks8.SaveAs Filename:= _
        "C:\Users\C770817\SW-PROJEKTE\SAMPLES\Mappe22.csv", FileFormat:=xlCSV, _
        CreateBackup:=False, Local:=True
        
End Sub

just painfull.
Thanks and regards for any helpfull explanation

 

---

Sepp

Answer 6

All our problems with bad list separators, ("," instead of ";") in CSV files, written by EXCEL workbook.Application.SaveAs(...) are gone since we down graded our Spezpla Client and all underlaying DLL's from .NET 4.0 to .NET 3.5;

This client (VB.NET) (DLL's C#.NET) interoperates via OLE Automation or how ever you call it today with EXCEL.

I call this a BUG in .NET 4.0

It works as it should if you explicetly use only .NET 3.5 in your client code.

However the problem in the Excel Macros 8as explained in a previous reply) remains

If you call

aWorkbook.Application.SaveAs(... Local:=True) the CSV Files has the ";" as requested by the reginal settings ListSeparator

aWorkSheet.Application.SAveAs(... Local:=True) the CSV Files have wrongly "," but not the requested reginal ListSeparators

I call this a Bug in EXCEL

---

Sepp

Answer 7

For me, I 've reported a anomaly in .-1
As I said, when we down grade to .NET 3.5 all is fine.
But when we use .NET 4.0 for our projects,
integrated with EXCEL and WORD,
EXCEL writes "," instead of ";" into .csv files , if we do

aWorkSheet.Application.SaveAs(... Local:=True ...)

but does not as defined in the Local Region Map.

EXCEL works correct/uses/writes the charater given in the Local Region Map if we do

aWorkBook.Application.SaveAs(... Local:=True ...)

What bothers me is that no one at MS seems to take te time and fix this bug. We are in a situation that we can no longer stay with .NET 3.5 just for that bug, but have to upgrade our solutions/projects to use .NET 4.0, mainly for DLL's becoming local web service proxies. So we have a lot of work load just because someone has induced a little bug and is now unable or unwilling to fix it.

---

Sepp