Meaning of specifications in the Text to column feature

Hi,

Im trying to programmatically open a textpad kind of file (and then save it
as excel file) and I recorded a macro for the same. The relevant line is
.....

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\abc\Desktop\dev11022.xls", Origin:=437,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

I wanted to understand
a) What "Origin" means
b) Is Startrow the row from which textpad will be read?
c) I want to export whole of the textpad in to excel without any exclusions
in such a way that each row in the texpad occupies a single cell in Excel.
In view of that would it be more safer if I remove the specification of
TextQualifier:=xlDoubleQuote. If yes, what value should I put following the
equal to sign in the text qualifier statement.
d) What does FieldInfo:=Array(1, 1). Based on my requirement mentioned in
point c) does the specification in Fieldinfo pose a danger for all my data
not getting transferred.
e) What does TrailingMinusNumbers:=True mean. Again does the trailing minus
number specification affect the amount and the way that gets stored in each
cell of the excel.


Thanks a lot,
Hari
India

Most of this is explained in the help file in great detail. I would start
there and then ask for explanation.

Most of this reflects the choices you made when you went throught the text
import wizard. For instance, at the start of the wizard is the Choice start
at line and a textbox. You entered 1 and it is recorded as

Startrow:=1

From you description, that is what you want.

For the remainder, begin in Help. But as an example, for me, help only
shows the values 1, 2, or 3 for Origin.

? xlMSDOS
3
? xlWindows
2
? xlMacintosh
1


You may have more because you don't have an English version of Excel
(assumed).

--
Regards,
Tom Ogilvy


"Hari" <excel_hari@hotmail.com> wrote in message
news:eSjqpmk%23EHA.936@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Im trying to programmatically open a textpad kind of file (and then save
it
> as excel file) and I recorded a macro for the same. The relevant line is
> ....
>
> Workbooks.OpenText Filename:= _
> "C:\Documents and Settings\abc\Desktop\dev11022.xls", Origin:=437,
> StartRow _
> :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
> Comma:=False _
> , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
> TrailingMinusNumbers:=True
>
> I wanted to understand
> a) What "Origin" means
> b) Is Startrow the row from which textpad will be read?
> c) I want to export whole of the textpad in to excel without any
exclusions
> in such a way that each row in the texpad occupies a single cell in Excel.
> In view of that would it be more safer if I remove the specification of
> TextQualifier:=xlDoubleQuote. If yes, what value should I put following
the
> equal to sign in the text qualifier statement.
> d) What does FieldInfo:=Array(1, 1). Based on my requirement mentioned in
> point c) does the specification in Fieldinfo pose a danger for all my data
> not getting transferred.
> e) What does TrailingMinusNumbers:=True mean. Again does the trailing
minus
> number specification affect the amount and the way that gets stored in
each
> cell of the excel.
>
>
> Thanks a lot,
> Hari
> India
>
>

Hi Tom,

Thanx for directing me to help file. My mistake for not consulting that.(Too
erratic regarding accessing help.)

I also have the same options as yours for origin (I have English version).
Excel help files says that origin "Specifies the origin of the text file".
So, I think that 437 refers to the platform in which this texpad type of
file was created (This textpad I got from my client).
--
Thanks a lot,
Hari
India

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uuKOPwk#EHA.4092@TK2MSFTNGP09.phx.gbl...
> Most of this is explained in the help file in great detail. I would start
> there and then ask for explanation.
>
> Most of this reflects the choices you made when you went throught the text
> import wizard. For instance, at the start of the wizard is the Choice
start
> at line and a textbox. You entered 1 and it is recorded as
>
> Startrow:=1
>
> From you description, that is what you want.
>
> For the remainder, begin in Help. But as an example, for me, help only
> shows the values 1, 2, or 3 for Origin.
>
> ? xlMSDOS
> 3
> ? xlWindows
> 2
> ? xlMacintosh
> 1
>
>
> You may have more because you don't have an English version of Excel
> (assumed).
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Hari" <excel_hari@hotmail.com> wrote in message
> news:eSjqpmk%23EHA.936@TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Im trying to programmatically open a textpad kind of file (and then save
> it
> > as excel file) and I recorded a macro for the same. The relevant line is
> > ....
> >
> > Workbooks.OpenText Filename:= _
> > "C:\Documents and Settings\abc\Desktop\dev11022.xls", Origin:=437,
> > StartRow _
> > :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> > ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
> > Comma:=False _
> > , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
> > TrailingMinusNumbers:=True
> >
> > I wanted to understand
> > a) What "Origin" means
> > b) Is Startrow the row from which textpad will be read?
> > c) I want to export whole of the textpad in to excel without any
> exclusions
> > in such a way that each row in the texpad occupies a single cell in
Excel.
> > In view of that would it be more safer if I remove the specification of
> > TextQualifier:=xlDoubleQuote. If yes, what value should I put following
> the
> > equal to sign in the text qualifier statement.
> > d) What does FieldInfo:=Array(1, 1). Based on my requirement mentioned
in
> > point c) does the specification in Fieldinfo pose a danger for all my
data
> > not getting transferred.
> > e) What does TrailingMinusNumbers:=True mean. Again does the trailing
> minus
> > number specification affect the amount and the way that gets stored in
> each
> > cell of the excel.
> >
> >
> > Thanks a lot,
> > Hari
> > India
> >
> >
>
>

Hi Tom,

On further probing...

In the help, TextQualifier:=xlDoubleQuote, texqualifier is described as
"specifies the text qualifier". What does qualifier mean. Going through the
3 optionals in help I set TextQualifier:=xlTextQualifierNone (I have a
morbid fear that some data would be missed out otherwise.)
What would be the difference in the result if I use
TextQualifier:=xlTextQualifierNone rather than TextQualifier:=xlDoubleQuote.

I have used text to columns feature a lot in Excel (non programmatically)
and till now I have been BLIND to this option. I used to do text to columns
a lot and was always concerned only with the Delimiter option and never
bothered to understand what textqualifier means. Please enlighten me.
--
Thanks a lot,
Hari
India

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uuKOPwk#EHA.4092@TK2MSFTNGP09.phx.gbl...
> Most of this is explained in the help file in great detail. I would start
> there and then ask for explanation.
>
> Most of this reflects the choices you made when you went throught the text
> import wizard. For instance, at the start of the wizard is the Choice
start
> at line and a textbox. You entered 1 and it is recorded as
>
> Startrow:=1
>
> From you description, that is what you want.
>
> For the remainder, begin in Help. But as an example, for me, help only
> shows the values 1, 2, or 3 for Origin.
>
> ? xlMSDOS
> 3
> ? xlWindows
> 2
> ? xlMacintosh
> 1
>
>
> You may have more because you don't have an English version of Excel
> (assumed).
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Hari" <excel_hari@hotmail.com> wrote in message
> news:eSjqpmk%23EHA.936@TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Im trying to programmatically open a textpad kind of file (and then save
> it
> > as excel file) and I recorded a macro for the same. The relevant line is
> > ....
> >
> > Workbooks.OpenText Filename:= _
> > "C:\Documents and Settings\abc\Desktop\dev11022.xls", Origin:=437,
> > StartRow _
> > :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> > ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
> > Comma:=False _
> > , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
> > TrailingMinusNumbers:=True
> >
> > I wanted to understand
> > a) What "Origin" means
> > b) Is Startrow the row from which textpad will be read?
> > c) I want to export whole of the textpad in to excel without any
> exclusions
> > in such a way that each row in the texpad occupies a single cell in
Excel.
> > In view of that would it be more safer if I remove the specification of
> > TextQualifier:=xlDoubleQuote. If yes, what value should I put following
> the
> > equal to sign in the text qualifier statement.
> > d) What does FieldInfo:=Array(1, 1). Based on my requirement mentioned
in
> > point c) does the specification in Fieldinfo pose a danger for all my
data
> > not getting transferred.
> > e) What does TrailingMinusNumbers:=True mean. Again does the trailing
> minus
> > number specification affect the amount and the way that gets stored in
> each
> > cell of the excel.
> >
> >
> > Thanks a lot,
> > Hari
> > India
> >
> >
>
>

Text qualifier is used to delimit/mark text strings if they include a
delimiter character.

for a CSV file as an example

123,the house,345,big dog

would be broken into 4 columns, but assume we have

123,My house, is big, 345, big dog

this would be broken into 5 columns, but 'My House, is big' is intended to
be a single field value. To indicate this we use the TextQualifier
Character

123,"My house, is big",345, big dog

now all is well.

--
Regards,
Tom Ogilvy

"Hari" <excel_hari@hotmail.com> wrote in message
news:%2331I61l%23EHA.3616@TK2MSFTNGP11.phx.gbl...
> Hi Tom,
>
> On further probing...
>
> In the help, TextQualifier:=xlDoubleQuote, texqualifier is described as
> "specifies the text qualifier". What does qualifier mean. Going through
the
> 3 optionals in help I set TextQualifier:=xlTextQualifierNone (I have a
> morbid fear that some data would be missed out otherwise.)
> What would be the difference in the result if I use
> TextQualifier:=xlTextQualifierNone rather than
TextQualifier:=xlDoubleQuote.
>
> I have used text to columns feature a lot in Excel (non programmatically)
> and till now I have been BLIND to this option. I used to do text to
columns
> a lot and was always concerned only with the Delimiter option and never
> bothered to understand what textqualifier means. Please enlighten me.
> --
> Thanks a lot,
> Hari
> India
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:uuKOPwk#EHA.4092@TK2MSFTNGP09.phx.gbl...
> > Most of this is explained in the help file in great detail. I would
start
> > there and then ask for explanation.
> >
> > Most of this reflects the choices you made when you went throught the
text
> > import wizard. For instance, at the start of the wizard is the Choice
> start
> > at line and a textbox. You entered 1 and it is recorded as
> >
> > Startrow:=1
> >
> > From you description, that is what you want.
> >
> > For the remainder, begin in Help. But as an example, for me, help only
> > shows the values 1, 2, or 3 for Origin.
> >
> > ? xlMSDOS
> > 3
> > ? xlWindows
> > 2
> > ? xlMacintosh
> > 1
> >
> >
> > You may have more because you don't have an English version of Excel
> > (assumed).
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Hari" <excel_hari@hotmail.com> wrote in message
> > news:eSjqpmk%23EHA.936@TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > Im trying to programmatically open a textpad kind of file (and then
save
> > it
> > > as excel file) and I recorded a macro for the same. The relevant line
is
> > > ....
> > >
> > > Workbooks.OpenText Filename:= _
> > > "C:\Documents and Settings\abc\Desktop\dev11022.xls",
Origin:=437,
> > > StartRow _
> > > :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> > > ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
> > > Comma:=False _
> > > , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
> > > TrailingMinusNumbers:=True
> > >
> > > I wanted to understand
> > > a) What "Origin" means
> > > b) Is Startrow the row from which textpad will be read?
> > > c) I want to export whole of the textpad in to excel without any
> > exclusions
> > > in such a way that each row in the texpad occupies a single cell in
> Excel.
> > > In view of that would it be more safer if I remove the specification
of
> > > TextQualifier:=xlDoubleQuote. If yes, what value should I put
following
> > the
> > > equal to sign in the text qualifier statement.
> > > d) What does FieldInfo:=Array(1, 1). Based on my requirement mentioned
> in
> > > point c) does the specification in Fieldinfo pose a danger for all my
> data
> > > not getting transferred.
> > > e) What does TrailingMinusNumbers:=True mean. Again does the trailing
> > minus
> > > number specification affect the amount and the way that gets stored in
> > each
> > > cell of the excel.
> > >
> > >
> > > Thanks a lot,
> > > Hari
> > > India
> > >
> > >
> >
> >
>
>

Hi Tom,

Thanx for the post. Very nice explanation.

Regards,
Hari
India

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uvDM1Hm#EHA.3924@TK2MSFTNGP15.phx.gbl...
> Text qualifier is used to delimit/mark text strings if they include a
> delimiter character.
>
> for a CSV file as an example
>
> 123,the house,345,big dog
>
> would be broken into 4 columns, but assume we have
>
> 123,My house, is big, 345, big dog
>
> this would be broken into 5 columns, but 'My House, is big' is intended to
> be a single field value. To indicate this we use the TextQualifier
> Character
>
> 123,"My house, is big",345, big dog
>
> now all is well.
>
> --
> Regards,
> Tom Ogilvy
>
> "Hari" <excel_hari@hotmail.com> wrote in message
> news:%2331I61l%23EHA.3616@TK2MSFTNGP11.phx.gbl...
> > Hi Tom,
> >
> > On further probing...
> >
> > In the help, TextQualifier:=xlDoubleQuote, texqualifier is described as
> > "specifies the text qualifier". What does qualifier mean. Going through
> the
> > 3 optionals in help I set TextQualifier:=xlTextQualifierNone (I have a
> > morbid fear that some data would be missed out otherwise.)
> > What would be the difference in the result if I use
> > TextQualifier:=xlTextQualifierNone rather than
> TextQualifier:=xlDoubleQuote.
> >
> > I have used text to columns feature a lot in Excel (non
programmatically)
> > and till now I have been BLIND to this option. I used to do text to
> columns
> > a lot and was always concerned only with the Delimiter option and never
> > bothered to understand what textqualifier means. Please enlighten me.
> > --
> > Thanks a lot,
> > Hari
> > India
> >
> > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > news:uuKOPwk#EHA.4092@TK2MSFTNGP09.phx.gbl...
> > > Most of this is explained in the help file in great detail. I would
> start
> > > there and then ask for explanation.
> > >
> > > Most of this reflects the choices you made when you went throught the
> text
> > > import wizard. For instance, at the start of the wizard is the Choice
> > start
> > > at line and a textbox. You entered 1 and it is recorded as
> > >
> > > Startrow:=1
> > >
> > > From you description, that is what you want.
> > >
> > > For the remainder, begin in Help. But as an example, for me, help
only
> > > shows the values 1, 2, or 3 for Origin.
> > >
> > > ? xlMSDOS
> > > 3
> > > ? xlWindows
> > > 2
> > > ? xlMacintosh
> > > 1
> > >
> > >
> > > You may have more because you don't have an English version of Excel
> > > (assumed).
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Hari" <excel_hari@hotmail.com> wrote in message
> > > news:eSjqpmk%23EHA.936@TK2MSFTNGP12.phx.gbl...
> > > > Hi,
> > > >
> > > > Im trying to programmatically open a textpad kind of file (and then
> save
> > > it
> > > > as excel file) and I recorded a macro for the same. The relevant
line
> is
> > > > ....
> > > >
> > > > Workbooks.OpenText Filename:= _
> > > > "C:\Documents and Settings\abc\Desktop\dev11022.xls",
> Origin:=437,
> > > > StartRow _
> > > > :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> > > > ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
> > > > Comma:=False _
> > > > , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
> > > > TrailingMinusNumbers:=True
> > > >
> > > > I wanted to understand
> > > > a) What "Origin" means
> > > > b) Is Startrow the row from which textpad will be read?
> > > > c) I want to export whole of the textpad in to excel without any
> > > exclusions
> > > > in such a way that each row in the texpad occupies a single cell in
> > Excel.
> > > > In view of that would it be more safer if I remove the specification
> of
> > > > TextQualifier:=xlDoubleQuote. If yes, what value should I put
> following
> > > the
> > > > equal to sign in the text qualifier statement.
> > > > d) What does FieldInfo:=Array(1, 1). Based on my requirement
mentioned
> > in
> > > > point c) does the specification in Fieldinfo pose a danger for all
my
> > data
> > > > not getting transferred.
> > > > e) What does TrailingMinusNumbers:=True mean. Again does the
trailing
> > > minus
> > > > number specification affect the amount and the way that gets stored
in
> > > each
> > > > cell of the excel.
> > > >
> > > >
> > > > Thanks a lot,
> > > > Hari
> > > > India
> > > >
> > > >
> > >
> > >
> >
> >
>
>