Free Microsoft Excel 2013 Quick Reference

CSV File with Double quotes

I want to create a CSV file from excel. But the additional requirement is I want value in each cell to be enclosed with double quotes. For eg
Now I am getting the casv format as
Value1,value2,value3

This should be "Value1","Value2","Value3"

Is there a way in excel to get the csv file created in the above way.

Please help.

Thanks & Regards
Anand


Post your answer or comment

comments powered by Disqus
When I create csv files, I need to have all columns wrapped in double-quotes. Currently, Excel only wraps those columns that contain commas in double-quotes. I have access to both Excel 2003 and 2007. Is there a way to configure Excel to wrap all columns and if not, is there some VB magic that I can run to do this?

I tried to search the forum to see if this had already been discussed, but did not find anything.

Thanks,

Jack

I have a macro that exports a file to a CSV file with the fields enclosed in quotes, however there in items in some of the fields that are already quoted is there a way to modify this macro to ignore quoted items:

for ex: <ul class="test></ul>
is becoming
<ul class=""test""></ul>

Public Sub OutputQuotedCSV()
Const QSTR As String = """"
Dim myRecord As Range
Dim myField As Range
Dim vFilename As Variant
Dim nFileNum As Long
Dim sOut As String

'Get a filename to save as
vFilename = Application.GetSaveAsFilename(filefilter:="Microsoft CSV files,*.csv", _
Title:="Save as CSV with fields in double quotes")

If vFilename = False Then Exit Sub 'User chose Cancel

nFileNum = FreeFile
Open vFilename For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, 256).End(xlToLeft))
sOut = sOut & "," & QSTR & _
Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub
nevermind it works correctly

I'm trying to create an csv type file with double quote around selected cell
values

I am a little lost in making this work.

I found a few examples around the web of exporting to CSV with Double Quotes. For example:

http://www.markinns.com/articles/ful..._double_quotes

http://windowssecrets.com/forums/sho...-around-values

But I can't come up with a way to export to CSV with the first column not receiving the double quotes.

Any thoughts?

I have two CSV files that I would like to edit through Excel VBA.

Both files have too many lines to be shown in Excel though, so I can't just use cell rerences.

Every so many lines I want to delete a row and insert a row from one CSV file to the other.

I could easily write a loop do this with cell references, but since all the lines aren't shown I can't do that.

I have no idea how to reference all the rows in the CSV file with VBA when most of them aren't viewable on the worksheet.

Any references or thoughts would be appreciated!

Hi

I'm using an IF function that replaces [square brackets] with "double quotes" (it has to be double quotes)

How do I specify that the double quote character is inserted as a piece of text without Excel seeing the quotes as a command to end the string of text to be replaced?

Hello,

In a Excel Macro (VBA) I'm trying to save a ".xls" file into a ".csv
file with SEMICOLON ";" delimiters. There is the code :

ActiveWorkbook.SaveAs FileName:="test", _
FileFormat:=xlCSV, CreateBackup:=False

But when I open the result file, the delimiters are comma ",". Do yo
have a solution to save the file with semicolon ";" ?

Thank you very much in advance

--
Message posted from http://www.ExcelForum.com

need to export part of row as csv file with column e plus .bom extension
as file name.

a b c d e f
g h i j k l m n o p q r
s t u

done1 11/28/05 312944 862423 599-020d p a g
32 10/25/06
g:sigmasndata55parts55 done2 action cell

cells would be s,c,e,h,i,f,d,,,j,,,,t

notice cell j in csv file will have to be date as shown 2006/10/06
instead of the 10/25/06 that excel file shows in row,, because this is how
the date is inserted into list it is pasted as text from a as400 dat file ..
g:sigmasndata55parts55312944599-020d,g,32,p,862423,,,2006/10/25,,,,done2file would be saved as c:tmp599-020d.bomexample cells in column u could have the action code in each cell pasteddown sheet ..like row 1 click on cell in u1 and csv file would be created of row 1like row 2 click on cell in u2 and csv file would be created of row 2rod

Hi all,

I'm trying to open a CSV-File with double-quotation mark and comma.
Afterwards i need to make some checkings in this file and save the correct
data again.

The source data looks like this: "019292","Placards","1"
The result when saving is this: 19292,Placards,1

Leading zero is missing and the double quotation mark is missing.

My code is this:

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(xlsdatei, xlMSDOS, 1, xlDelimited,
xlTextQualifierDoubleQuote, False, False, True, False, False, False)
xlBook.SaveAs FileName:="C:test.csv", FileFormat:=xlCSV
xlBook.Close SaveChanges:=False

Please help. Thank you!

Regards
kaiserlich

Hi all,

I have to open csv files with possibly more than 65000 rows of data in them,
and all the rows greater than 1 worksheet have to be put on the next
sheet...when that is full I have to add another sheet and so on...

Due to the limitations of Excel, I can't even tell the text Import
(Querytables) method to start at row 65000 as the textFileStartRow parameter
is supposed to be an integer (DOH !) - who came up with that one?

So, what is the best option, open the files using OLEDB Text Provider in ADO
and load from recordset to read them in chunks of 65000 odd records, or is
there a better way?

Or should I use the Textfile Import Method, and read it in in chunks of say
30000 by setting the textFileStartRow property to current Value + 30000 for
each iteration until completed?

thanks for any help or ideas or sympathy...

Philip

I have a csv file with 100,000 records on it, I know excel only downloads up
to a little over 65,000, and I have tried the Wizard, but it won't let me
export specific rows after about 32,000. I have also tried to change the csv
file to a Word file and cut it into two seperate files, but then Excel won't
recognise the format. There must be a way!
PS I don't have Access

How can I export a CSV file with semicolon as the delimiter. By default it sets it to a comma. I do not want the comma. Thanks in advance for you help.

ActiveWorkbook.SaveAs Filename:="C:test.csv", FileFormat:=xlCSV, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Hi,

I have 4 csv files with 1000000 rows in all of them.

I need to somehow sort all these at one place and by A-Z

Is this possible?

Hi,
I'm writing a macro to automate the integration process between payroll and invoicing software but the invoicing program requires text fields to be imported with double quotes around them.

when i use the code:
ActiveCell.FormulsR1C1 = "H"

and open the .csv file in notepad it displays ....,H,....

If I write:
ActiveCell.FormulsR1C1 = """H""" or
ActiveCell.FormulsR1C1 = Chr(34) & "H" & Chr(34)

it displays .......,"""H""",.........

it looks fine in Excel ("H") but the invoicing software requires the .csv to display ......,"H",........ and I can't seem to figure it out

Suggestions would be much appreciated

Chris

Hi there guys
If I have a xpreadsheet how can I save as a csv file with each column enclosed in quotes?? I can save the file as a csv but the fields are not enclosed in double quotes like below.

"TOM CLARK","58 MAIN ST.","NORMANTOWN","CA","83345"

I have an Excel 2003 SP2 file with columns that contain text that includes
double quotation marks. For example: Select "Yes" to process this form.

When I save the file as a CSV file, the double quotation marks are saved.
However, when I ftp the file to a Unix server, the double quotation marks are
removed from the file.

So....how can I preserve the double quotaion marks in the file when I ftp it
to a Unix server? The only soltuion I have found is to replace each double
quotation mark with 2 single quote marks prior to ftping it, then replacing
the 2 single quote marks with a double quotation mark on the Unix server
after ftping it.

Thanks for your help!
Shannon

I need to be able to save a spreadsheet as a csv file, using comma delimited
but also having double quotes around each field. I'm running Excel 2000, on
Windows XP sp2. Any help would be appreciated.

Worker in office has been "saving as" a csv file for well over a year. Suddenly when I save it now it creates a csv file that does NOT have the quotes around each field as it had before. Not much information on Google about this.

Even tried saving with CSV for Windows and CSV for Dos and both with and without the Worksheet.

Confused.

Thanks,
Paul

I have a lot of csv files to import. When I double click on any one of them manually using explorer excel opens them perfectly. However, when I loop through the same files using a script that incorporates a parsing array I recorded with excel's import tool, any file where one of the 'columns' has extra commas in it excel recognizes them and splits the data for that row into additional columns. For example, one column is for 'company name' and many companies have commas in their name. Can anybody tell me if it possible in VB to have excel open the files the same way it does natively as opposed to the hard coded array method created by the import tool?

I am having difficulty parsing the attached csv file into rows/columns.

The rows seem to be divided by a delimiter Excel cannot read. When I open the file 'as is', the entire contents of the file appear in cell A1 and the row delimiter looks like a little square with a question mark in it. The columns are delimited with "," (a comma surrounded by quotes).

I have tried using the text to column wizard with limited success. It reads the commas and quotes fine but not the row delimiter so the headers and column data don't line up after I'm done. I tried copying and pasting the mystery character into the wizard as well but it won't let me select it.

Can anyone tell me how to parse the file properly? thanks!

note - just realized the site won't allow me to upload a csv file so I have saved it in excel and uploaded that

I have the following data in an excel file in 3 different cells
name address city

I want to create a CSV file that reads
"name","address","city"

Is there an easy way to do this.

I am working with Excel 2000

Thanks

I have data in an excel worksheet. I have to save it as a .CSV file (comma separated values) to supply the data in ASCII format. A requirement is that alphanumeric fields must be displayed in "double quotes" and numeric not. Excel automatically will put double quotes (when you open the CSV file, say, in Wordpad) only around a text string containing a comma. I have several fields/cells which must be alphanumeric but obviously do not have commas in the text. How do I get the text cell to either (1) display with double quotes in excel (without having to phyically type them into each cell - there are thousands!) or to display with double quotes when opening the file as a CSV or text file.
HELP

Hello,

I'm using excel 2003 and i'm having problem with csv file that contains
values such as 123e4 since excel interpets it as 123*10^4. Is there any way
that I can write this number so excel will leave it as a string ?

I'm aware of the article
http://www.kbalertz.com/Q66033/Hexad....Imported.aspx
but the solution propose there to use the wizard is clumsy. I want to double
click on csv file and get the string.

Thanks, Eyal

I regularly use Excel to edit CSV files, since it is often easier to edit
these files in Excel's cell-based format as opposed to a plain text editor,
but in many respects Excel is not exactly easy to use with CSV files.

As an example, open a CSV file and make a change. Now try and shut down
Excel, saving your changes. You will get a series of several dialog boxes,
starting with a "Save As..." dialog, then a "Are you sure you want to
overwrite the file?" dialog, then the "Incompatible features" dialog, then
finally the program exits. This long stream of dialog boxes is pretty
annoying. Hitting "Save" before "Exit" allows one to skip the "Save As..."
dialog, but then you get another "Do you want to save your changes?" dialog
as soon as you try to exit.

Another issue revolves around templates. When opening a CSV file, Excel
seems to ignore default templates stored in XLSTART. This means it is
impossible to add your favorite formatting options to the default template,
so that they are available whenever you open a CSV file. In fact, when
opening a CSV file by double-clicking on it, it causes a problem inside of
Excel, where Excel will always ignore default templates, until you shut down
and restart Excel without double-clicking on a document. For example,
double-click on a CSV file. Then go to "File->New" and create a blank
workbook. The blank workbook will not use the default template stored in
XLSTART. You must shut down and restart Excel to get it to start using the
default template again.

Then there is the double-quote issue. This is more an issue caused by the
fact that there is no universal software standards body, so there is no
universal standard format for CSV files. But essentially, any field in a CSV
file that contains a double-quote or a comma has extra double-quotes placed
around it internally by Excel. These extra quotes can be seen by opening the
CSV file in a program like Notepad. Using double-quotes in this way allows
Excel to have commas inside of a field in a CSV file, which is good, but many
other programs that utilize CSV files do not do this. This makes CSV files
edited in Excel incompatible with many other programs, with no way of making
them compatible short of something like writing a custom Lisp routine to
translate between Microsoft Excel CSV and the "other" CSV format, where
double-quotes are not treated as special characters.

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


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