Comma Separated Values, .CSV files

Importing text data as Comma Separated Variable (CSV) files

Location: http://www.mvps.org/dmcritchie/excel/csv.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Some HELP items to read more on this subject.

File format converters supplied with Microsoft Excel

Help -> Wizard --> csv:

When a CSV file is read in with the .csv extension it will be read in automatically by Excel. The same file with a .txt extension will be processed by the Text Import Wizard and you can manually process how the columns are handled. CSV files are are also referred to as flat files, ascii files, and spreadsheet files.

The fields in a .CSV file are separated by commas. If there is a comma within the field the field is enclosed in double quotes. Sometimes the field will be enclosed in double quotes when not necessary.

A .CSV file will be read in automatically by Excel which will determine what the format is.

To override this automatic determination you can rename or copy the file so that the extension is .TXT instead of .CSV so that the Text Import Wizard will be used.

Using the Text Import Wizard

The Text Import Wizard will process files having an extension of .TXT

From within Excel

File --> Open --> (specify your .TXT file)
The Text Import Wizard will be invoked
on the first window choose delimited and Windows ( don't know what Mac, Win, OS/2 differences would actually be)
on the second window choose tab and comma (it is the comma that is important)
The text qualifier should show double quote (")
on the third window you make your changes for text, by selecting the column and choosing the text radio button, for such things as inventory numbers, phone numbers, and zip codes. You can alternatively choose among a choice of date formats.
When completed, hit the Finish key.

Fixing the column widths

On the column widths. If you don't have data spanning cells you should be able to do quite well with formatting the column to fit the data.

Select all button, which is the rectangular box in the upper left corner -- (or Edit --> Select All )
Double-click on the column grid line -- (or Format --> Column --> width
Close Macro Recording

Using the OpenText Method to automate importing of a file

Using the OpenText Method in a macro for specialized importing of a file

Bypassing the Text Import Wizard for customized Formatting on in Conversion of .csv or .txt file containing phone numbers and zip codes to used as text not numbers can be accomplished using the OpenText method.

Turn on the macro recorder.
Open your .TXT file which invokes the Text Import Wizard, do the necessary things there. Select the entire sheet to adjust the columns to fit. Stop recording. You will get a macro similar to the following once you change the macro name.

Sub KarenCSV()
  Workbooks.OpenText Filename:="C:/temp/csv.txt", Origin:=xlWindows, _
     StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
     xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _
     :=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
     :=Array(Array(1, 1), Array(2, 2), Array(3, 1), Array(4, 2))
  Cells.Select
  Cells.EntireColumn.AutoFit
End Sub

There are probably much better ways of implementing this and creating a workbook but the following shows that it works.

Open an empty worksheet.
Invoke the macro
Tools --> Macro --> KarenCSV
which will generate a new workbook
Select ALL of active cells (Ctrl+shift+end) --> Ctrl+C (copy)
exit the new workbook without saving
paste special into the original workbook
Edit --> Paste Special --> all

Background and additional information:

I did a search on newsgroup archives and I think I found what you need.

on UNIX and phone, but I will list a better search below.

HELP --> INDEX --> opentext

(see also texttocolumns method)

See Google Advanced Groups Search -- http://groups.google.com/advanced_group_search
Find messages with all of the words: opentext fieldinfo
Newsgroup: *excel*
More information on Google and newsgroups

One of the replies in the newsgroup archives suggested turning on the Macro Recorder which simplifies the process of creation and understanding of how to implement it.

An Alternate Solution -- Don't know how or if it works. If you first create your Excel file 'template' and designate the zip code field as text (not numbers) then you can paste your data and retain the zeros.

Create a CSV file

	A	B	C	D	E	F
1	Name	Items	Price	Total	Text.zip	Quote.zip
2	Alice	5000	14.15	70,750.00	00712	00712
3	Bunny	14	15.50	217.00	00708	00708
4	Carrie	100	14.80	1,480.00	00712	00712

Name,Items, Price , Total ,Text.zip,Quote.zip
Alice,5000 , 14.15 ," 70,750.00 ",00712,00712
Bunny,14 , 15.50 , 217.00 ,00708,00708
Carrie,100 , 14.80 ," 1,480.00 ",00712,00712

Create a CSV file without affecting the current sheet or workbook

Before starting be sure to save your workbook, so that you preserve your original file; whether making a copy manually, or with a program, because you do not want to have your CSV file be the only updated version.

The following will create a CSV file for use in another application with your data (instrument readings?). During the run, if you have old file in your temp directory with current sheetname you will be asked if you want to replace it (Yes), at the end your file is brought up in Notepad for your review.

Sub Macro20()
    Dim filename As String, RC As Long
    filename = "c:\temp\" & _
        Replace(Application.ActiveSheet.Name, " ", "") & ".csv"

    ActiveSheet.Copy         'creates a new workbook with one sheet
    Cells.Select             'select ALL cells
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select    'reduce to only once cell selected
                
    ActiveWorkbook.SaveAs filename:=filename, _
        FileFormat:=xlCSV, CreateBackup:=False
    '-- activeworkbook is the newly copied worksheet's workbook (.CSV)
    ActiveWorkbook.Close savechanges:=False
    RC = Shell("Notepad " & filename, 1)  'for your review
End Sub

The central part of the macro is based on recording a macro

Edit, Move or Copy, to book: (new book), and must place a check next to create a copy
Select ALL cells (Ctrl+A before Excel 2003, the gray button in any version)
Ctrl+G, Special, Blanks
Edit, Delete, Shift Left

ActiveSheet.Copy creates a new workbook, if you are wondering about copying a sheet (see sheets.htm), for example:

    Workbooks("Book1").Sheets("Sheet1").Copy Before:=Workbooks("Book2").Sheets(1) 
    ActiveSheet.Name = newName

would create a new worksheet

Restore Default Text to Columns defaults, CSV, macro repeated by Ron Rosenfeld, .excel, 2004-06-21
Exporting to Text Files and Importing Text Files both on same page to or from .CSV files, Chip Pearson.
Create CSV files from each sheet in selection, Dave Peterson, 2005-09-15, public.excel
Saveas, Save each worksheet as a separate workbook, macro to split up workbook into separate files strictly to see how big each worksheet really is.
Univ. of Illinois, How to Import data into Excel, http://www.dmi.uiuc.edu/excelimport.pdf

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions. Posting suggestions and netiquette. More information on newsgroups and searching newsgroups. Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).

This page was introduced on June 27, 2002. [My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie send email comments