Importing text data as Comma Separated Variable (CSV) files

Home page:
[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

  1. File --> Open --> (specify your .TXT file)
  2. The Text Import Wizard will be invoked
  3. on the first window choose delimited and Windows ( don't know what Mac, Win, OS/2 differences would actually be)
  4. on the second window choose tab and comma (it is the comma that is important)
  5. The text qualifier should show double quote (")
  6. 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.
  7. 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.

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.

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))
End Sub
There are probably much better ways of implementing this and creating a workbook but the following shows that it works.

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

1NameItems Price Total
2Alice5000 14.15 70,750.00 0071200712
3Bunny14 15.50 217.00 0070800708
4Carrie100 14.80 1,480.00 0071200712
Name,Items, Price , Total ,,
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.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 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
Excel questions not directly concerning my web pages are best directed to newsgroups
such as news:// 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

Copyright © 1997 - 2007,  F. David McRitchie,  All Rights Reserved