Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bavjean
 
Posts: n/a
Default .CSV file format - comma or semi-colon?

Hello,

I have an existing file that is in .csv file format. The comma ( , ) is used
to seperate the values. When I open the file in Excel, it works fine and
displays the values seperately in cells.

Now I am creating my own .csv files through VBA, with the SaveAs method for
Worksheet objects.
E.g. I use the following syntax:

objWS.SaveAs Filename:="c:\temp\" + gstrNameVP + ".csv",
FileFormat:=xlCSVWindows

The file saves as .csv BUT the values are seperated by a semi-colon( ; )
which is not what I want. I want comma-seperated. Otherwise when I open the
..csv in Excel, the values do not display correctly in the cells.
I also changed the list seperator character to a comma in my XP's Regional
settings-Number settings.

I cannot understand how the original file was saved using commas for
seperating the values.
When I create a file in Excel, and save it as .csv, it always uses
semi-colon seperators.


Can somebody please tell me how I can seperate the values by commas in the
..csv properly?
Thanks in advance and for paying attention to my post.

Jean
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Jean,

Try changing your list separator value, which is set by your operating
system.

Use this (though the specific names and tabs, etc. may change with Windows
version - these are for Win XP Prof.)
Start | Settings | Control panel | Regional and Language Options | Regional
Options | Customize.... | Number tab | List Separator - make sure it is a
comma.

HTH,
Bernie
MS Excel MVP

"bavjean" wrote in message
...
Hello,

I have an existing file that is in .csv file format. The comma ( , ) is

used
to seperate the values. When I open the file in Excel, it works fine and
displays the values seperately in cells.

Now I am creating my own .csv files through VBA, with the SaveAs method

for
Worksheet objects.
E.g. I use the following syntax:

objWS.SaveAs Filename:="c:\temp\" + gstrNameVP + ".csv",
FileFormat:=xlCSVWindows

The file saves as .csv BUT the values are seperated by a semi-colon( ; )
which is not what I want. I want comma-seperated. Otherwise when I open

the
.csv in Excel, the values do not display correctly in the cells.
I also changed the list seperator character to a comma in my XP's Regional
settings-Number settings.

I cannot understand how the original file was saved using commas for
seperating the values.
When I create a file in Excel, and save it as .csv, it always uses
semi-colon seperators.


Can somebody please tell me how I can seperate the values by commas in the
.csv properly?
Thanks in advance and for paying attention to my post.

Jean



  #3   Report Post  
bavjean
 
Posts: n/a
Default

Thanks Bernie, but I stated in my message that I already tried that what you
suggested:
I also changed the list seperator character to a comma in my XP's Regional
settings-Number settings.


However, I found article in the MSKB which sort-of told me: NOT POSSIBLE
it is here if you or anyone's interested:

http://support.microsoft.com/default...b;en-us;288839


"Bernie Deitrick" wrote:

Jean,

Try changing your list separator value, which is set by your operating
system.

Use this (though the specific names and tabs, etc. may change with Windows
version - these are for Win XP Prof.)
Start | Settings | Control panel | Regional and Language Options | Regional
Options | Customize.... | Number tab | List Separator - make sure it is a
comma.

HTH,
Bernie
MS Excel MVP

"bavjean" wrote in message
...
Hello,

I have an existing file that is in .csv file format. The comma ( , ) is

used
to seperate the values. When I open the file in Excel, it works fine and
displays the values seperately in cells.

Now I am creating my own .csv files through VBA, with the SaveAs method

for
Worksheet objects.
E.g. I use the following syntax:

objWS.SaveAs Filename:="c:\temp\" + gstrNameVP + ".csv",
FileFormat:=xlCSVWindows

The file saves as .csv BUT the values are seperated by a semi-colon( ; )
which is not what I want. I want comma-seperated. Otherwise when I open

the
.csv in Excel, the values do not display correctly in the cells.
I also changed the list seperator character to a comma in my XP's Regional
settings-Number settings.

I cannot understand how the original file was saved using commas for
seperating the values.
When I create a file in Excel, and save it as .csv, it always uses
semi-colon seperators.


Can somebody please tell me how I can seperate the values by commas in the
.csv properly?
Thanks in advance and for paying attention to my post.

Jean




  #4   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Sorry, I missed that.

In that case, try a macro, example below.

HTH,
Bernie
MS Excel MVP

Sub ExportToCSV()

Dim FName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer

FName = "C:\CSVTest.csv"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

With Range("A1").CurrentRegion
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If WholeLine = "" Then
WholeLine = Cells(RowNdx, ColNdx).Text
Else
WholeLine = WholeLine & "," & Cells(RowNdx, ColNdx).Text
End If
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub

"bavjean" wrote in message
...
Thanks Bernie, but I stated in my message that I already tried that what

you
suggested:
I also changed the list seperator character to a comma in my XP's

Regional
settings-Number settings.


However, I found article in the MSKB which sort-of told me: NOT POSSIBLE
it is here if you or anyone's interested:

http://support.microsoft.com/default...b;en-us;288839


"Bernie Deitrick" wrote:

Jean,

Try changing your list separator value, which is set by your operating
system.

Use this (though the specific names and tabs, etc. may change with

Windows
version - these are for Win XP Prof.)
Start | Settings | Control panel | Regional and Language Options |

Regional
Options | Customize.... | Number tab | List Separator - make sure it is

a
comma.

HTH,
Bernie
MS Excel MVP

"bavjean" wrote in message
...
Hello,

I have an existing file that is in .csv file format. The comma ( , )

is
used
to seperate the values. When I open the file in Excel, it works fine

and
displays the values seperately in cells.

Now I am creating my own .csv files through VBA, with the SaveAs

method
for
Worksheet objects.
E.g. I use the following syntax:

objWS.SaveAs Filename:="c:\temp\" + gstrNameVP + ".csv",
FileFormat:=xlCSVWindows

The file saves as .csv BUT the values are seperated by a semi-colon(

; )
which is not what I want. I want comma-seperated. Otherwise when I

open
the
.csv in Excel, the values do not display correctly in the cells.
I also changed the list seperator character to a comma in my XP's

Regional
settings-Number settings.

I cannot understand how the original file was saved using commas for
seperating the values.
When I create a file in Excel, and save it as .csv, it always uses
semi-colon seperators.


Can somebody please tell me how I can seperate the values by commas in

the
.csv properly?
Thanks in advance and for paying attention to my post.

Jean






  #5   Report Post  
JE McGimpsey
 
Posts: n/a
Default

take a look he


http://www.mcgimpsey.com/excel/textfiles.html


In article ,
"bavjean" wrote:

Thanks Bernie, but I stated in my message that I already tried that what you
suggested:
I also changed the list seperator character to a comma in my XP's Regional
settings-Number settings.


However, I found article in the MSKB which sort-of told me: NOT POSSIBLE
it is here if you or anyone's interested:

http://support.microsoft.com/default...b;en-us;288839



  #6   Report Post  
bavjean
 
Posts: n/a
Default

Thanks for the help guys, I will give it a try!

"JE McGimpsey" schrieb:

take a look he


http://www.mcgimpsey.com/excel/textfiles.html


In article ,
"bavjean" wrote:

Thanks Bernie, but I stated in my message that I already tried that what you
suggested:
I also changed the list seperator character to a comma in my XP's Regional
settings-Number settings.


However, I found article in the MSKB which sort-of told me: NOT POSSIBLE
it is here if you or anyone's interested:

http://support.microsoft.com/default...b;en-us;288839


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
When converting an .xls file to .csv I get too many commas at the. One-eyed-Ian Excel Discussion (Misc queries) 3 December 7th 04 01:03 AM
format question when open csv file Jeff Excel Discussion (Misc queries) 1 December 1st 04 07:53 PM
Openning an .csv file hbarniv Excel Discussion (Misc queries) 4 December 1st 04 01:29 AM
Excel How do I create a comma delineated xls file to a comma delineated. Mark Excel Discussion (Misc queries) 0 November 26th 04 11:28 PM
.CSV file format - comma or semi-colon antifashionpimp Excel Worksheet Functions 1 November 10th 04 02:18 PM


All times are GMT +1. The time now is 12:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"