Solved

Excel 2007 - saving as a .csv no longer encases the fields in inverted commas

Posted on 2011-03-07
4
673 Views
Last Modified: 2012-06-27
I appreciate that this question has already been posed - see "Export to CSV options in Excel 2007", but I am looking for a better solution.

In earlier versions of Excel (before 2007) when you Saved As a .csv file the fields would be encased in inverted commas, e.g. "123","ABC","XYZ"    But now I get  123,ABC,XYZ

I need the inverted commas because the .csv file is imported into a Visual Basic program.  I don't want to make the import procedure any more complicated for my users, so adding Access to the mix isn't really an option.  Without reverting to Excel 2003, does anyone know of a workaround for this problem?

Thank you
Geoff
0
Comment
Question by:lybreeze
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 10

Expert Comment

by:Makrini
ID: 35062438
Something like this code will work.  Just change the range accordingly.  Note that this one will have "" around EVERY cell in the range

Sub runit()
    Write_CSV_File "myfile.csv", Sheets("Sheet1").Range("A1:Z5000")
End Sub

Sub Write_CSV_File(ByVal filename As String, output_data)
'
'
Dim Lastrow, lastcolumn, rowcount, columncount As Long
 
Lastrow = UBound(output_data)
lastcolumn = UBound(Application.Transpose(output_data))
'
    
    Open filename For Output As #1      'open CSV file
        For rowcount = 1 To Lastrow
            For columncount = 1 To lastcolumn
                output_data(rowcount, columncount) = """" & output_data(rowcount, columncount) & """"
            Next columncount
            Print #1, Join(Application.WorksheetFunction.Index(output_data, rowcount, 0), ",")
        Next rowcount
    Close #1    'close CSV file

End Sub

Open in new window

0
 
LVL 10

Accepted Solution

by:
Makrini earned 500 total points
ID: 35062466
By the way - usually Excel will assume inverted commas if there are spaces in the cell.  This is not ideal for CSV and I NEVER trust Excel to write my csv files.  This is the reason for the code.

  I use a modified version of this with a text ini file for each csv I write.  The text file contains the formats for all the columns I wish to format.  In this way I can also write out dates in the format I actually want to...
0
 

Author Comment

by:lybreeze
ID: 35082194
Hi Makrini,
Many thanks for your reply.  I guess that I would need to run this as a macro.  A problem will be that the file has a different name every time.  However, I'll give it a go and report back.

What a nuisance that we have to do this when it worked perfectly in Excel 2000/03

Thanks again
Geoff
0
 

Author Closing Comment

by:lybreeze
ID: 35137322
Thank you for your replies.  However, I think that I got confused with the way that Excel works in respect of .csv files, and there is not a difference between Excel 2003 and 2007 in this regard.

I have made the following observations :-
*   a .csv file created or saved by any version of Excel will only have double-quotes around those fields that contain a comma, e.g. an Excel row of    ABC    123    WX,YZ    will become   ABC,123,"WX,YZ"  in a .csv file.  This happens regardless of whether the original file was a .xls or a .csv file
 
*   if a field already contains a double-quote then Excel will duplicate those double-quotes in a .csv file, so   "Chez Nous"    Albert Square      in Excel, becomes    ""Chez Nous"",Albert Square    in .csv
 
*   if an Excel row contains empty cells at the end, Excel will normally add commas to indicate that there is no data in those cells (it also does this with empty cells in the middle of a row)
 
*   the 3 methods outlined above mean that a .csv file can usually be imported into a VB program without a problem
 
*   however, sometimes Excel does not insert the commas at the end to indicate empty cells. This often happens when the spreadsheet is large and contains rows of varying lengths.  This can cause VB imports to fail because the number of fields imported is not the number that was expected.
 
*   another problem is that Excel will convert telephone numbers into a numeric format if there isn't a space in the middle of the number, and it will therefore strip off the leading zero
 
*  to conclude, it is much better if the app. that creates the .csv file does so with every field encased in double-quotes, including any empty fields at the end of the record.  The only problem then is if there are any double-quotes within any of the fields, but these should be programmed out at source.  VB programs can import such a .csv file provided there are no double-quotes within any of the fields

Makrini's code could certainly help in cases where you don't have control over the app. that creates the .csv file, as it would then encase each field in double-quotes.  I guess you would run it as a macro?

Geoff
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question