Workbooks.OpenText method, FieldInfo parameter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am struggling to understand the FieldInfo parameter in the
Workbooks.OpenText method. I have a csv file that I am opening, I have no
problem dumping the whole file into a sheet but when I try to use the
FieldInfo parameter to specify which columns to ignore and which should be
treated as text (to preserve a leading 0) I get lost. Can anyone point me to
a worked example or the like, or explain to me in laymans terms how to go
about it?

Regards,
 
When VBA (not excel) sees that *.csv filename, it ignores your fieldinfo stuff.

If you rename your .csv to .txt (or almost anything else), then your macro will
work ok.
 
I love continuity!

Thanks Dave. I have 22 columns to bring in, now my understanding is that if
I create an array like

Dim ColumnFormats(1 To 22, 1 To 2) As Variant

Then put 1 to 22 in position 1 column 1 (using option base 1) and then
xlTextFormat, or xlSkipColumn (or whatever is required) in column 2 for the
relevant position and then include FieldInfo:=ColumnFormats in the parameter
list

Then it should all work, or have I got myself confused?

Regards,
 
ok,

a little more understanding happening here, the list of parameters are
referred to by an index number and not a string. Still not quite there yet.
Here is my code. I have 22 columns in the text file (not CSV) and I have 4
different data types that I want to specify.

Option Explicit
Sub importdata()
Dim myFileName
Dim ColumnsDesired
Dim DataTypeArray
Dim x
Dim ColumnArray(0 To 21, 0 To 3)

myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If myFileName = False Then
MsgBox "Try Later"
Exit Sub
End If

' fill the column and data type information
ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22)
DataTypeArray = Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0,
9, 9, 9, 9, 9)

' populate the array for fieldinfo
For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
ColumnArray(x, 1) = ColumnsDesired(x)
ColumnArray(x, 2) = DataTypeArray(x)
Next x

' open the file
Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
Comma:=True, FieldInfo:=ColumnArray

End Sub
 
You have a couple of problems in your code.

First, the easy one:
Dim ColumnArray(0 To 21, 0 To 3)
should be:
Dim ColumnArray(0 To 21, 1 to 2)

And those columnArrays have to be valid--0 isn't a valid choice.

You can use 1-10 (either as numbers or as xlconstants)

XlColumnDataType can be one of these XlColumnDataType constants.

(I think in earlier versions of the help, they actually gave the numbers instead
of xl's constants):

1 xlGeneralFormat. General
2 xlTextFormat. Text
3 xlMDYFormat. MDY Date
4 xlDMYFormat. DMY Date
5 xlYMDFormat. YMD Date
6 xlMYDFormat. MYD Date
7 xlDYMFormat. DYM Date
8 xlYDMFormat. YDM Date
10 xlEMDFormat. EMD Date (Taiwanese era dates are used)
9 xlSkipColumn. Skip Column

I don't think I could guess what you want for each field:

DataTypeArray _
= Array(0, 9, 3, 0, 0, 1, 0, 0, 0, 9, 9, 9, 0, 0, 0, 9, 0, 9, 9, 9, 9, 9)

but those 0's can't be there.
 
Thanks Dave,

pointed me in the right direction. I assumed that the list of datatypes were
in numerical order and then when I read that xlSkipColumn was number 9 that
the number had to start from 0. All the 0's in that array were to ignore
columns.

Once again, thanks for your help.

Final code for anyone else that is interested is

Option Explicit
Sub importdata()
Dim myFileName
Dim ColumnsDesired
Dim DataTypeArray
Dim x
Dim ColumnArray(0 To 21, 1 To 2)

' open the file

myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If myFileName = False Then
MsgBox "Try Later"
Exit Sub
End If

' fill the column and data type information
ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22)
DataTypeArray = Array(1, 9, 3, 1, 1, 2, 1, 1, 1, 9, 9, 9, 1, 1, 1, 9, 1,
9, 9, 9, 9, 9)

' populate the array for fieldinfo
For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
ColumnArray(x, 1) = ColumnsDesired(x)
ColumnArray(x, 2) = DataTypeArray(x)
Next x

' open the file
Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
Comma:=True, FieldInfo:=ColumnArray

End Sub
 
All the 0's were to bring that field in as General, right--not skip the column?

(Your code does things differently from what your text reads -- but I bet you're
happier with the code <vbg>.)


Thanks Dave,

pointed me in the right direction. I assumed that the list of datatypes were
in numerical order and then when I read that xlSkipColumn was number 9 that
the number had to start from 0. All the 0's in that array were to ignore
columns.

Once again, thanks for your help.

Final code for anyone else that is interested is

Option Explicit
Sub importdata()
Dim myFileName
Dim ColumnsDesired
Dim DataTypeArray
Dim x
Dim ColumnArray(0 To 21, 1 To 2)

' open the file

myFileName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If myFileName = False Then
MsgBox "Try Later"
Exit Sub
End If

' fill the column and data type information
ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15, 16, 17, 18, 19, 20, 21, 22)
DataTypeArray = Array(1, 9, 3, 1, 1, 2, 1, 1, 1, 9, 9, 9, 1, 1, 1, 9, 1,
9, 9, 9, 9, 9)

' populate the array for fieldinfo
For x = LBound(ColumnsDesired) To UBound(ColumnsDesired)
ColumnArray(x, 1) = ColumnsDesired(x)
ColumnArray(x, 2) = DataTypeArray(x)
Next x

' open the file
Workbooks.OpenText Filename:=myFileName, DataType:=xlDelimited,
Comma:=True, FieldInfo:=ColumnArray

End Sub
Click to expand...
 
Yes I know what I meant :) Gotta stop trying to do two things at once!

For anyone else reading this the 0's were to bring the field in as a
general, the 9's skip the field.
 
Back
Top