Creating CSV Files
CSV, or comma-separated-values, files are native Excel file formats. That is, Excel can open and read them without any special converters. To create a CSV file, change the "Save as type" box in the Save As dialog box. Take a spreadsheet like this
From the File menu, choose Save As and change the type to CSV.
Excel will give you a warning message that some features cannot be saved. Since CSV is a text-only format, you don’t get anything fancy when you save under this method, just the raw data. Here’s what the file looks like in Notepad after it’s been saved.
When you try to close the newly saved CSV file from Excel, it will warn you that the workbook hasn’t been saved even though you know you just did it. I think Excel does this with all text-based formats and you can just ignore the warnings.
Hi Dick
Many thanks for your info about csv files - I’ve been using these for a while now at work to create automated processes.
However, there is still one problem that we have… I want to save a csv file where one of the cells *must* have 7 characters, even if it starts with a 0. I have found that Excel knocks off the leading zero(s) when saving as csv, even when the cells are set to format 0000000 or ‘0…
Do you know of a way to ensure that these leading zeros are still contained in the .csv file?
Many thanks for a great website (nothing like a bit of flattery ;o))
Kind Regards
Stephen
My 2p worth …
I’ve just done a quick test on this and in Excel 2002 SP3 this seems fine. Created a random series of numbers, formatted them as 0000000 and saved the whole thing in csv format. Then opened it in a text editor and it was fine.
I tried with Excel 2000 as well. It works fine for me using custom formatting.
I use CSV import and export a lot. The only thing I have to be careful with is the long number. Excel will truncate them using exponent. It is necessary to use text format to avoid this problem.
Guys,
Very interesting stuff on CSV’s. Something I have been looking for but haven’t found is how to save a CSV that has quotes around dates and times.
In this particular case, I’m reading in a CSV, making mods to it and wishing to save it back out as a CSV with my edits. Excel just eats those quotes!
Thanks,
J
You mention that CSVs are native file formats for Excel. In my experience though, Excel chokes on CSVs that have any sort of Unicode encoding. For UCS2, opening them from explorer causes Excel to show all of the cells of a row in the first cell (doesn’t seem to recognize the UCS2 comma); you must use the “File Open” wizard to specify the delimiter in order for Excel to handle properly. UTF-8 files are opened correctly with regards to cells, but non-ASCII text (such as Chinese characters in my line of work) will be corrupted. Of course, my experience is limited to Excel 2000, but as far as I know Excel 2k has the same limitations. Do you know if there’s any way to get around these, specifically the UTF-8 problem, as my company prefers to work with UTF-8.
Thanks, and love your blog.
Roy. I really don’t know what native means, only that you don’t need a converter to open “most” CSVs. If the converted is built-in, I call it native. I don’t even know what UCS2 is, for me there’s BIFF and text and that’s about it.
Can you read the non ASCII characters through VBA? You could use the techniques here
http://www.dicks-blog.com/archives/2004/11/09/roll-your-own-csv/
to open and write CSVs if so. I don’t deal with international stuff very often, so I don’t know what kind of limitations there are.
Thanks, I’ll definitely give the VBA a try some time!
Can someone tell me how to convince a CSV file saved with the string “123E45″ to not turn into an exponential number when excel reads it?
Thanks.
Hi. CSV it’s easy
But how can I export excel 2000 table to csv with unicode encoding? 
I will export to csv some tables with a text in russian, but excel 2000 normally don’t support unicode for csv files :(( Can anyone help me? Thnx
Hi,
How can I get data stored in CSV format into the cells. Its a reverse work, from CSV to excel for data manipulation. ANy help?
thanks in anticipation
regards
sameek
sameek: You can open CSV’s in Excel directly. Every comma is a column and every line feed it a row.
Hi, I was wondering how to export an excel file to csv with quotes around the cell values. I just cannot find an option for it. Anyone with experience with it please let me know. Thanks!
Same question as 12. Would like to export to CSV format, but need every cell to be surrounded by quotes. Seems Excel removes the quotes on save.
skky and Sean: I don’t think you can do that with anything that’s built-in, but try here
http://www.dicks-blog.com/archives/2004/11/09/roll-your-own-csv/
hi, can anyone help me with this problem, Im Opening a .CSV in Excel and it shows all of the cells of a row in the first cell, instead of separate the values in each column…. Thanks for your help!!
The CSV file = 208145,SERIO,ZERMEO,VARGAS,21/02/2005,DIFERIDA,1,P,MERIDA,ZEVS401127HH2,3 A,6 Y 8,365
I try to open in a different PC with excel and opens correctly, what is wrong with my excel?
Dick,
I saw this posting on your website:
Stephen Wortley Says:
November 8th, 2004 at 6:35 pm
Hi Dick
Many thanks for your info about csv files - Ive been using these for a while now at work to create automated processes.
However, there is still one problem that we have I want to save a csv file where one of the cells *must* have 7 characters, even if it starts with a 0. I have found that Excel knocks off the leading zero(s) when saving as csv, even when the cells are set to format 0000000 or 0
Do you know of a way to ensure that these leading zeros are still contained in the .csv file?
Many thanks for a great website (nothing like a bit of flattery ;o))
I was not able to view your response. Is there somewhere I could go to view your response as this is an issue that I am currently encountering. Thanks for any help you can give me.
Brian
Hi
I have an Excel VB program that reads a html file and saves as a CSV. However, I would like to change delimiter to something other than comma. Is there a way to specify the delimiter character in VB? I would like it to be a ~ or |.
Thanks
Raja
Raja: There’s no built in way to do that, but you can use a macro as described here
http://www.dicks-blog.com/archives/2004/11/09/roll-your-own-csv/
We have just had a problem with saving a CSV file with a leading zero for one column of data so I did some experimenting with custom formatting and then saving the data to a CSV file.
I thought I would see if anyone else had the same problem and if there was a different answer to what I had come up with.
It seems there are a few people looking for an easy fix to the problem.
The following is a fix that I have found works with Excel 2003.
To save data with a set number of leading zeros you can custom format the cell or column with something like 0000000.
This will add leading zeros to any number less than 999999 eg 1234 will be 0001234, 123456 wil be 0123456
If you want just a leading zero on a number use the custom format 0######.
This will add a leading zero to any number less than 999999 eg 123456 will be 0123456, 1234 will be 01234.
All you have to do is now is use the Save As feature and save to a CSV file.
Hope this helps.
Peter
The best solution for all the formatting issues when reading CSV files is probably to stop using Excel to read the files altogether, and instead use a parser, http://www.csvreader.com/ .
if you try to import multilingual data (like Japanese characters) from csv file via Java, and use inputstreamreader with utf-8 encoding, it will not work. This is because excel uses an encoding that isn’t directly surpported by Java. Any good suggestions on how to solve this problem?
When I save a document containing smart quotes to a .csv file, it is replacing the smart quotes with ?. Any idea on how to get this to stop?
Custom Formatting works for fixed length and formatting as Text will work for variable length when writing out a CSV file, but Excel always reverts back to formatting the cell as number when you open a CSV file.
If you had data like this; 0009,09,9. Excel would convert all or 9,9,9…with no clue as to what it should be.
Even more fun is having cells containing 01-02, 02-03…Excel converts these dates.
Why doesn’t Excel use the same import wizard that Access uses” It allows you to override how to import a cell?
Re CSVs with quotes. If you save it as a csv in Excel it removes the quotes. You can open the file in Access, change all fields to text and then export again as csv it will add the quotes.
http://members.chello.at/robert.graf/CSV/
When you “Save As” your Excel file as a CSV file, the original Excel file has not been saved. That’s why you get those warnings. Imagine this scenario:
1. Open an XLS file.
2. Change the contents of cell A1 from 5 to 7.
3. Export the XLS file as a CSV file.
4. Close Excel and choose not to save the XLS file.
Your XLS file will still have 5 in cell A1. The CSV file will have 7 in the same location.
HTH
I orchestrated my first VB yesterday… So I am a novice.
I am not sure what you mean… I have used filter and wuto filter before, but how do I incoroprate it into my VB code so it is automated.
Currently I manually pull data via ODBC
For eg. select x from Y where z > 1000 and dt = 20041231
What I want to be able to do is to input x and z > 1000 and pass it to the query t run.
I was able to this with the dt = part yesterday where I pass in the date. And that took a whole day.
I am not sure how to do the rest.. like being able to select a item frm a list and have it represent x.
Thanks in advance.
For those who have the same problem as Jerry at position 15. have to change the setting from the Regional & Language Options, will have to set in Regional Option the language to uk or something, at least this is how i solved the problem. U can find more about this at: http://members.chello.at/robert.graf/CSV/
hey Raja you can change the delimiter to something other than a comma by changing the list separator property under the language settings you are using in regional options (control panel->regional and language options-> regional options tab Customize button), dont forget to change it back.
got a csv file having dates and when i opened it in excel. for the date columns i selected the column and selected date format as dd-mmm-yy. most of the dates got changed except for a few. Also the few which did not change were left aligned (like text) instead of left align like all other dates.
Any idea and a solution to this?
I have been exporting data from database to csv files. I am able to successfully view it in Micosoft Excel also. The first line in my csv files is the column heading, is there any way to display the headings( first row in csv file ) in bold?
If u have any clue on this please let me know.
Many thanks
Hi!
I am currently working for a company that uses excel and access to track incoming orders. I get the order files in a .csv or .xls format and then pull it up in Excel. All fields have to be in a certain format, such as date 2005-10-10, etc. I change the data in Excel and then save to a .csv file. Then I import into Access and also upload the orders to a credit card company. When I save to .csv and then open the .csv file in Excel, however, I have 2 problems.
1 - The data formats that I changed in Excel to make the data match what I need (10/10/05 to 2005-10-10, for instance) is back to the original data (10/10/05).
2- The last digit of the credit card number is gone and replaced with a 0.
If I open the .csv file in Notepad, the data is correct. But, I have to have it in Excel…
Anyone have any ideas on what I am doing wrong or how to fix this? Thanks!
After changing the data in Excel, do you need to keep it in CSV format? If it were mine, once I got it into Excel, I’d leave it there (or get it into Access). Formats are a common casualty when data is saved to CSV file.
Hi Jon,
Yes, it has to be .csv to import into my other software. The problem is when I open a .csv in excel a lot of the data becomes unusable - for instance orders off our website download as a .csv file that automatically opens into an excel spreaddsheet when it finishes downloading. The orders have a credit card number that imports from a .csv into excel as a scientific number. If I change it back to number format all the last digits of the credit card number are 0. This makes it a little difficult to fulfill orders. Any help would be appreciated. Thanks!
Julie
Arggh! I wrote a nice response, but it got eaten by the browser.
Anyway, you can “import” the CSV file, rather than just opening it. On the Data menu, choose Import External Data, then Import Data. Browse to the CSV file, and click Open. This brings up the Text Import Wizard.
In step 1, select Delimited. In step 2, check Comma; this previews how the data will be separated. In step 3, you can select each column in turn and choose a format. Choose Text for that credit card number field, and you’ll get all 16 (or however many) digits, not truncated to zero, and without all the mad scientist formatting.
Hi,
I really need some help…I have some data in txt and csv format. I would like to set the 3 diffrent columns with 3 different fixed lengths. With 3000 over lines, I would like to know if there is an alternate way to set the fixed-length of the columns with spaces(to fill the lenght) using Excel?
If not, what other programs can i try?
For Eg:
(123 ,name ,remark …)
Any kind of tips and help would be much appreciated.
Thanks in advance.
Hi I really need so help. I have a CSV file and I want to write a script that deletes records based on the criteria of one of the fields
Eg
Field_1
Field_2
Field_3
Cash_Payments
I need to create another CSV from the original that deletes all records where Cash_Payments 0
Many thanks!!
Hi here is a good one for ya, I have developed a program in VBA to inport data and maniulate it to the format that i desire, afterwards i want to save it as a csv (automated, embedded in my program), problem is when i run my save routine is does not put the file in a true csv format. here is the simple program:
Sub Save()
ActiveWorkbook.SaveCopyAs “F:\DATE.CSV”
End Sub
I get a file that is too big.
Thanks Bryan
How can I format text in csv file from perl script.
Can you assist me?
How can I format text in csv file from perl script.
Can you assist me?
Thanks Suborna
Hi Everyone,
I need some serious help on this issue..
I have a table that contains many special characters like “é,é,é,j,à… “.I have written a java program to create a CSV of this table.
But the output doesn’t come witht he special characters as above..
What can i do??
Please help
Hi guys,
I am trying to get a csv file from a xml file by applying a xsl to it. It works fine for all non UTF-8 characters. However for data with utf8 characters (like japanese and stuff), the excel sheet is actually showing some different characters(they are ascii i believe).
For example..
The data in xml is :
Я можу їсти шкло, й в
On viewing the xml in textpad :
Я можу їсти шкло, й в
On applying xsl to the xml to get csv:
Я можу Ñ—Ñти шкло
Can anybody help me figure out what I should be doing to get proper data in xsl file…
Hello. I found this thread from a Google search, and I think I have also found the solution (thanks to http://66.102.7.104/search?q=cache:5qnnMYmKstEJ:forum.java.sun.com/thread.jspa%3FthreadID%3D655662%26messageID%3D3854182+excel+UTF-8+encoding+csv&hl=en) : Specifically for those of you having Unicode or UTF-8 character problems, the very first page of a Google search for the input words: excel UTF-8 encoding csv
will give you what you need to know. There does not seem to be any universal way of telling Excel that a CSV file contains UTF-8 characters, but you can input that information via a dialog and filter if you import the .csv file renamed to .txt instead. Then you can specify the File origin to be UTF-8.
So the problem is either to keep xsl from converting the code-page-specific single byte values (128+) to double-byte UTF-8 OR to teach the program which opens the .csv file to interpret it correctly.
Hope this helps.
Hi,
I am creating a csv file from a java program, now when i try to save a number with leading zero it is deleting those leading zero, as many of you have suggested in the above comments that i can do a custom, but when i am creating a file itself from a program how do i set the custom column……
Thanks for anybody’s reply in advance.
Thanks,
Yogesh
Hi,
i Create a CSV File with 300 columbn but when i opened it in excel then it give error and open and some data is missing. so can we do somthing
Rupesh, I’d suggest you post future questions in the Excel newsgroups, where lots of people are available to help you, but here’s an answer for you.
Excel only has 256 columns, that is why you are having the problem with 300 columns. If this is a process you will do repeatedly, write or have written for you some VBA code. If not, here’s a workaround. This should work as long as the combined length of all the fields in a record is less than 32,768. If it’s longer, I think you’ll have to use some code.
1. Rename the file to “.txt” instead if “.csv”. This will allow you to read the file in without Excel trying to parse it out. All of the information on the first line will be in cell A1, the second line in B1, etc. OPen the file in Excel.
2. In B1, put the following formula, and copy it down. It finds the 200th instance of a comma in the file.
=FIND(CHAR(7),SUBSTITUTE(A1,”,”,CHAR(7),200))
(Thanks to Ron Coderre, who posted this to a newsgroup recently, so I didn’t have to try to figure it out)
http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/aa67702a9453a69d/919d3ec1bd0247fb?q=excel+find+text+string+instance&rnum=2#919d3ec1bd0247fb
3. In C1, put
=LEFT(A1,B1-1)
4. In D1, put
=MID(A1,B1+1,100000)
5. Copy B1:D1 and paste it down as many rows as you have data.
6. In column C you now have the first 200 fields of info, and in D you have the last 100 fields. Copy each of these and paste to a tab (using Paste Special-Values).
7. In each new tab, run Data-Text to Columns, choose “delimited” and choose “comma-delimited”
Good luck.
Hi
I tried importing a .CSV file containing Chinese(Simplified) characters into MSExcel worksheet in my .NET application. And I had the same problem as others had. The Chinese character sets in the database and the .CSV file dont match. The BOM of the .CSV file is fine however.It is EF BB BF, still the chinese language encoding seems to be different. Only after saving the .CSV with UTF8 encoding throu the file conversion wizard in Word or Excel I get the original characters. Now My problem is that I need to force the action of the File Conversion Wizard in .NET programmatically so that the right language encoding is chosen..Please do tell me if there is any way of specifying the encoding info while importing the csv file to Excel..The locale settings are all Ok..
I had the same problem when I once worked in Perl where I had to change the encoding of Chinese strings (UTF8 encoded)from Simplified Chinese to Simplified Chinese GBK (code page 936)for the Chinese characters to be displayed properly in the Windows and DOS console. I realised that the encoding used by MSDOS and Windows Default encoding chosen for UTF8 strings, were different.And then I found the solution in the CodePage module..I think this info will be useful coz the problems in both the cases are similar..
Do correct me if I’ve understood anything wrongly…I need a solution for overcoming this problem asap..Thanks…
For converting csv to unicode, after you exported it from excel, you may need a second software which can change the BOM of the text files.
I use emeditor. Simply open the csv file and choose save as, below file type you can change the encoding of the txt / csv files.
Yet I am still looking for a way to do it in one step.
Hi Ranjith,
It is not possible to write special characters in csv file because csv file is nothing but a text file.
Thanks,
Suborna Fermi
For JStockton in position 23
After searching for about an hour, this solution may help those that would like to prevent data like 0 - 1 to be converted into dates.
After importing, invoke the Text to Columns wizard to parse the data.
Select Delimited, choose comma as the delimiter
Then on Step 3 of the process, select the column and tell Excel it is a Text column.
Hopes this helps,
RD
Following is for search engines so others can find it:
excel converting csv data to dates
excel converting negatives to dates
excel converting hyphens to dates
excel converting numbers to dates
When saved as CSV( I may have several worksheets in xls, and each is saved as csv ) , message box does appear ( “Save changes ‘yes’ , ‘no’ , ‘cancel’ ) . IF programmatically save 100 xls files as csv , would it be possible to avoid popping up this message box somehow ?
TIA
Michael
Assumeing you are using Excel VBA, if you do not want the ( “Save changes ‘yes’ , ‘no’ , ‘cancel’ ) message to appear. All you have to do is to write a statement:
Application.Displayalerts = False
This is almost a must for programming. However, it eliminates all alerts and so you have to exercise caution as to where to put it and where to remove it (by setting it to True).
On the subject of leading zeroes in CSV. The common misconception is that Excel strips leading zeros when it saves a file as CSV. This is not quite true. The leading zeros are still there - if you open the file by Notepad, you will see them. What actually happens is that Excel automatically recognizes a field as numeric WHEN THE FILE IS LOADED and takes out the leading zeroes then.
I Have a big problem whith UTF-8 encoding, and Whith Chienese chars !!! how to save a document in CSV and UTF-8 in Exell ???
Bob
http://picasaweb.google.com/china.landscape/
hi all,
i am trying to create a .csv file using Excel. and i am making sure that all the columns have the wrap option unchecked and the first column as Text format (because some cells have 12-001 which excel might interpret as Dec-01). i am saving the file and closing it then opening it again. however when i open it, all the columns have the wrap option checked and the 1st column has its format as custom date and not text which is causing some cells to show dec-01 instead of 12-001.
how can i stop excel from doing that?
Can someone tell me how to convince a CSV file saved with the string “12333333333335845″ to not turn into an exponential number when excel reads it?
Re: prashant jain
if this csv file is only going to be read by excel you can try “=”"12333333333335845″”" as the value.
Re: prashant jain and Dan,
Or import that column as text format
Opening .csv in Excel loses leading zeroes:
if you have the .csv as: a,b,04,004 , you lose the 0
if you have a,b,=”04″,=”004″ , you keep the zeros.
Catch is that Excel then thinks that you’ve made changes to the file
Also, to find an exact match (eg 04) using ctrl+F, you have to look in Values, as it’s now the result of the formula.
Hi
I have a web application working under Linux Suse which exports data from the database to a CSV file (with Japanese characters)using OutputStreamWriter with utf8 as encoding. What happens is the CSV file outputs the correct characters in Linux but when I try to view the file in Windows Excel, I get the wrong characters.
Moreover, when I change the encoding of OutputStreamWriter encoding to shift_jis, I can see the correct Japanese characters in Windows but not in Linux.
Is there any way to solve this problem? Thank you very much.
I’m sorry, if this solution has already been posted, I didn’t feel like reading past 24 ish.
To solve my prob with excel auto-formatting my csv files when they are opened.. i just change the file extension to .txt — then excel starts the text import wizard and you get to specify how each column is formatted on import.
Hi, there. I am super frustrated with this file conversion and have been working on it for hours trying to export an Outlook distribution list so that I can transfer it to a friend.
I have managed to save the entire list as a text file, and originally it was in the format “Name ” so I did some find/replace to get it into a comma separated format like “Name,email@domain.com”. However, every time I try to either save it with the .csv extension, or import it into excel and then save it as a .csv from there, it tells me there are elements that are not acceptable .csv elements, do I want to proceed anyway. I say yes but then when I try to import the .csv file with Yahoo it says it’s not a valid .csv file.
What are these elements that I’m using that are not allowed? What format should I have these addresses in so that I can save it appropriately? Is it better to save it from the .txt file and just rename the extension .csv, or import it into Excel and then name it from there?
I’m out of ideas at this point, thanks!!
Lindsey - If the Outlook distribution list is in your contacts, right click on the object and choose Forward.
When your friend receives this file they can just drag it into their contacts folder…
I have something weird going on here. When I save the CSV file in Excel, it opens in Notepad with semi-colon separators instead of commas.
Also, when I try to open a CSV file in Excel, it does not put it in nice columns, but shows everything in 1 column separated by commas.
What’s up with that?
Hi Friends,
Here is VB.NET code, that you can use to export excel file with special character.
Dim grd1 As New GridView()
grd1.DataSource = dtOut ‘ dtOut is my Data Table
grd1.DataBind()
Response.ContentType = “application/vnd.ms-excel”
Response.AddHeader(”Content-Disposition”, “attachment;filename=products.xls”)
Response.Charset = “”
Response.ContentEncoding = Encoding.Unicode
Response.BinaryWrite(Encoding.Unicode.GetPreamble())
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
grd1.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
when you get excel file you just open in MS Excel and Save As. CSV file.
Regards,
Kataria Bipin
Hello,
I am facing a strange problem. I have an excel file and am trying to save it as a .csv file. When i save it, the date format goes haywire. The excel file has the date format as mm-yy i.e. jul-04 but, when i save it as a .csv file, the date changes to jul-07.
can anyone please help me out?
for all those who had a problem with the quotes being thrown away when opening csv in excel - rather use import dexternal data and then after you have chosen the comma as the seperator the next screen asks what is the text qualifier it is set to ” so just change it to none
Hi !
Within Excel I created two macro buttons
One which changes the default delimiter from e.g. a comma to any other chosen character
and another which reverts the delimiter back to a comma
It’s very easy to implement and have it documented in a Word document. If anybody wants a copy or prefers me to copy, feel free to email me:
email_walesgbn@yahoo.co.uk
Rgds
Brett
Hi, novice here. I create a xls with 10 items on line one and then 8 items on everyother line. When I save as CSV and then open in notepad lines and on have “,,” at the end - is this just to keep the csv square (ie adding empty items on the end of the rows)? Will this affect any program that tries to process the csv? Or am I worrying about nothing!
My gues is that you probably have some non-empty cells in the two columns after your last column. You should be able to resolve this easily.
Highlight all the empty columns after your last column, right-click, and then select Clear Contents.
Hi, thanks for that but it hasn’t helped, I’m still unable to create a csv file with a different number of items on different lines. It’s just a bit weird. I’m using Excel 2003 SP2.
I see the same effect as Dave, double commas to pad out the CSV to have the same number of items per row. If you delete the superfluous commas, Excel still reads the CSV correctly. As to other programs, it depends on how they read the CSV file. If it was a simple VB6 program, then you’d be better to have the same number of items per line anyway, as you’d normally be assigning each item to a variable. If the program reads the line as a single string and then parses it into an array based on the comma then you could have issues with it showing more items than are actually there.
The guys that will be processing my csv reckon it will be fine. Thanks for the help though - I’ll keep trying to see if different programs do it differently.
Hi all,
is there any way of formatting the cells when we create a .csv file?.
th problem i am facing is i am filling the column with lengthy integer, i.e. more than 12 digits in length. due to the internal operation of xls its showing the integer in scientific format.
as our friends has mentioned appending a “=” in the front will make it open as a string and display the correct integer value.
The problem occurs when i am opening in any other editors (notepad, textpad). the “=” is present in the file. so i would like to know whether we can tell the .csv as this column is string like wise instead of using the export wizard of xls.
nice blog…..but i am facing the same problem as roy because excel doesnot show UTF-8 characters like chinese and japanese when converted from CSV format
Dave Blower, your initial suggestion on the reason for the extra commas was correct. The length of a csv is based on the largest number of columns in the range that Excel determines will be saved in the csv. If columns are missing values, then the files will have blanks in between the “extra” commas.
In most applicatinos, this is the way you’d want it–otherwise you’d never know how many elements to expect in a line. How you deal with blanks found in the csv depends on the app, of course.
For numbering 0001 - 1000 simplest way is to start at 99990001 and drag down to 99991000 and then do a find and replace in a text editing program for ‘9999′ into ‘ ‘ (or nothing). This will delete the 9999 and leave you a number with the 0s intact.
Hi, I am using office 2007, in excel 2007, “save as”, I see no option let me save as “csv” file, anyone could help?
Water - “Office” - Save as - Other formats. CSV is in the drop down list (save as type).
I have an macro that I would like to run when the workbook is open.
MACRO:
Sub saveascsv()
ActiveWorkbook.SaveAs Filename:=”C:\documents and settings\workstation\desktop\medicare batch eligibility\ztest_change.csv”, FileFormat:=xlCSV, CreateBackup:=False
End Sub
How can I tell the macro to run when the workbook is opened and then close the workbook.
Thanks.
turtlkky
You can find answers to questions like that on the Excel newsgroups, which are a great resource.
http://groups.google.com/group/microsoft.public.excel.programming/
But:
(1) you need to name the macro Auto_Open.
(2) add this line at the end:
ActiveWorkbook.Close False
False closes without saving, True if you want to save changes. I assume that something else happens before the “SaveAs”, since otherwise you;d save the same file everytime.
Hi,
I am trying to save an excel file as a csv file with a value of 00 in one field. I have tried to customise using a number of different values and customisation without any success. This file has been used previously as a look-up value file for a third party report writer and it had the 00 in it.
I am desperate!! for help as our reports are useless without having this filed validated.
Actually i’m having a problem here while creating a text file here: For Each column As DataColumn In dataTable.Rows (highlighted in red)
error msg: invalidCastException was unhandled.
my vb.net codes:
'create the command object for the connection to the database
'also assign the sql statement to be use in querying our database
Dim command As SqlCeCommand = New SqlCeCommand("Select ProductCode, Weight from Transactions", New SqlCeConnection("Data Source=\Program Files\Meaders\Meaders.sdf;Password=meaders"))
'open the connection
command.Connection.Open()
'create the sqldataadapter that would hold the result set
Dim sqlAdapter As SqlCeDataAdapter = New SqlCeDataAdapter(command)
'create our datatable
Dim dataTable As DataTable = New DataTable
'fill the datatable with the values fetched from our query
sqlAdapter.Fill(dataTable)
'set the conttent type of the file to be downloaded
Dim sr As StreamWriter = New StreamWriter("\Program Files\Sample.txt")
'HttpContext.Current.Response.ContentType = "Application/x-msexcel"
'add the response headers
'Response.AddHeader("content-disposition", "attachment; filename=""" & filenameField.Text & """")
'add the contents of the result set to the response stream and generate our csv file
Dim test = ToCSV(dataTable)
MsgBox(test)
sr.Write(test)
'close the connection
command.Connection.Close()
End Sub
Public Function ToCSV(ByVal dataTable As DataTable) As String
'create the stringbuilder that would hold our data
Dim sb As StringBuilder = New StringBuilder
'check if there are columns in our datatable
If (dataTable.Columns.Count 0) Then
'loop thru each of the columns so that we could build the headers
'for each field in our datatable
For Each column As DataColumn In dataTable.Columns
'append the column name followed by our separator
sb.Append(column.ColumnName & ",")
Next
'append a carriage return
sb.Append(vbCrLf)
'loop thru each row of our datatable
For Each row As DataRow In dataTable.Rows
'loop thru each column in our datatable
For Each column As DataColumn In dataTable.Rows
'get the value for tht row on the specified column
'and append our separator
sb.Append(row(column).ToString() & ",")
Next
'append a carriage return
sb.Append(vbCrLf)
Next
End If
Return sb.ToString()
End Function
can anyone help me please. thanks
Hi All,
I’ve an issue saving a csv file in Excel 2003 SP2. I’ve created an 11 field file in Excel and saved it as csv. In this file some of the fields will not always be populated and it’s often fileds 9 -11 that are left un-populated. Excel maintains csv format fine for the first 16 records regardless of what fileds are populated. However from record 17 onwards, if filed 11 is not populated then the trailing commas are not inserted. This can be tested by opening in notepad.
e.g.
Line No 15 - Joe,Bloggs,M,147852,111122,L,A,01/01/2007,,,41
Line No 16 - Jane,Bloggs,F,147852,111122,L,A,01/01/2007,,,
Line No 17 - Jack,Bloggs,M,147852,111122,L,A,01/01/2007
Line No 18 - Jill,Bloggs,F,147852,111122,L,A,01/01/2007
Is there anything that can be done to ensure that trailing commas are always inserted regardless of whether the fields are populated. (like they are for the first 16 records). Has anybody encountered this issue before? Has anyone a decent work-around. This file will be processed by an application to add records to a database.
Any help appreciated.
Thanks
Jose
Hey Jose, yeah, I just noticed that problem too. It’s a work related issue, in fact. And it’s screwing with my records. However, it’s only occuring on field AA.
I have two files. One requires field AA, the other doesn’t. If I save the file with nothing in column AA, Excel 2003 will sometimes put column AA in the first 16 records, when I don’t want it there at all. Other times, when I save the other file with a column header for AA, it will put commas in only the first 16 records, removing the commas that I want for the rest of the records! Looks like it’s time to edit the files by hand using good old-fashioned NOTEPAD.
One workaround, if you WANT the fields there, is to make sure there’s a value in every cell in the column. Make it a 0 value, or something you designate as empty. That will force the commas to show up. As for making sure the first 16 commas DON’T exist, I can’t help you!
What was Microsoft Thinking? Oh, I know. “Holy crap! Deadlines! Ship it anyway!” ^_^
-Steve
I just hit this yesterday and have been googling around for a better solution. It is very frustrating because the app I exchange data with chokes and dies on record 17 when the trailing commas disappear. Two approaches seems to work 1) I wrote a VBA macro to open the file as text and fill out the correct number of commas to any line that was short. Ugly, but it works. 2) As mentioned above, make sure the last field in the record has something in it (appropriate to the data type. Space characters seem to work). Seems really sloppy on Microsoft’s part.
Turns out there is a Microsoft Knowledge Base article: KB77295 “Column delimiters missing in spreadsheet saved as text” (http://support.microsoft.com/?kbid=77295).
I am trying to export data to .csv format from perl
Hearder used –>
print “Content-type: application/vnd.ms-excel\n”;
print “Content-disposition: filename=$filename.csv\n\n”;
Code to insert data in excel –>
print join(’,', @columns);
for ($i=0 ; $i”;
for ($j=0 ; $j[$i][$j] =~ s/\,/\./g;
if ($data->[$i][$j] =~ /^[A-Z][a-z][a-z]\s+\d{1,2}?\s+\d{4} 12:00:00:000[A|P]M$/)
{
$data->[$i][$j] = convert_dbdate_to_standard_date($data->[$i][$j]);
}
$str .=$data->[$i][$j] . “,” }
chop $str ;
print $str;
} #for
This shows each row as csv (i.e. all column values are comma separated) and i have about 380 rows.
Now when i try to save this as csv, first it gives an error message : not the correct csv format. Even if i save it, the columns are not segregagted.
I tried opening this .csv using notepad and noticed that each row is surrounded by “” meaning my row is a complete string and this stops the further column breakage.
like “001,SBTN ,0,19651″
“002,SBLJ ,0,19651″
If i remove the quotes and save again , then i can see real grid format in .csv
001,SBTN ,0,19651
002,SBLJ ,0,19651
Anyone has a solution?
With CSV file import, excel has a mechanism to distinguish between commas that are field separators, and commas that are part of the field data. With any string enclosed in double quotes, i.e. “001,SBTN ,0,19651″, the commas within the quotes are treated as part of the cell data instead of cell separators. You have to figure out how to program Perl to not write your data to the disk file with the $str data line enclosed double quotes.
“001,SBTN ,0,19651″ = one column, if imported
001,”SBTN ,0,19651″ = two colums, if imported
001,SBTN ,”0,19651″ = three columns, if imported
001,SBTN ,0,”19651″ = four columns, if imported
001,SBTN ,0,19651 = four columns, if imported
If anyone needs it, I have a relatively fast, small, CSV parsing class called ‘CsvReader’. It reads items quickly (much faster than other VBA readers I have found). It loads a CSV file from disk sort of in a streamed manner. You just call the ‘ReadString’ function repeatedly to get items, and it tells you with the ‘EndOfLine’ property whether you’ve hit a new line. You can also read an array of strings from an entire line with ‘ReadLine’. If you want it, e-mail me at ashton(dot)wilkins(at)gmail(dot)com. I use it to parse large CSV files. You can split up such files and load them into Excel worksheets one at a time if you want to load a very large file.
Comma as a data in a input file while creating CSV how to avoid commas.
I am using PHP for export program. where i put data in file in following way $data = $row[0] . “,” . $row[1] . “,” . $row[2] . “,” . $row[3] . “\n”;
If any of the row field contain comma as a part of data like “Jose, Smith”
then it crate the one cell for jose and one more for smoth.
I wanna keep jose, smith in one cell.
Hi
I have an Excel sheet with some Chinese Characters. While converting the Excel sheet to CSV file, the characters are replaced with question marks (?). Is there a way to retain the Chinese characters in CSV file while converting the Excel sheet?
Thanks
Amit
I have a vba routine that saves an excel 2000 sheet to my C: drive as a CSV file. The problem is that all of my cells text content is being truncated to bring it down to the 255 length.
Does any one know how to disable this or a work round.
Much appreciated
Pete
Hihi..I hv a question abt .csv file..
i would like to save all the sheets in the same csv file..so what should I do..??
Regards,
shizu
Hey you,
I do have a question about csv-files as well:
A calculating program gives me a csv-file. Importing is no problem, but I want to change some data and load it in the program again. The problem is, the in the csv-file the program creates at the end of each line is a rectangle (I think Unicode) to marc the end of a data-set (you can see it when you open the file with an editor). When I change the data with excel and save it again as a csv-file, these simbols are gone and the program is not able to read the file anymore. Do you know how to write a simbol at the end of each line automatically or if it is possible to write a macro in Excel to create such a file on my own?
Thanks a lot,
Alex
i, novice here. I create a xls with 10 items on line one and then 8 items on everyother line. When I save as CSV and then open in notepad lines and on have “;” at the end??eg:
in excell:
1 11
1 21
3 13
after that i opened in notepad,the result:
1;11
1;21
3;13
can u help me to resolved this problem??thx be4
Hi,
I am tring to write some data(read from DB) in an excel file using java streams. I am using the Output stream to write the data. When I try to write Kanji characters I am not able to see the charactes correctly. E.g. K-ROLL（FACT-A）巣鴨（OFF-20190） appears as K-ROLL（FACT-A）巣鴨（OFF-20190）
Can any one suggest the reason and soluction to this. I am using excel 2003.
Below is code I am using.
String excelFilePath = “C:/temp/ExportOrderItemResults.xls”;
FileOutputStream fWriter;
PrintWriter valueWriter;
OutputStreamWriter outputStreamWriter;
File outputFile = new File(excelFilePath);
fWriter = new FileOutputStream(outputFile);
outputStreamWriter = new OutputStreamWriter(fWriter, “UTF-8″);
valueWriter = new PrintWriter(outputStreamWriter);
try {
valueWriter.print(”Column1″ + TAB);
valueWriter.print(”Column2″ + TAB);
valueWriter.print(”Column3″ + TAB);
valueWriter.print(”Column4″ + TAB);
valueWriter.println(TAB);
while(sqlResultSet.next()) {
valueWriter.print(sqlResultSet.getString(1) + TAB);
valueWriter.print(sqlResultSet.getString(2) + TAB);
valueWriter.print(sqlResultSet.getString(3) + TAB);
valueWriter.print(sqlResultSet.getString(4) + TAB);
valueWriter.println(TAB);
}
valueWriter.close();
}
catch (Exception e) {
e.printStackTrace();
}
Thanks,
Rupesh
Hi,
i have a csv with value ‘91e5′ in it. when i try to convert it to an excel, this ‘e’ is automatically considered as exponent. i dont want this to happen. what can i do to avoid this?
Hi I receive data in CSV format, which is opened and edited in Excel, the data contains the ‘TM’ (trademark) symbol/special character. When I save the spreadsheet again in CSV format, the special characters all become ‘?’ This only happens on one computer though.
When the same operation is carried out on a Windows Vista/Office 2007 machine, it works fine keeping all the formatting, on an alternative laptop running Windows XP/Office 2007 it is also fine. However on one laptop with windows xp/office 2007 it will just lose all the special characters. I have checked that all the service packs etc are up to date for Office on all the computers. Does anyone have any ideas?
Wow- this was quite helpful! I was submitting a grant today to National Science Foundation for a professor, and had to change the spreadsheet into a CSV file. Yikes. But this site was a big help. Thanks much!
Anyone looking to produce a ‘properly formatted’ CSV that should have double quotes around text and date values will struggle with Excel because all it seems to do is separate each cell with a comma and it doesn’t bother with double quotes.
Solution: OpenOffice Calc !
It works a treat, except that dates default to dd/mm/yy instead of dd/mm/yyyy. Not a problem as you can change the format prior to saving, but just a pain. Otherwise, Microsoft could learn from OpenOffice
hi , i often need to change a number of txt files to csv. does some one have a macro to do that ie automate the process. thanks
Hi, When I edit a CSV file I loose all my quotes when opening it in a notepad to be used with Access. Does anyone know how to maintain those quotes while being edited in the CVS format? Thanks
Man for a second I thought my Excel (version 2003) didn’t allow me to save CSVs. It only showed XLS, XML, XLT and HTML. I googled for help and your guide came up. That one screenshot reminded me to check the Save dialog again. Turns out I just had to scroll allll the way down to find the .csv option.
Is there a way to import tags from an excel file?
I have a .csv file that my web developer used to create a database of information which is
used on my website. I can download and open the file in xcel 2004 on Macintosh and make all corrections. When I go to save it I get a message that says that the file may contain features that are not compatible with a cvs file. If I continue to save and upload to the web it is corrupted. Any ideas for me.
Thanks
@Robin.
Make sure you are saving as a CSV file and not XSL. It sounds as if Excel is trying to preserve your formatting by saving it in native XLS format rather than CSV.
How can I save a utf-8 .csv file such that it remains in utf-8 with Excel 2003? I tried but it saves it in sme Western something and I lose the readability of some special language characters. Thank you in advance!
How can I save an utf-8 .csv file imported in Excel 2003 such that it remains utf-8? Currently, when I save it, it becomes Western sg and I lose some special Hungarian characters. TU in advance!
hello everyone,
I have a user trying to open a *.csv file in Excel (using Office 2007), i’m getting the following error
“Cannot create file: *.csv. Right-click the folder you want to create the file in, and then click Properties on the shortcut menu to check your permissions for the folder.”
He forwarded the file to me and I opened it fine. The only difference is that I am running ??Windows 7 as my OS and he is running Windows XP.
Any suggestions?
Thanks!
Did he check the permissions of the folder? Did he have write permissions?
well the file was sent to him…it’s not saved on his computer…so he was just trying to view the file.
some details a have presented here:
http://sites.google.com/site/igorbustiuc/tips-tricks/microsoftexcelsaveascsvfileunicodecharactersissue
If your text in CSV file has the character ‘e’ along with numbers it converts them to exponential value, I have a workaround for this. If the CSV file is generated through a program wrap the text with a excel text function like this =trim(”123E45″) this would force excel to treat it as text rather than numeric.
I am exporting some data in a .csv file. But when I open that .csv file using excel it converts lengthy strings (like “1233333333333333333333333333” ) into exponential number which I don’t want to happen..
how to convince a CSV file saved with the string “12333333333335845″ to not turn into an exponential number when excel reads it?????
Plz reply asap..
One possibility, not prefered one, is to open file manually and in import dialog set field datatype as a string.
This will allow you to save any formatting including leading 0.
And when later you will work with VBA you can always use CINT to convert vallues back to integer for manipulation…
If I save an Excel file to *.cvs where 1 column is English and 1 column is Chinese, when I re-open the *.cvs file shouldn’t the Chinese column still display in Chinese characters?
They are not. Instead they are weird symbol characters. I have tried to create the *.cvs save while both in Chinese regional language settings on 1st and 3rd tabs, and also tried while in English on 1st and 3rd tabs. Rebooted after each language settings change.
Any suggestions?
Hi I’ve been reading lots of posts trying to find a solution to my problem. I have 32,768 entries in a CSV file, which I generated by approximating a sine wave. I’m now trying to sort the single column into an array of 16×2048. That’s 16 columns by 2048 rows. Ideally i would take A1-A16 and make that Row1, and then A16-A32 and make that Row2 etc.. until all values in the CSV are organized into rows of 16 numbers.
I’m also not familiar with VB or VBA.
If anyone can suggest a method for doing this it would be greatly appreciated.
@Camosun Student,
Test this macro out on a copy of your data to make sure it works correctly before implementing it permanently (you cannot Undo the results of a macro). To install the macro, press Alt+F11 to go into the VB Editor, click Insert/Module from its menu bar and then copy/paste the following code into the code window that appeared. The only thing you have to do before using the macro is change (if necessary) three of the Const statement assignments to match your actual conditions… they are StartRow, DataCol and WorksheetName. After that, just go back to your worksheet and press Alt+F8 to evoke the Macro DialogBox, select ConvertCVScolumn from the list and click the Run button. Here is the macro code for you to copy/paste…
Dim X As Long, LastRow As Long, Index As Long
Const StartRow As Long = 1
Const DataCol As String = "A"
Const WorksheetName As String = "Sheet1"
Const NumOfCols As Long = 16
LastRow = StartRow + 32767
With Worksheets(WorksheetName)
For X = StartRow To LastRow Step NumOfCols
Index = Index + 1
.Cells(Index, DataCol).Resize(1, NumOfCols) = WorksheetFunction. _
Transpose(.Cells(X, DataCol).Resize(NumOfCols))
Next
.Range(.Cells(Index + 1, DataCol), .Cells(LastRow, DataCol)).Clear
End With
End Sub
Hello,
I read most of the post and i did not find the answer to my question. if anyone could help it would be great
i am currently using Excel 2007. when I create this simple table:
colum 1 column2
Row1 1
Row2 1 2
Row3 1 2 3
Row4 1 2 3 4
Row5 1 2 3 4 5
I save it as CSV and when i open it with a text editor i get the following
1,,,,
1,2,,,
1,2,3,,
1,2,3,4,
1,2,3,4,5
as you can see Excel adds some extra commas add the end of every row except the last one
How do i get rid of this behavior
thank you
samyb: I think you’d have to use something like this
http://www.dailydoseofexcel.com/archives/2004/11/09/roll-your-own-csv/
and check for and ignore the blank cells. Email me at dkusleika@gmail.com with your data if you need help writing the macro.
To keep leading zeros
Create 2 variables
String delim = “=\”";
String delim2 = “\”";
Then enclose your field
delim+ yourfieldname + delim2
Hello,
found a few people with problems similar to mine but didnt find a solution.
I need to convert to .csv from MS Excel 2007 that has some Japanese characters.
The japanese characters are replaced by ??? in the csv. Somehow I need to set the encoding to Unicode/UTF-8 while saving as .csv
Can someone please help?
Just write three bytes at the begining of CSV file (0xEF 0xBB 0xBF). That makes Excel to display unicode characters. In ASP.NET: Response.BinaryWrite(new byte[] { 0xEF, 0xBB, 0xBF });