CheetoBandito
asked on
Export to CSV options in Excel 2007
Am I hallucinating or did they remove any or all options in 2007 in regards to CSV files? I'm trying to export a worksheet to csv with the normal settings, but I need each field to be enclosed by quotes. I see no options anywhere for this. Am I looking in the wrong place? I could've sworn that you could set these options in earlier versions.
ASKER
I need every field to have quotes around it regardless if there is a comma in the field. Is there a way to do this? Or should I look at a different office program.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Your the man.
I can't find how to do it either but right off Microsofts help and on their website it states you can from what I'm seeing. Plus if you goto http://www.howtogeek.com/howto/microsoft-office/make-excel-2007-always-save-in-excel-2003-format/ and check that out one of the options is to automatically save as CSV along with many others. I know the page is for changing the default save to 2003 but if you follow the directions and actually look at the list of available formats CSV is in there. So if it's not supported or doesn't work, why is it burried there?
You can save a Microsoft Office Excel 2007 file in another file format by clicking the Microsoft Office Button , and then clicking Save As. The file formats that are available in the Save As dialog box vary, depending on what type of sheet is active (a worksheet, chart sheet, or other type of sheet).
You can open a file that was created in another file format (in an earlier version of Microsoft Office Excel or in another program) by clicking the Microsoft Office Button , and then clicking Open. You will be prompted to save it to an Office Excel 2007 format, unless you open a workbook that is shared. For files that were created in an earlier version of Excel, you have the option to keep the original format.
Note Whenever you save a file in another file format, some of its formatting, data, and features may be lost. For more information, see Formatting and features that are not transferred in Excel file format conversions.
In this article
--------------------------
Excel formats
Text formats
Other formats
Clipboard formats
File formats that are not supported in Excel
--------------------------
Excel formats
Format Extension Description
Excel Workbook .xlsx The default Office Excel 2007 XML-based file format. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4.0 macro sheets (.xlm).
Excel Workbook (code) .xlsm The Office Excel 2007 XML-based and macro-enabled file format. Stores VBA macro code or Excel 4.0 macro sheets (.xlm).
Excel Binary Workbook .xlsb The Office Excel 2007 Binary file format (BIFF12).
Template .xltx The default Office Excel 2007 file format for an Excel template. Cannot store VBA macro code or Excel 4.0 macro sheets (.xlm).
Template (code) .xltm The Office Excel 2007 macro-enabled file format for an Excel template. Stores VBA macro code or Excel 4.0 macro sheets (.xlm).
Excel 97- Excel 2003 Workbook .xls The Excel 97 - Excel 2003 Binary file format (BIFF8).
Excel 97- Excel 2003 Template .xlt The Excel 97 - Excel 2003 Binary file format (BIFF8) for an Excel template.
Microsoft Excel 5.0/95 Workbook .xls The Excel 5.0/95 Binary file format (BIFF5).
XML Spreadsheet 2003 .xml XML Spreadsheet 2003 file format (XMLSS).
XML Data .xml XML Data format.
Excel Add-In .xlam The Office Excel 2007 XML-based and macro-enabled Add-In, a supplemental program that is designed to run additional code. Supports the use of VBA projects and Excel 4.0 macro sheets (.xlm).
Excel 97-2003 Add-In .xla The Excel 97-2003 Add-In, a supplemental program that is designed to run additional code. Supports the use of VBA projects.
Excel 4.0 Workbook .xlw An Excel 4.0 file format that saves only worksheets, chart sheets, and macro sheets. You can open a workbook in this file format in Office Excel 2007, but you cannot save an Excel file to this file format.
Text formats
Format Extension Description
Formatted Text (Space-delimited) .prn Lotus space-delimited format. Saves only the active sheet.
Text (Tab-delimited) .txt Saves a workbook as a tab-delimited text file for use on another Microsoft Windows operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
Text (Macintosh) .txt Saves a workbook as a tab-delimited text file for use on the Macintosh operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
Text (MS-DOS) .txt Saves a workbook as a tab-delimited text file for use on the MS-DOS operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
Unicode Text .txt Saves a workbook as Unicode text, a character encoding standard that was developed by the Unicode Consortium.
CSV (comma delimited) .csv Saves a workbook as a comma-delimited text file for use on another Windows operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
CSV (Macintosh) .csv Saves a workbook as a comma-delimited text file for use on the Macintosh operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
CSV (MS-DOS) .csv Saves a workbook as a comma-delimited text file for use on the MS-DOS operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
DIF .dif Data Interchange Format. Saves only the active sheet.
SYLK .slk Symbolic Link Format. Saves only the active sheet.
Note If you save a workbook in text format, all formatting is lost.
Top of Page
Other formats
Format Extension Description
DBF 3, DBF 4 .dbf dBase III and IV. You can open these files formats in Excel, but you cannot save an Excel file to dBase format.
Quattro Pro 5.0 (Win) .wb1 Quattro Pro version 5.0 for Windows. You can open Quattro Pro files in Excel by using a converter. You cannot save an Excel file to Quattro Pro format.
Quattro Pro 7.0 (Win) .wb3 Quattro Pro version 7.0 for Windows. You can open Quattro Pro files in Excel by using a converter. You cannot save an Excel file to Quattro Pro format.
Top of Page
Clipboard formats
You can paste data from the Microsoft Office Clipboard into Excel by using the Paste or Paste Special command (Home tab, Clipboard group, Paste button) if the Office Clipboard data is in one of the following formats.
Format Extension Clipboard type identifiers
Picture .wmf or .emf Pictures in Windows Metafile Format (WMF) or Windows Enhanced Metafile Format (EMF).
Note If you copy a Windows metafile picture from another program, Excel pastes the picture as an enhanced metafile.
Bitmap .bmp Pictures stored in Bitmap format (BMP).
Microsoft Excel file formats .xls Binary file formats for Excel versions 5.0/95 (BIFF5), Excel 97-2003 (BIFF8), and Office Excel 2007 (BIFF12).
SYLK .slk Symbolic Link Format.
DIF .dif Data Interchange Format.
Text (tab-delimited) .txt Tab-separated text format.
CSV (Comma-delimited) .csv Comma-separated values format.
Formatted text (Space-delimited) .rtf Rich Text Format (RTF). Only from Excel.
Embedded object .gif, .jpg, .doc, .xls, or .bmp Microsoft Excel objects, objects from properly registered programs that support OLE 2.0 (OwnerLink ), and Picture or another presentation format.
Linked object .gif, .jpg, .doc, .xls, or .bmp OwnerLink, ObjectLink , Link, Picture, or other format.
Office drawing object .emf Office drawing object format or Picture (Windows enhanced metafile format, EMF).
Text .txt Display Text, OEM Text.
HTML .htm Hypertext Markup Language.
Note When you copy text from another program, Excel pastes the text in HTML format, regardless of the format of the original text.
Top of Page
File formats that are not supported in Excel
The following file formats are no longer supported in Office Excel 2007. You cannot open or save files in these file formats.
Format Extension Clipboard type identifiers
Excel Chart .xlc Excel 2.0, 3.0, and 2.x file formats
WK1, FMT, WK2, WK3, FM3, WK4 .wk1, .wk2, .wk3, .wk4, .wks Lotus 1-2-3 file formats (all versions)
Microsoft Works .wks Microsoft Works file format (all versions)
WQ1 .wq1 Quatro Pro for MS-DOS file format
DBF 2 .dbf DBASE II file format
Tips for file formats that are not supported by Excel
If a file format that you want to use is not supported in Excel, you can try the following:
Search the World Wide Web (WWW) for a company that makes file format converters for file formats that are not supported in Excel.
Save to a file format that another program supports. For example, you may want to import your spreadsheet into another program that does not support the Excel file format. But the other program may be able to import another supported file format, such as an XML spreadsheet or a text file format. In this case, you can save your workbook to the XML spreadsheet format, and then from the other program, import the XML file.
Source:
http://office.microsoft.com/en-us/excel/HP100141031033.aspx#TextFormats
You can save a Microsoft Office Excel 2007 file in another file format by clicking the Microsoft Office Button , and then clicking Save As. The file formats that are available in the Save As dialog box vary, depending on what type of sheet is active (a worksheet, chart sheet, or other type of sheet).
You can open a file that was created in another file format (in an earlier version of Microsoft Office Excel or in another program) by clicking the Microsoft Office Button , and then clicking Open. You will be prompted to save it to an Office Excel 2007 format, unless you open a workbook that is shared. For files that were created in an earlier version of Excel, you have the option to keep the original format.
Note Whenever you save a file in another file format, some of its formatting, data, and features may be lost. For more information, see Formatting and features that are not transferred in Excel file format conversions.
In this article
--------------------------
Excel formats
Text formats
Other formats
Clipboard formats
File formats that are not supported in Excel
--------------------------
Excel formats
Format Extension Description
Excel Workbook .xlsx The default Office Excel 2007 XML-based file format. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4.0 macro sheets (.xlm).
Excel Workbook (code) .xlsm The Office Excel 2007 XML-based and macro-enabled file format. Stores VBA macro code or Excel 4.0 macro sheets (.xlm).
Excel Binary Workbook .xlsb The Office Excel 2007 Binary file format (BIFF12).
Template .xltx The default Office Excel 2007 file format for an Excel template. Cannot store VBA macro code or Excel 4.0 macro sheets (.xlm).
Template (code) .xltm The Office Excel 2007 macro-enabled file format for an Excel template. Stores VBA macro code or Excel 4.0 macro sheets (.xlm).
Excel 97- Excel 2003 Workbook .xls The Excel 97 - Excel 2003 Binary file format (BIFF8).
Excel 97- Excel 2003 Template .xlt The Excel 97 - Excel 2003 Binary file format (BIFF8) for an Excel template.
Microsoft Excel 5.0/95 Workbook .xls The Excel 5.0/95 Binary file format (BIFF5).
XML Spreadsheet 2003 .xml XML Spreadsheet 2003 file format (XMLSS).
XML Data .xml XML Data format.
Excel Add-In .xlam The Office Excel 2007 XML-based and macro-enabled Add-In, a supplemental program that is designed to run additional code. Supports the use of VBA projects and Excel 4.0 macro sheets (.xlm).
Excel 97-2003 Add-In .xla The Excel 97-2003 Add-In, a supplemental program that is designed to run additional code. Supports the use of VBA projects.
Excel 4.0 Workbook .xlw An Excel 4.0 file format that saves only worksheets, chart sheets, and macro sheets. You can open a workbook in this file format in Office Excel 2007, but you cannot save an Excel file to this file format.
Text formats
Format Extension Description
Formatted Text (Space-delimited) .prn Lotus space-delimited format. Saves only the active sheet.
Text (Tab-delimited) .txt Saves a workbook as a tab-delimited text file for use on another Microsoft Windows operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
Text (Macintosh) .txt Saves a workbook as a tab-delimited text file for use on the Macintosh operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
Text (MS-DOS) .txt Saves a workbook as a tab-delimited text file for use on the MS-DOS operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
Unicode Text .txt Saves a workbook as Unicode text, a character encoding standard that was developed by the Unicode Consortium.
CSV (comma delimited) .csv Saves a workbook as a comma-delimited text file for use on another Windows operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
CSV (Macintosh) .csv Saves a workbook as a comma-delimited text file for use on the Macintosh operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
CSV (MS-DOS) .csv Saves a workbook as a comma-delimited text file for use on the MS-DOS operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
DIF .dif Data Interchange Format. Saves only the active sheet.
SYLK .slk Symbolic Link Format. Saves only the active sheet.
Note If you save a workbook in text format, all formatting is lost.
Top of Page
Other formats
Format Extension Description
DBF 3, DBF 4 .dbf dBase III and IV. You can open these files formats in Excel, but you cannot save an Excel file to dBase format.
Quattro Pro 5.0 (Win) .wb1 Quattro Pro version 5.0 for Windows. You can open Quattro Pro files in Excel by using a converter. You cannot save an Excel file to Quattro Pro format.
Quattro Pro 7.0 (Win) .wb3 Quattro Pro version 7.0 for Windows. You can open Quattro Pro files in Excel by using a converter. You cannot save an Excel file to Quattro Pro format.
Top of Page
Clipboard formats
You can paste data from the Microsoft Office Clipboard into Excel by using the Paste or Paste Special command (Home tab, Clipboard group, Paste button) if the Office Clipboard data is in one of the following formats.
Format Extension Clipboard type identifiers
Picture .wmf or .emf Pictures in Windows Metafile Format (WMF) or Windows Enhanced Metafile Format (EMF).
Note If you copy a Windows metafile picture from another program, Excel pastes the picture as an enhanced metafile.
Bitmap .bmp Pictures stored in Bitmap format (BMP).
Microsoft Excel file formats .xls Binary file formats for Excel versions 5.0/95 (BIFF5), Excel 97-2003 (BIFF8), and Office Excel 2007 (BIFF12).
SYLK .slk Symbolic Link Format.
DIF .dif Data Interchange Format.
Text (tab-delimited) .txt Tab-separated text format.
CSV (Comma-delimited) .csv Comma-separated values format.
Formatted text (Space-delimited) .rtf Rich Text Format (RTF). Only from Excel.
Embedded object .gif, .jpg, .doc, .xls, or .bmp Microsoft Excel objects, objects from properly registered programs that support OLE 2.0 (OwnerLink ), and Picture or another presentation format.
Linked object .gif, .jpg, .doc, .xls, or .bmp OwnerLink, ObjectLink , Link, Picture, or other format.
Office drawing object .emf Office drawing object format or Picture (Windows enhanced metafile format, EMF).
Text .txt Display Text, OEM Text.
HTML .htm Hypertext Markup Language.
Note When you copy text from another program, Excel pastes the text in HTML format, regardless of the format of the original text.
Top of Page
File formats that are not supported in Excel
The following file formats are no longer supported in Office Excel 2007. You cannot open or save files in these file formats.
Format Extension Clipboard type identifiers
Excel Chart .xlc Excel 2.0, 3.0, and 2.x file formats
WK1, FMT, WK2, WK3, FM3, WK4 .wk1, .wk2, .wk3, .wk4, .wks Lotus 1-2-3 file formats (all versions)
Microsoft Works .wks Microsoft Works file format (all versions)
WQ1 .wq1 Quatro Pro for MS-DOS file format
DBF 2 .dbf DBASE II file format
Tips for file formats that are not supported by Excel
If a file format that you want to use is not supported in Excel, you can try the following:
Search the World Wide Web (WWW) for a company that makes file format converters for file formats that are not supported in Excel.
Save to a file format that another program supports. For example, you may want to import your spreadsheet into another program that does not support the Excel file format. But the other program may be able to import another supported file format, such as an XML spreadsheet or a text file format. In this case, you can save your workbook to the XML spreadsheet format, and then from the other program, import the XML file.
Source:
http://office.microsoft.com/en-us/excel/HP100141031033.aspx#TextFormats
Great 2 seconds after I post that I figure it out... I just clicked the start button and convert is right off it. You click that and change the type to CSV.
I just happened upon this as I was looking up the same thing, and the Accepted Solution is convoluted. The Open Discussion answer is difficult to wade through.
I finally found the solution with Save As --> Other Formats
In the Save as Type dropdown, scroll about half way down the scroll bar and you'll find CSV (Comma delimited) (*.csv)
KISS --> Keep it Simple Señor.
I finally found the solution with Save As --> Other Formats
In the Save as Type dropdown, scroll about half way down the scroll bar and you'll find CSV (Comma delimited) (*.csv)
KISS --> Keep it Simple Señor.
ASKER
serialband, Yeah that works fine if you're okay with the default output. But I needed to have each field enclosed in quotes. Something Access allows me to do, but for what I could tell, Excel 2007 does not.
Excel does put quotes around any text value which contains a comma - to prevent the comma from being treated as a delimiter.