Free Microsoft Excel 2013 Quick Reference

Excel: save-as csv : delimiter set to semi-colon, not comma. Why?

When I use save-as csv, the delimiter used by Excel is semi-colon and not
comma. Why is this? Is there a setup that I can use to ensure comma as the
delimiter?


Hi,
I was wondering if any one knows of a macro/scrip that i can attach to a button within an excel worksheet to save a CSV file of the sheet to a FTP site?

Button..
1. Save sheet as CSV.
2. Upload to FTP site.

Regards
Craig

Hi,

a French customer has noted that Excel always seems to put a semi colon
as the delimiter
when using the Save As CSV functionality.

If I set up my PC as French Language in Regional Options, then click
Customise
and change the list separator from semi colon to comma, each time I use
Save as CSV in excel the default
French semi colon delimiter is always used instead of the comma. As i
understand it the list separator value
should be used.

Is there anything else I need to change to have the comma delimiter when
in French mode? I have trawled the
web but can't seem to find any answers. Any info would be much
appreciated.

Many thanks,

Jeff

i'm using the following script to save the excel file as tab delimited text.
although the file it produces and the manually saved file seems to be
similar they are slightly different when i compare them by fc.exe even their
sizes equal. i am using office 2002 English, there is an issue reported for
office 2000 in microsoft site but nothing about this one. is it really an
issue or what should i do to get the similar effect for programmatic save?

Dim objXL
Set objXL = WScript.CreateObject("Excel.Application")
'
' open file
'
objXL.Workbooks.Open("C:d.xls")
'objXL.Visible = true

'close workbook AFTER saving
objXL.DisplayAlerts = False 'disable the overwrite? prompt, JUST DO IT
objXL.ActiveWorkbook.SaveAs "C:d.txt", -4158
objXL.Workbooks.Close
objXL.DisplayAlerts = True 'reenable prompts

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

Hi!
When I create a new worksheet and format the cells as "text" so it retains
my input exactly as I input it & save it as a comma delimited csv file, close
it, reopen it, my formatting is not retained.

I went to this web site
http://exceltips.vitalnews.com/Pages...CSV_Files.html

and followed the instructions, but the last instruction said to save as .csv
again, but after that, it still looses the formatting upon reopening the file.

Example:
I input data = 11200000000000000000
I save as .csv & when I reopen it now displays as: 12233E+19
And it doesn't retain any leading zeroes. I even put a single apostrophe in
front of the leading zero, it isn't retained.

I don't ever remember having this problem with older versions of Excel.

Can anyone help?

Thanks!

Excel 2007 Save As CSV - MS-DOS adds extra spaces in the comma-delimited
file. (My List Separator in Regional Settings is simply comma, not
space-comma or so.)

So, I get
MC-001A, 192.00 , 7.00 , 6.60 , 91.00 , V-TOP
instead of
MC-001A,192.00,7.00,6.60,91.00,V-TOP

How do I fix this? Thanks.

I have to open worksheets and then save them as CSV files for another
program. When I used the macro recorder, it copied the entire path so that
when I run macro is puts the file back in the same folder every time
regardless of path for the XLS file. All the file names are VOLUME, but the
path changes for the various options. How do I edit the macro below to get
it to save XLS files as CSV files in the same directory that the XLS file
cam from instead of the one where I initially recorded the macro?

Active..SaveAs Filename:= _
"Y:Yarger Engineering20090802SynchroPhase 1
(2011)SundayAMVOLUME.csv" _
, FileFormat:=xlCSV, CreateBackup:=False

If it makes any difference, I do this all the time, but the path structure
changes from project to project. I would like to automatically open,
update, save and then save as CSV file, but I have no idea how to set up a
macro to loop within an unknown path for future projects. The first part of
the path will always be "Y:Yarger Engineering" followed by the project
number and name, then "synchro", phase or year, maybe the day of week but
normally not since we normally don't worry about weekends, and then the time
of day. I may have an upcoming project where I will have to do this 200
times, so I really don't want to have to do this manually any more. In some
cases, this will be creating the first CSV file and in others it will
overwrite an existing CSV file.

Brad

Excel 2002 on XP Pro SP 3

Hi,
I am trying to edit a csv file in excel,
but after opening a csv file in excel and saving it as a new csv file (without editing),
certain spaces disappear.
for example:

The original CSV file contains:
20, 201, , , , , , 120," ,0, 1,10

After save as csv in Excel:
20,201, , , , , ,120,""" ",0,1,10

In the above example the space before the number 20 doesn't exist,
also the spaces before the number 201..
Please help me with this.
Thanks

I am working on loading a database, and the older data (delimited text) files don't have some of the fields that the newer data has..
so my solution is to use excel to simply add those missing columns in the old data and leave all the values blank. finally save as csv to get back to delimited text (easy enough right?)

this works perfect for new columns that are sandwiched in between actual data, but when i append the new columns to the end of the row, i noticed a problem.

the csv file that excel produces has null values for the first 16 rows (what i want), but then seems to ignore those fields altogether for the rest of the file!

anyone ever ran into this? is there an option that i'm neglecting?

I am having a problem doing the following:

Save a mobile telephone number in a spreadsheet
Cell is formatted as text so the zero appears ie 0423 176 323

Then save as CSV. Close the CSV and then reopen and the 0 disappears.

I have tried using Excel 2000 and Excel 2003 and both doing the same thing.

Does anyone know how I can get the CSV file to retain the leading zero?

Cheers!

Hi!
When I create a new worksheet and format the cells as "text" so it retains
my input exactly as I input it & save it as a comma delimited csv file, close
it, reopen it, my formatting is not retained.

I went to this web site
http://exceltips.vitalnews.com/Pages...CSV_Files.html

and followed the instructions, but the last instruction said to save as .csv
again, but after that, it still looses the formatting upon reopening the file.

Example:
I input data = 11200000000000000000
I save as .csv & when I reopen it now displays as: 12233E+19
And it doesn't retain any leading zeroes. I even put a single apostrophe in
front of the leading zero, it isn't retained.

I don't ever remember having this problem with older versions of Excel.

Can anyone help?

Thanks!

Hi,

I'm trying to create a excel sheet with macros that will generate a tab delimitd file which is a journal voucher and I want to upload that .txt file to our financial system (SAP). When I create a tab delimited file manually, that is I enter all the values in excel and save as .txt it works great. But I have created a spreadsheet where the useres (all employees on the financial departement) enter the info required. I have a macro validating that all fields are correct and so on.. When they are finished they sedt the spreadsheet to me (an excelfile) via email, this is done by a submit buttom(macro) and the I have a hidden sheet in the worksheet which gets all the values that are necessery and then I create my tab delimited file using the well known save as fileformat=xltext macro. But SAP will not accept this file. I get error messages as convertion erros in line 4 column 6 and so on. The sheet which is saved as .txt is 100% with formulas, it gets all the value from another sheet(sheet1). I use the formula =IF(Sheet1!$E17=0;"";Sheet1!E17) in the cells with E17 changing to whatever cell is required. What can be wrong here? May it be that all the formulas (columns A to H and row 2 to 1001) somehow mix it all up and even to the cell is empty is the formula is somehow hidden in the .txt file and that messes it all up?? Please help me. If my explanation is diffult to understand please let me know and I'll try ro explain.

Thanks

Hi Guys, I need to save an Excel Document as a CSV file. It is used to import into a particular apllication. The problem is that I need to have cell A showing 00 (format = text), and cell b showing 06/06/2008 (format = text).
This is fine in Excel, but when I save as CSV file it always formats Cell A to General and shows only one zero, and converts cell b to date format. When I do this in Excel97 it works fine, but in excel 2003 this doens't do what I want.

Thanks

The weirdest thing seems to be happening in my worksheet I have a field that is the last of my header row and it is unpopulated most of the time with the exception of my header. I am attempting to save this worksheet as tab delimited text to import the data in an application that was written. However when I save as txt the tab for the last field disappears after 16 records everytime?? I need to have all thirteen fields recognized for my import process to work and sometimes the 13th maybe populated?? anyone have any ideas as to why excel would choose not to recognize this blank column after 16 records everytime and what I could do to fix this?

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

I am saving a spreadsheet where the date format was:
mm/dd/yyyy
I needed to be YYYY-MM-DD
I did that using format/custom YYYY-MM-DD.
The problem is when I save as CSV ,close, open again, the format shows:
mm/dd/yyyy
I used Editplus, didn't save the comma delimited.
Please help

Hi!

When saving an Excel worksheet as tab-delimited text, cells containing
double-quote (") or comma (,) are surrounded by double-quotes in the
resulting text file. I would like to be able to induce or control the
quoting of cell values in tab-delimited text output. Specifically, I would
like the content of cells containing space ( ) to be quoted in the text
output.

Is this possible?

Thanks,
Brynturk

I believe this is a bug in Excel 2003. I get an error when saving as
CSV with VBA password protection turned on. If I turn off password
protection, the same code runs without error.

To reproduce the problem...

1. Open a new Excel file.
2. Type "Test" in Sheet1 cell A1
3. using Excel's Control Toolbox toolbar, draw a button on the
worksheet
4. right click on the button and select "view code"
5. enter the following line of code in the button click event (be sure
to enter a path that exists on your system -- I used "c:temp" which
did exist on my system):
ThisWorkbook.SaveAs "c:temptest.csv", xlCSVWindows
6. save your Excel file
7. click the button and observe that the VBA code runs without error
8. close the CSV file that you saved in step 7
9. re-open your Excel file that you saved in step 6
10. open the Visual Basic editor and select ToolsVBAProject Properties
from the menu
11. click the Protection tab, check the "Lock project for viewing"
checkbox, enter a password and click OK (this protects your VBA code).
12. close the Excel file
13. re-open the Excel file
14. click on the button you added in step 3 and notice the error.
"Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed
15. open the Visual Basic editor and attempt to view your code
16. when prompted for your password, enter it
17. click on the button you added in step 3 notice now the code runs
without error.

So it's clear that the same code runs fine when VBA password protection
is turned on or the password has been entered, or produces an error
when VBA password protection is turned off or the password has been
entered.

Any ideas how to correct this issue or work around it?

Thanks!

Hi!

When saving an Excel worksheet as tab-delimited text, cells containing
double-quote (") or comma (,) are surrounded by double-quotes in the
resulting text file. I would like to be able to induce or control the
quoting of cell values in tab-delimited text output. Specifically, I would
like the content of cells containing space ( ) to be quoted in the text
output.

Is this possible?

Thanks,
Brynturk

Hi

I'm trying to write some script which will save a worksheet as a csv file. I can get it to create the csv, but the system requires that any text formatted values be enclosed by "".

So I wrote a little script which adds the "" around text values prior to saving as csv. To my bewilderment, the result was text surrounded by 3 sets of "".

So TEXT should be "TEXT" but came out """TEXT"""!

I've attached the original xls spreadsheet (Example Feeder.xls), the csv file produced by the main code below (Example Feeder.txt) and the csv prodcued by adding the code at bottom, which wraps all text values in "" prior to saving (Example Feeder (with quotes).txt).

Does anybody have any ideas what I'm doing wrong?

Dion

'Record batch name and location
varFeederDestinationXLS = varFeederDirectory & varBatchName & ".xls"
varFeederDestinationCSV = varFeederDirectory & varBatchName & ".1"

'Create file header
WSfeeder.Cells(1, 1).NumberFormat = "@"
WSfeeder.Cells(1, 1).Value = Format("FH", "@")
WSfeeder.Cells(1, 2).NumberFormat = "@"
WSfeeder.Cells(1, 2).Value = Format("READY TO PAY", "@")
WSfeeder.Cells(1, 3).NumberFormat = "@"
WSfeeder.Cells(1, 3).Value = "RTP" & Format(Date, "ddmmyy;@")
WSfeeder.Cells(1, 4).NumberFormat = "@"
WSfeeder.Cells(1, 4).Value = Format(Date, "ddmmyy;@") & "RWKN"

'Calculate batch total()
For varSourceRow = 2 To varLastRow
    If WSinvoice.Cells(varSourceRow, 11).Value = "" Then
        varBatchTotal = varBatchTotal + WSinvoice.Cells(varSourceRow, 8).Value
    End If
Next varSourceRow
    
'Copy invoices to tmpFeeder sheet
varTargetRow = 2
varInvoiceCountNo = 1
For varSourceRow = 2 To varLastRow
    If WSinvoice.Cells(varSourceRow, 11).Value = "" Then
        'Invoice header
        WSfeeder.Cells(varTargetRow, 1).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 1).Value = Format("IH", "@")
        WSfeeder.Cells(varTargetRow, 2).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 2).Value = Format("READY TO PAY", "@")
        WSfeeder.Cells(varTargetRow, 3).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 3).Value = Format("GBP", "@")
        WSfeeder.Cells(varTargetRow, 4).NumberFormat = "0;-0"
        WSfeeder.Cells(varTargetRow, 4).Value = Format(varInvoiceCountNo, "0;-0")
        WSfeeder.Cells(varTargetRow, 5).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 5).Value = Format(WSinvoice.Cells(varSourceRow, 7).Value, "@")
        WSfeeder.Cells(varTargetRow, 6).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 6).Value = Format("STANDARD", "@")
        WSfeeder.Cells(varTargetRow, 7).NumberFormat = "dd-mmm-yy"
        WSfeeder.Cells(varTargetRow, 7).Value = Format(Date, "dd-mmm-yy")
        WSfeeder.Cells(varTargetRow, 8).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 8).Value = Format(WSinvoice.Cells(varSourceRow, 4).Value, "@")
        WSfeeder.Cells(varTargetRow, 9).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 9).Value = Format(WSinvoice.Cells(varSourceRow, 3).Value, "@")
        WSfeeder.Cells(varTargetRow, 10).NumberFormat = "0.00;-0.00"
        WSfeeder.Cells(varTargetRow, 10).Value = Format(WSinvoice.Cells(varSourceRow, 8).Value, "0.00;-0.00")
        WSfeeder.Cells(varTargetRow, 11).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 11).Value = Format("30 DAYS NET", "@")
        WSfeeder.Cells(varTargetRow, 12).NumberFormat = "dd-mmm-yy"
        WSfeeder.Cells(varTargetRow, 12).Value = Format(Date, "dd-mmm-yy")
        WSfeeder.Cells(varTargetRow, 13).NumberFormat = "0.00;-0.00"
        WSfeeder.Cells(varTargetRow, 13).Value = Format("0", "0.00;-0.00")
        
        varTargetRow = varTargetRow + 1
        
        'Invoice line
        WSfeeder.Cells(varTargetRow, 1).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 1).Value = Format("TL", "@")
        WSfeeder.Cells(varTargetRow, 2).NumberFormat = "0;-0"
        WSfeeder.Cells(varTargetRow, 2).Value = Format(varInvoiceCountNo, "0;-0")
        WSfeeder.Cells(varTargetRow, 3).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 3).Value = Format(Left$(WSinvoice.Cells(varSourceRow, 5).Value, 6), "@")
        WSfeeder.Cells(varTargetRow, 4).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 4).Value = Format(Left$(WSinvoice.Cells(varSourceRow, 6).Value, 4), "@")
        WSfeeder.Cells(varTargetRow, 5).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 5).Value = Format("00000", "@")
        WSfeeder.Cells(varTargetRow, 6).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 6).Value = Format("00000", "@")
        WSfeeder.Cells(varTargetRow, 7).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 7).Value = Format("000000", "@")
        WSfeeder.Cells(varTargetRow, 8).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 8).Value = Format(WSinvoice.Cells(varSourceRow, 4).Value, "@")
        WSfeeder.Cells(varTargetRow, 9).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 9).Value = Format("ITEM", "@")
        WSfeeder.Cells(varTargetRow, 10).NumberFormat = "0.00;-0.00"
        WSfeeder.Cells(varTargetRow, 10).Value = Format(WSinvoice.Cells(varSourceRow, 8).Value, "0.00;-0.00")
        WSfeeder.Cells(varTargetRow, 11).NumberFormat = "0.00;-0.00"
        WSfeeder.Cells(varTargetRow, 11).Value = Format(WSinvoice.Cells(varSourceRow, 8).Value, "0.00;-0.00")
        WSfeeder.Cells(varTargetRow, 12).NumberFormat = "@"
        WSfeeder.Cells(varTargetRow, 12).Value = Format("EXEMPT", "@")
        WSfeeder.Cells(varTargetRow, 13).NumberFormat = "0;-0"
        WSfeeder.Cells(varTargetRow, 13).Value = Format("1", "0;-0")
        
        varTargetRow = varTargetRow + 1
        varInvoiceCountNo = varInvoiceCountNo + 1
    End If
Next varSourceRow
        
'Add footer info
WSfeeder.Cells(varTargetRow, 1).NumberFormat = "@"
WSfeeder.Cells(varTargetRow, 1).Value = "IF"
WSfeeder.Cells(varTargetRow, 2).NumberFormat = "0.00;-0.00"
WSfeeder.Cells(varTargetRow, 2).Value = Format(varBatchTotal, "0.00;-0.00")
WSfeeder.Cells(varTargetRow, 3).NumberFormat = "0;-0"
WSfeeder.Cells(varTargetRow, 3).Value = Format(varInvoiceCountNo - 1, "0;-0")

'Add quotes to values
Call AddQuotesToValues

'Create feeder files
Application.DisplayAlerts = False
Sheets("tmpFeeder").Copy
ActiveWorkbook.SaveAs Filename:=varFeederDestinationCSV, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True

The code to add "" is as follows:

Sub AddQuotesToValues()
    Dim rngData As Range, rngCell As Range, varLastFeederRow As Integer
     
    varLastFeederRow = Sheets("tmpFeeder").Cells(5000, 1).End(xlUp).Row

    Set rngData = Sheet5.Range(Cells(1, 1), Cells(varLastFeederRow, 13)).SpecialCells(xlCellTypeConstants, xlTextValues)
    For Each rngCell In rngData
        rngCell.Value = """" & rngCell.Value & """"
    Next rngCell
End Sub


Can someone please help me modify this code to save as .csv? I tried just putting it after the file name and that didn't work. thanks!

Dim strpath As String, strFileName As String
strpath = "W:BISYS"
strFileName = "PacCap_ChicagoEquity_Trades_" & Format(Now(), "MMDDYYYY")
ActiveWorkbook.SaveAs Filename:= _
strpath & strFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


I have an excel file with many sheets in it. I need each sheet saved as a .csv file. Is there a quick way to save all sheets as .csv without having to go through each one?

I believe this is a bug in Excel 2003. I get an error when saving as
CSV with VBA password protection turned on. If I turn off password
protection, the same code runs without error.

To reproduce the problem...

1. Open a new Excel file.
2. Type "Test" in Sheet1 cell A1
3. using Excel's Control Toolbox toolbar, draw a button on the
worksheet
4. right click on the button and select "view code"
5. enter the following line of code in the button click event (be sure
to enter a path that exists on your system -- I used "c:temp" which
did exist on my system):
ThisWorkbook.SaveAs "c:temptest.csv", xlCSVWindows
6. save your Excel file
7. click the button and observe that the VBA code runs without error
8. close the CSV file that you saved in step 7
9. re-open your Excel file that you saved in step 6
10. open the Visual Basic editor and select ToolsVBAProject Properties
from the menu
11. click the Protection tab, check the "Lock project for viewing"
checkbox, enter a password and click OK (this protects your VBA code).
12. close the Excel file
13. re-open the Excel file
14. click on the button you added in step 3 and notice the error.
"Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed
15. open the Visual Basic editor and attempt to view your code
16. when prompted for your password, enter it
17. click on the button you added in step 3 notice now the code runs
without error.

So it's clear that the same code runs fine when VBA password protection
is turned on or the password has been entered, or produces an error
when VBA password protection is turned off or the password has been
entered.

Any ideas how to correct this issue or work around it?

Thanks!

When I save a spreadsheet as CSV, and some of the fields at the end of
the row are empty (ie row 1 has 10 fields, and subsequent rows have <10
fields), some rows have consecutive comma's at the end of the row for the
empty fields, and some do not. Anyone see/hear of this behavior? I'm using
Excel 2002 SP3.

To reproduce the problem, try entering 1 - 5 in cells A1 - E1, then
enter 1 & 2 in columns A & B for the next 20 rows. Save as CSV, and open in
notepad, rows 2 - 16 have three comma's at the end, & rows starting at 17 do
not. The problem always starts at row 17 for me.