How to specify text format when importing CSV files?

[Guest]

I would like to prevent Excel from attempting to interpret the data type when
importing CSV files, and instead always format all fields as Text. For
example, even if a csv field looks like a date, I want it formatted as Text.
When importing a file ending in .txt, the Text Import Wizard lets me
interactively specify each field type individually. That works OK except
when I import files with many fields (100+), it is a burden to click on each
field individually and specify text. Can I make Text format the default when
importing txt and csv files? Thanks.

 

[Debra Dalgleish]

I don't know of any way to change the default settings.

However, if you have lots of fields, you could format all of them at the
same time:

In Step 3 of the Text Import Wizard, click on the header of the first
column, to select it
Scroll right, to see the last column
Hold the Shift key, and click on its heading, to select all the columns
Under 'Column data format', select Text
Click Finish

 

[Dave Peterson]

I do this when I have this situation (it's useful if you import the same file
format repeatedly).

1. Rename the file to .txt (from .csv)
2. Start a new workbook
3. tools|macro|record new macro (record in this new workbook)
4. file|open your .txt file
5. Run that text to columns wizard and divide your records into 100 fields
6. continue formatting the data (add headers/subtotals/page layout/filters...)
7. Stop recording
8. Now close the .txt file and save that workbook with your recorded macro.
9. Show the Forms toolbar (view|toolbars|forms)
10. Draw a nice big old button on that worksheet. Assign your macro to it.

If the filename never changes, you may not need to change the code at all.

If the filename could vary, you could tweak your code to ask the user to select
a file.

When you recorded your macro, you got something that looked like:

Option Explicit
Sub Macro1()

Workbooks.OpenText Filename:="C:\myfile.txt", Origin:=437, StartRow:=1,...

End Sub

Well, instead of having your filename in the code, you can give the user a
chance to pick it themselves (take a look at getopenfilename in VBA's help):

Sub macro1A()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

You may have to tweak some other code--depending on how much other stuff you did
in your recorded macro.

If you have trouble, post back with a question.

========

So the point is: when you want to import that 100 field .txt file (not .csv),
you open the workbook with the macro and click that giant button!

tada!