Import all data as TEXT?

  • Thread starter Thread starter A Nony Mouse
  • Start date Start date
A

A Nony Mouse

Hi all...

Long-time lurker here.

On a regular basis, I need to import large data sets into excel that are CSV
or fixed-width files. A number of the columns in these files get 'jumbled'
by Excel if I don't set the appropriate columns to be imported as 'text'.

My question is: Is there a way to tell excel to import/open *all* columns
in a give file as text automatically? Some of these data sets have a lot of
columns, and it takes *FOREVER* to set each column individually to text
using the little import screen (can only select one column @ a time).

Thanks, Mouse
 
Hey Mouse,

Set up a macro with the opentext method, using a FieldInfo input array where
each of the second values is a 2: use as many as you have columns. This
example is for 7 columns.

Workbooks.OpenText Filename:="C:\Excel\text.prn", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1,
2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2),
Array(7, 2))

HTH,
Bernie
MS Excel MVP
 
If you're doing this stuff manually, when you get to that "define your fields"
dialog on the text to columns wizard, you can click on the first field, use the
bottom scrollbar and go to the far right. Then shift click.

This selects all the fields and you can select Text.

If you're going to use a macro, you've got a different problem. Excel's VBA
likes to ignore the code defining fields when the input file has a .csv
extension.

Bernie changed the input file to .PRN to avoid the problem.

If renaming the file is ok, you could use this alternative for the comma
delimited files:

Option Explicit
Sub testme02()

Dim myFileName As Variant
Dim myArray(1 To 256, 1 To 2) As Long
Dim iCtr As Long

myFileName = Application.GetOpenFilename("Txt Files,*.txt")
If myFileName = False Then
Exit Sub
End If

For iCtr = 1 To 256
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 2
Next iCtr

Workbooks.OpenText Filename:=myFileName, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=myArray

End Sub

(I renamed my .csv to .txt for this to work. And since there's only 256
columns, that's how big my array is.)

====
For fixed width files--if all the files are the same structure, you can record a
macro when you do one and modify that code to ask for the filename to import.

In fact, you can steal some of the code above and just plop your opentext code
into it.
 
Back
Top