Free Microsoft Excel 2013 Quick Reference

Deafult delimiter for csv save/open

Most users that I know have the default delimiter for csv file open/save as a comma (,). However, some users appear to use other symbols, e.g. the semi-colon (. I know how to change this on a file-by-file basis - but how can the default delimiter be changed? Help much appreciated.


Post your answer or comment

comments powered by Disqus
Haven't had occassion to use this, but a colleague passed it on and it looks useful. (Couldn't find it referenced here on Oz.)

If you need Export Excel data to a CSV file but the data have commas in it, you can in Windows change the delimiter used in the export from outside of Excel -- specifally via the Control Panel.

Go to the Control Panel --> Regional and Language Options (or Date, Time and Regional Options --> Regional and Language Option on some machines) --> click the Customize button. In the dialog box that opens you will see and option for "List separator" in which you can change the comma to a different character --> click Apply then Ok and then Apply again. Now when you go into Excel and choose Export as CSV the new character will be used as the delimiter.

i have this macro to save as a csv this macro only save the workbook the you are looking at, i will like for this macro to save as a csv all open workbooks preserving the name of the xls file. Any help is appreciate, thx

Sub SAVE_AS_CSV()
'
Dim strFilename As String

' get existing xls filename
strFilename = ActiveWorkbook.Name

' create new csv filename
strFilename = Left(strFilename, InStr(strFilename, ".")) & "csv"

'save file as csv
ActiveWorkbook.SaveAs filename:=strFilename, FileFormat:=xlCSV

End Sub

Hi,

I wanted to find out are there any differences between csv saved from Excel
2003 and Excel 2007?

Thanks in advance,

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi Friends,
I am using MS Excel to open the Comma Seperated Value files.

For some of the users all the values of CSV are displaying in a single cell.
Is there any setting i can suggest for users...for me the same csv file is opening correctly.

For eg:
if the values in the CSV files are
JAMES,GEORGE,HELLO,HOW,ARE,YOU
These all values are displaying in the column A of the excel, instead of displaying seperately in the Columns A,B,C,D,E

Thanks and Regards,
James George.

How can/do I specify a specific char delimiter for a file when I save it?

I have a document that has 4-5 macros associated with various command buttons. The file is 150Kb and it takes several seconds (10 or more) for me to open and/or close it with or without saving the document. Is there any way for me to reduce the time it takes a file with macros to close? I have other documents that are larger but do not have macros that open/close much quicker.

Thanks in advance,

Scott

I have a *.CSV file that opens/updates information for part measurements.
Current macros have previously closed all but the Personal macro workbook.
When a new part is measured, and it's data sent to the CSV file, I need a
method of automatically running macros when this CSV file opens. The CSV
itself will not store a self-start macro, either due to Excel constraints, or
due to the setup of the sendig program. Can someone help me set up a macro
in the Personal workbook that will begin after this specific CSV opens? I am
using Excel 97.

Thank!
--
CPPoe

I have recently upgraded to Windows XP Home edition and have reinstalled all
my applications. I regularly download csv files from one particular site and
use them in Excel, but now they always download with all data in column A (in
the past they downloaded correctly as csv files, using separate columns).

In Control panel/Regional settings, comma is specified as the list
separator, but this is not having the required effect.

I can go through the "Data/Text to Columns/Delimited/comma" process each
time I download (and that process works), but I would like the default to
operate as it should.

Any helpful advice would be appreciated.

When I use the following code, the File/Save As Windows dialog box opens and
as long as a file name is supplied - the file saves properly. However, if
you click Cancel and don't provide a file name - a false.xls file is created.
Is there a way to avoid this? What I want is just for the save as function
to cancel without a save.

'Prompts to save the TLR file with the appropriate name

ChDir "U:ACTUARIALRenewals"
sFilename = Application.GetSaveAsFilename("", "excel files (*.xls),*.xls")
ActiveWorkbook.SaveAs Filename:=sFilename

Does anyone know the setting so that when you go to file - open in excel 2003 it launches a window for each file opened? seems to me there was something under tools - options, but don't see anything there.. thank you!

Hello,
I have a macro that includes a text to columns operation using space as the
delimiter. After running the macro, when I paste more data into an Excel
spreadsheet space is still being used as the delimiter until I shut down and
re-launch Excel. Is there a statement that I can add to my macro that
would reset the delimiter for pasted data to tab?

TIA,

Tom

How come the same csv-file opens ok on some computer here at work (it autodivides itself into different columns) whilst on some computer it just opens everything into the A column?

All computers at work are _supposed_ to have the same settings.

Anyone have any idea?

Is there a way to put in the macro code something like a translator. I have this code for exporting to csv file.In the process of making csv, i need to translate letters for example, X=Y, S=E, and so on and the results put actually in the csv file. I need this for some autocad content manipulation, because Russian letters are not recognized, and are imported in autocad like set of question marks.
Here is the code i use for csv "save as".

'
'
'
Sub write_csv_xls()
    Application.DisplayAlerts = False
    
    xls = ActiveWorkbook.FullName
    Length = Len(xls) - 3
    csv = Left(xls, Length) & "csv"
    
    ActiveWorkbook.SaveAs Filename:= _
        csv, FileFormat:=xlCSV, _
        CreateBackup:=False
    
'Delete the Existing copy of the file so we can save it back over it
    'without the write-protected error
    Kill xls
    ActiveWorkbook.SaveAs Filename:= _
        xls, FileFormat:=xlNormal, _
         Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
        
    Application.DisplayAlerts = True
End Sub
Thanks in advance!

Since Office 2003, users are unable to launch the downloadable Excel CSV
file. Excel opens and then closes immediately. We changed the file
extension properties for CSV to open with a text editor and the same thing
happens. We have tested side by side Bare-Metal XP and Office 2003 versus
Bare-Metal XP and Office 2000. The issue is specific to Office 2003.

I am in the business of providing URLs to Excel users and other programmers. Such URLs links point to CSV data. Excel users would like to use it in a way that they can "Refresh" at will and programmers depend on CSV data format.

The problem I am facing is that for CSV formatted data, the URL ends up looking like: "http://www.my.com/file1.csv". Excel treats any URL ending in "csv" as a file for which it needs to prompt user every time refresh is desired. I found a property in the Connection to disable that prompt. (Its on Connection Properties window --> Usage tab --> Prompt for file name on refresh). However this becomes a pain for the end user to disable that prompt for every data connection/URL.

My question is: Is there a way to disable that property of prompting for a new data file for each Refresh? In fact, ideally it should be such that its set for all Excel data content downloaded from my site.

Any help is greatly appreciated.

thanks,
Vinay
vinayj1@yahoo.com

I started similar thread http://www.excelforum.com/excel-prog...ng-to-csv.html but i feel i didn't explain my problem exactly so it led to confusion with the guys who was willing to hep me. I ask moderators if its a problem for me starting another thread on the same topic please delete the first one.
So...
First of all i need to explain why I need this because its the usual question that people ask me. I use some excel file for Autocad automation. Im basicaly fillin the tables in Autocad from the data in Excel. I export the CSV in a separate file from that same excel (Im using a macro for that) and use that in Autocad. It all works fine when using English keyboard. Coleagues from Moscow asked me if i could do the same automation For CAD in their office. Of course they all use russian letters in their work. Problem occurs when i save the csv from the excel i use in Russian instead of letters i get a lot of question marks. I didnt even bother to import that in Autocad. But the thing that works is hex representation of the given letter.
For example,
ц U+0446
у U+0443
к U+043A
е U+0435
н U+043D
г U+0433
ш U+0448
щ U+0449
з U+0437
х U+0445
ъ U+044A
ф U+0444
ы U+044B
в U+0432
а U+0430
п U+043F
р U+0440
о U+043E
л U+043B
д U+0434
ж U+0436
э U+044D
я U+044F
ч U+0447
с U+0441
м U+043C
и U+0438
т U+0442
ь U+044C
б U+0431
ю U+044E
When imported in CAD this works just fine. Russian letters appear.
So my question is, is there a way to translate the Russian letters in above stated manner, in the moment when macro saves the csv. To be perfectly clear, i want my excel unchanged, but the csv translated.
This is the code i use for csv save as:
Code:

Sub write_csv_xls()     
Application.DisplayAlerts = False          
xls = ActiveWorkbook.FullName     
Length = Len(xls) - 3     
csv = Left(xls, Length) & "csv"         
 ActiveWorkbook.SaveAs Filename:= _        
 csv, FileFormat:=xlCSV, _         CreateBackup:=False      
'Delete the Existing copy of the file so we can save it back over it    
 'without the write-protected error     
Kill xls     ActiveWorkbook.SaveAs Filename:= _        
 xls, FileFormat:=xlNormal, _         
Password:="", WriteResPassword:="", 
ReadOnlyRecommended:= _        
 False, CreateBackup:=False              
Application.DisplayAlerts = True 
End Sub


Hello,

Using Excel 2007 and Windows 7.

I have a radio control program that allows one to create an Excel spreadsheet of frequencies, radio filter characteristics, etc., and Import it into the program. Then, when using the program, one can tune the radio to particular frequencies, etc that were put on the spreadsheet.

The Format that the spreadsheet has to be in is very definitive,
but well defined.

It also has to be in .csv format prior to the radio's doing an Import of them.

So, up to yesterday, I created spreadsheets, and the program had no difficulty in Importing them.

As of yesterday, it would not do any Import of newly created ones.
The older ones will Import just fine.

It can, of course, be something wrong in the program that became corrupted, but frankly I don't think so. I did reload the software from the **, and did a new reinstall of it. Same problem.

My tentative conclusion is that "something" changed with the Excel.

The spreadsheet that works, and the ones that don't, look to be, other than the numbers, identical. Both are in .csv format and apparently identical, other than the numbers in them.

Is there any way to see the actual .csv formated items in the spreadsheet ? The spreadsheets when I look at them look like the typically saved Excel Workbook ones, but I realize once I do a Save As to a .csv format, they are saved very differently.
Any way of seeing the .csv actual formated items ?

What else should I check between the old ones that work, and the new ones that don't ? At first glance they sure do look identical, but I keep thinking that there is something, meaningfully different between them.

Any thoughts on this, or things to try or look at, would be most appreciated.

Thanks, Bob

BTW: the file icon for the saved .csv files have a picture of a page with the right upper corner turned down a bit, and what looks like a small "a" with perhaps a comma after it. Is this correct for .csv saved spreadsheets ?
Is there a list of these icon pix anywhere ?

I'm having a problem doing something I have done successfully before. I need
to save some double-byte characters that I see in an Excel file into a
comma-delimited CSV file. Sounds simple, but I'm not getting the results I
need.

Here's what I'm doing with the file in Excel 2007.

1. Save As
2. Other Formats
3. CSV (MS-DOS) (*.CSV)
4. Open the file in Wordpad or Notepad

Unfortunately, a cell that looks like this in Excel: 什么是情商?

turns into this in the CSV file: ????????????

Any suggestions?

Thanks in advance for your help!
--
Michael

Based upon my research on this topic I think the following is a bug in
Excel 2003...

In the current workbook, executing a VBA macro that opens a CSV file
causes the current workbook to be recalculated even though; the current
workbook is the only other open workbook AND the current workbook's
calculation option is set to MANUAL. This behavior is extremely
problematic if the workbook executing the VBA code has many formulas,
and thus the recalc takes a long time -- which of course is why you
would select the manual calculation option in the first place.

To reproduce this issue do the following:

1- Open the Excel application

2- Open a new workbook (now the only open workbook)

3- Add the following VBA code to the new workbook:
Private Sub OpenCsvFile()
Dim oNewWorkbook As Workbook
'prompt for file
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Filters.Clear
.Filters.Add "Comma delimited files", "*.csv", 1
.Show
'open as read-only and ignore read-only
Set oNewWorkbook = Workbooks.Open(.SelectedItems.Item(1), ,
True, , , , True)
End With
End Sub

4- Set the new workbook's calculation mode to Manual (From Excel's menu
select Tools/Options - on the Calculation tab, select "Manual" and do
not check the "Recalculate before save box"). Note that this could be
done via VBA code, but I wanted to eliminate that variable in case
someone would raise the concern that setting the
Application.Calculation property in the VBA code was the cause of the
issue (i.e. Application.Calculation = xlCalculationManual).

5- Enter "1" in cell A1 of "Sheet1" of the new workbook

6- Enter "1" in cell A2

7- Enter "=A1+A2" (a formula) in cell A3

8- Now change cell A1's value to "3" (notice the formula in cell A3
still evaluates to "2" since the workbook is in manual calc mode)

9- Execute the VBA code "OpenCsvFile" (above) and select any CSV file
to be opened when prompted.

10- The VBA code will open the selected CSV file

After running the VBA code, look at cell A3 and notice the formula now
shows a value of "4" (the new A1 and A2 values [1+3], instead of the
original values [1+1]). This proves that the workbook has been
recalculated (by Excel) as a result of running the VBA code. Notice
the VBA code does not request the recalc (i.e. Application.Calculate).
In the code above, the recalculation occurs when the CSV file is opened
with this line of VBA code:
Set oNewWorkbook = Workbooks.Open(.SelectedItems.Item(1), , True, , , ,
True)

Others have complained about this behavior in this newsgroup, but
responses to their complaints have focused on changing calculation mode
to manual and/or the behavior of Excel's calc mode when multiple files
are open (i.e. subsequently opened files adopt the prior file calc
mode). Both of those suggested solutions are addressed/included in the
above example, but the issue still persists.

Any ideas?

Thanks!

Hello, I have a datafeed all setup in excel with 2500 or so lines and I'm using ~ and ^ as my field and text delimiters. When I save out to a CSV file it seems to work good. As in it looks the same in excel, but if opened with wordpad or notepad, some extra double quotations are added around the double quotations I already have in a field. For example,

In excel looks like: ~<a href="http://www.example.com/">Bla</a>~

whereas opened in plain text looks like: ^"~<a href=""http://www.example.com/"">Bla</a>~"^

These added doubld qoutes only happen with my fields with HTML in them. The rest of the fields like product name and such look just fine, ex: ^~Product Name~^

What might be the problem?

EDIT: Still haven't figured out the problem, but I think it has to do with escape characters. So far I have to manually open the CSV file up in a plain text editor and manually Find and Replace the "" with single ".

I need a formula to change a column list of phone #s from example 555-555-5555 to straight #s 15555555555 (adding the 1 and removing the dashes).
This is going to be saved as a CSV (Comma Delimited) file used as an online fax list.
Can you provide me a formala for this?

Thank You.

I would like to be able to execute a command line that will convert an Excel csv file to an Excel file that is TAB delimited. In other words, replace the comma delimiter with a TAB delimiter without having to open the file in Excel.

I am an inexperienced Excel user, so any examples would be greatly appreciated.

Thanks for your help

Hi Everyone. I have a tab-delimited file that I open with Excel, make a few tweaks to, then save as a comma-delimited file. However, the export file has to use quotes as a text qualifier as well, and I can't seem to find an option to add the quotes automatically when saving as a CSV file. Does anyone know how to achieve this. I must admit it has been many years since I have used Lotus 1-2-3, but I seem to remember it being quite easy to do in that program.

Since quotes are used quite commonly as a text qualifier in a delimited file, I find it hard to believe that Excel would not support this feature, but I can't find it if it does.

I don't have to do this that often, but often enough that I would really like to find a solution that doesn't involve me opening the CSV file and manually adding the quotes where necessary.

Any help would be much appreciated.

Alan

Hi !

I have an excelfile with about 10 columns of data with about 10 raws.

I save this file as CSV, then I can look in note pad that it looks ok with comma separation. I get this excelfile from another company and it has been working until recently,

Now when I open up the csv saved excel in note pad the raw look like a string: "a;d;f;a;r " (it should look like: a;d;f;a;r)

I have tested to copy and paste an old excel row before saving it into CSV, then it looks correct; that is: a;d;f;a;r (that is, no "" signs)

I have tried to find the differencies in the format of the raws but I connot find any, is there away to find this or is it hidden for normal users ? Or even better , is the a way to solve this.... ?

BR


No luck finding an answer? You could always try Google.