A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

VBA Import of text file & Array parsing of that data



 
 
Thread Tools Display Modes
  #1  
Old November 28th 04, 01:43 PM
Dennis
external usenet poster
 
Posts: n/a
Default VBA Import of text file & Array parsing of that data

Using XL 2993 & 97

The array formula which parses the information below (from a text file) by
"trimming" elements of the "Directory" line of information is:


Workbooks.OpenText Filename:="C:\Test\Listing.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:= Array(Array(0, 1), Array(10, 1), Array(17,1), _ Array(21, 1),
Array(39, 1))



Specifically:

"Directory of C:\BOB AMY Estates\Bank Documents"
is being parsed: (because of data columns following it)

11/21/2004|of C:\|BOB|AMY Estates\Bank|Documents
0 | 10 |17 | 21 | 39

******** Portion of the Actual Text Document ****^**********

Directory of C:\BOB AMY Estates\Bank Documents

11/21/2004 03:02 PM 120,787 NCB Ck #101.jpg
11/21/2004 03:01 PM 149,374 NCB Ck #93.jpg
11/21/2004 02:45 PM 136,291 NCB Ck Template.jpg
........... ..... .. ....... ...................
........... ..... .. ....... ...................
************************************************** ********

If I concatenate the columns created by the XL import array formula, I end
up with:

C:\BOBAMY Estates\BankDocuments

not the actual path of

C:\BOB AMY Estates\Bank Documents

Is there a way to force the array formula above to capture the space after
"BOB" to get "BOB " and also after
"AMY Estates\Bank" to get "AMY Estates\Bank "?

Yes, I can on-the-fly fix the data in the opeing XL file.
But I may have to manually change each similar data line.

I do not understand Array formula VBA parameter setup and coding. But
saying that, it seems to me that the array formula above is trimming spaces
before and after the data in the array elements above.

Any help is welcomed!

TIA Dennis
Ads
  #2  
Old November 28th 04, 01:59 PM
Dennis
external usenet poster
 
Posts: n/a
Default

My question as posted, does not line-up the data as was intended.

Consider selecting all of my post above and pasting into NotePad. It is
easier to understand.

Dennis

************************************************** *****

"Dennis" wrote:

> Using XL 2993 & 97
>
> The array formula which parses the information below (from a text file) by
> "trimming" elements of the "Directory" line of information is:
>
>
> Workbooks.OpenText Filename:="C:\Test\Listing.txt", _
> Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
> FieldInfo:= Array(Array(0, 1), Array(10, 1), Array(17,1), _ Array(21, 1),
> Array(39, 1))
>
>
>
> Specifically:
>
> "Directory of C:\BOB AMY Estates\Bank Documents"
> is being parsed: (because of data columns following it)
>
> 11/21/2004|of C:\|BOB|AMY Estates\Bank|Documents
> 0 | 10 |17 | 21 | 39
>
> ******** Portion of the Actual Text Document ****^**********
>
> Directory of C:\BOB AMY Estates\Bank Documents
>
> 11/21/2004 03:02 PM 120,787 NCB Ck #101.jpg
> 11/21/2004 03:01 PM 149,374 NCB Ck #93.jpg
> 11/21/2004 02:45 PM 136,291 NCB Ck Template.jpg
> .......... ..... .. ....... ...................
> .......... ..... .. ....... ...................
> ************************************************** ********
>
> If I concatenate the columns created by the XL import array formula, I end
> up with:
>
> C:\BOBAMY Estates\BankDocuments
>
> not the actual path of
>
> C:\BOB AMY Estates\Bank Documents
>
> Is there a way to force the array formula above to capture the space after
> "BOB" to get "BOB " and also after
> "AMY Estates\Bank" to get "AMY Estates\Bank "?
>
> Yes, I can on-the-fly fix the data in the opeing XL file.
> But I may have to manually change each similar data line.
>
> I do not understand Array formula VBA parameter setup and coding. But
> saying that, it seems to me that the array formula above is trimming spaces
> before and after the data in the array elements above.
>
> Any help is welcomed!
>
> TIA Dennis

  #3  
Old November 28th 04, 05:05 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

I think if I had to do this, I'd import each record into one cell (column A).

Insert a header row 1. Then I could use a bunch of formulas to parse each
category of lines

In column B to get the folder name:
=if(left(a2,9)<>"directory",B1,mid(a1,14,255))

----+----+----+----+----+----+----+----+----+----+----+
11/21/2004 03:02 PM 120,787 NCB Ck #101.jpg

In column C (to get the filename):
=if(left(a1,9)="directory","",mid(a1,40,255)


And use as many as these type formulas as you want.

======
Another option may be to use a different Dir command in DOS.

Try shelling to DOS and doing a:

dir /?

to see all the options you can get.
dir /b/s *.xls
or
dir /b/s *.xls > listing.txt

may be sufficient.

==========
And you may want to post back with what you're doing. There are lots of ways to
skin a cat (ouch!).

Tushar Mehta has one version of a directory listing routine at:
http://www.tushar-mehta.com/excel/so...ist/index.html



Dennis wrote:
>
> Using XL 2993 & 97
>
> The array formula which parses the information below (from a text file) by
> "trimming" elements of the "Directory" line of information is:
>
>
> Workbooks.OpenText Filename:="C:\Test\Listing.txt", _
> Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
> FieldInfo:= Array(Array(0, 1), Array(10, 1), Array(17,1), _ Array(21, 1),
> Array(39, 1))
>
> Specifically:
>
> "Directory of C:\BOB AMY Estates\Bank Documents"
> is being parsed: (because of data columns following it)
>
> 11/21/2004|of C:\|BOB|AMY Estates\Bank|Documents
> 0 | 10 |17 | 21 | 39
>
> ******** Portion of the Actual Text Document ****^**********
>
> Directory of C:\BOB AMY Estates\Bank Documents
>
> 11/21/2004 03:02 PM 120,787 NCB Ck #101.jpg
> 11/21/2004 03:01 PM 149,374 NCB Ck #93.jpg
> 11/21/2004 02:45 PM 136,291 NCB Ck Template.jpg
> .......... ..... .. ....... ...................
> .......... ..... .. ....... ...................
> ************************************************** ********
>
> If I concatenate the columns created by the XL import array formula, I end
> up with:
>
> C:\BOBAMY Estates\BankDocuments
>
> not the actual path of
>
> C:\BOB AMY Estates\Bank Documents
>
> Is there a way to force the array formula above to capture the space after
> "BOB" to get "BOB " and also after
> "AMY Estates\Bank" to get "AMY Estates\Bank "?
>
> Yes, I can on-the-fly fix the data in the opeing XL file.
> But I may have to manually change each similar data line.
>
> I do not understand Array formula VBA parameter setup and coding. But
> saying that, it seems to me that the array formula above is trimming spaces
> before and after the data in the array elements above.
>
> Any help is welcomed!
>
> TIA Dennis


--

Dave Peterson
  #4  
Old November 28th 04, 06:43 PM
Dennis
external usenet poster
 
Posts: n/a
Default

Dave,

My concern was and is, that there is no easy solution.

FYI, the DOS Shell command that I am using is:

dir %1 /p /s /o:g-d > "c:\XX\DirListing.txt"

**************************************

The parsing VBA code is:

Workbooks.OpenText Filename:="C:\XX\DirListing.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _ FieldInfo:=
Array(Array(0, 1), Array(10, 1), Array(17, 1), Array(21, 1), Array(39, 1))
Range("A1").EntireColumn.Insert
Range("A4").FormulaR1C1="=IF(LEN(R[-1]C)=0,IF(MID(RC[1] _
,1,9)=""Directory"",MID(RC[2],4,3)&Left(RC[3],1,4)&Left(RC[4],1,18)&Trim(RC[5]),OFFSET(RC,-1,0)),"""")"
Range("A5").FormulaR1C1 = "=IF(LEN(R[-1]C)>0,IF(MID(RC[1] _
,1,9)=""Directory"",MID(RC[2],4,3)&Left(RC[3],1,4)&Left(RC[4],1,18)&Trim(RC[5]),OFFSET(RC,-1,0)),"""")"
Range("A5").AutoFill Destination:=Range("A5:A" & Range("B" _ &
Rows.Count).End(xlUp).Row), Type:=xlFillCopy


I WILL get back to you as I progress.

Also I will look at the link that you suggested.

Thanks again and again for all your help over time.

Dennis

************************************************** *
"Dave Peterson" wrote:

> I think if I had to do this, I'd import each record into one cell (column A).
>
> Insert a header row 1. Then I could use a bunch of formulas to parse each
> category of lines
>
> In column B to get the folder name:
> =if(left(a2,9)<>"directory",B1,mid(a1,14,255))
>
> ----+----+----+----+----+----+----+----+----+----+----+
> 11/21/2004 03:02 PM 120,787 NCB Ck #101.jpg
>
> In column C (to get the filename):
> =if(left(a1,9)="directory","",mid(a1,40,255)
>
>
> And use as many as these type formulas as you want.
>
> ======
> Another option may be to use a different Dir command in DOS.
>
> Try shelling to DOS and doing a:
>
> dir /?
>
> to see all the options you can get.
> dir /b/s *.xls
> or
> dir /b/s *.xls > listing.txt
>
> may be sufficient.
>
> ==========
> And you may want to post back with what you're doing. There are lots of ways to
> skin a cat (ouch!).
>
> Tushar Mehta has one version of a directory listing routine at:
> http://www.tushar-mehta.com/excel/so...ist/index.html
>
>
>
> Dennis wrote:
> >
> > Using XL 2993 & 97
> >
> > The array formula which parses the information below (from a text file) by
> > "trimming" elements of the "Directory" line of information is:
> >
> >
> > Workbooks.OpenText Filename:="C:\Test\Listing.txt", _
> > Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
> > FieldInfo:= Array(Array(0, 1), Array(10, 1), Array(17,1), _ Array(21, 1),
> > Array(39, 1))
> >
> > Specifically:
> >
> > "Directory of C:\BOB AMY Estates\Bank Documents"
> > is being parsed: (because of data columns following it)
> >
> > 11/21/2004|of C:\|BOB|AMY Estates\Bank|Documents
> > 0 | 10 |17 | 21 | 39
> >
> > ******** Portion of the Actual Text Document ****^**********
> >
> > Directory of C:\BOB AMY Estates\Bank Documents
> >
> > 11/21/2004 03:02 PM 120,787 NCB Ck #101.jpg
> > 11/21/2004 03:01 PM 149,374 NCB Ck #93.jpg
> > 11/21/2004 02:45 PM 136,291 NCB Ck Template.jpg
> > .......... ..... .. ....... ...................
> > .......... ..... .. ....... ...................
> > ************************************************** ********
> >
> > If I concatenate the columns created by the XL import array formula, I end
> > up with:
> >
> > C:\BOBAMY Estates\BankDocuments
> >
> > not the actual path of
> >
> > C:\BOB AMY Estates\Bank Documents
> >
> > Is there a way to force the array formula above to capture the space after
> > "BOB" to get "BOB " and also after
> > "AMY Estates\Bank" to get "AMY Estates\Bank "?
> >
> > Yes, I can on-the-fly fix the data in the opeing XL file.
> > But I may have to manually change each similar data line.
> >
> > I do not understand Array formula VBA parameter setup and coding. But
> > saying that, it seems to me that the array formula above is trimming spaces
> > before and after the data in the array elements above.
> >
> > Any help is welcomed!
> >
> > TIA Dennis

>
> --
>
> Dave Peterson
>

  #5  
Old November 28th 04, 10:20 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

The bad news is that if you parse your input with the .opentext statement, you
won't be able to recombine the cells to get your folder name. Since xl
eliminates the leading and trailing spaces, you won't know how many to add back
to get the correct foldername.

ps.

I think I would drop the /p from your DIR command.
dir %1 /s /o:g-d > "c:\XX\DirListing.txt"

That /p tells the DOS command DIR to pause when it fills the screen. When
you're redirecting the output to file, it doesn't really make sense to do that.




Dennis wrote:
>
> Dave,
>
> My concern was and is, that there is no easy solution.
>
> FYI, the DOS Shell command that I am using is:
>
> dir %1 /p /s /o:g-d > "c:\XX\DirListing.txt"
>
> **************************************
>
> The parsing VBA code is:
>
> Workbooks.OpenText Filename:="C:\XX\DirListing.txt", _
> Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _ FieldInfo:=
> Array(Array(0, 1), Array(10, 1), Array(17, 1), Array(21, 1), Array(39, 1))
> Range("A1").EntireColumn.Insert
> Range("A4").FormulaR1C1="=IF(LEN(R[-1]C)=0,IF(MID(RC[1] _
> ,1,9)=""Directory"",MID(RC[2],4,3)&Left(RC[3],1,4)&Left(RC[4],1,18)&Trim(RC[5]),OFFSET(RC,-1,0)),"""")"
> Range("A5").FormulaR1C1 = "=IF(LEN(R[-1]C)>0,IF(MID(RC[1] _
> ,1,9)=""Directory"",MID(RC[2],4,3)&Left(RC[3],1,4)&Left(RC[4],1,18)&Trim(RC[5]),OFFSET(RC,-1,0)),"""")"
> Range("A5").AutoFill Destination:=Range("A5:A" & Range("B" _ &
> Rows.Count).End(xlUp).Row), Type:=xlFillCopy
>
> I WILL get back to you as I progress.
>
> Also I will look at the link that you suggested.
>
> Thanks again and again for all your help over time.
>
> Dennis
>
> ************************************************** *
> "Dave Peterson" wrote:
>
> > I think if I had to do this, I'd import each record into one cell (column A).
> >
> > Insert a header row 1. Then I could use a bunch of formulas to parse each
> > category of lines
> >
> > In column B to get the folder name:
> > =if(left(a2,9)<>"directory",B1,mid(a1,14,255))
> >
> > ----+----+----+----+----+----+----+----+----+----+----+
> > 11/21/2004 03:02 PM 120,787 NCB Ck #101.jpg
> >
> > In column C (to get the filename):
> > =if(left(a1,9)="directory","",mid(a1,40,255)
> >
> >
> > And use as many as these type formulas as you want.
> >
> > ======
> > Another option may be to use a different Dir command in DOS.
> >
> > Try shelling to DOS and doing a:
> >
> > dir /?
> >
> > to see all the options you can get.
> > dir /b/s *.xls
> > or
> > dir /b/s *.xls > listing.txt
> >
> > may be sufficient.
> >
> > ==========
> > And you may want to post back with what you're doing. There are lots of ways to
> > skin a cat (ouch!).
> >
> > Tushar Mehta has one version of a directory listing routine at:
> > http://www.tushar-mehta.com/excel/so...ist/index.html
> >
> >
> >
> > Dennis wrote:
> > >
> > > Using XL 2993 & 97
> > >
> > > The array formula which parses the information below (from a text file) by
> > > "trimming" elements of the "Directory" line of information is:
> > >
> > >
> > > Workbooks.OpenText Filename:="C:\Test\Listing.txt", _
> > > Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
> > > FieldInfo:= Array(Array(0, 1), Array(10, 1), Array(17,1), _ Array(21, 1),
> > > Array(39, 1))
> > >
> > > Specifically:
> > >
> > > "Directory of C:\BOB AMY Estates\Bank Documents"
> > > is being parsed: (because of data columns following it)
> > >
> > > 11/21/2004|of C:\|BOB|AMY Estates\Bank|Documents
> > > 0 | 10 |17 | 21 | 39
> > >
> > > ******** Portion of the Actual Text Document ****^**********
> > >
> > > Directory of C:\BOB AMY Estates\Bank Documents
> > >
> > > 11/21/2004 03:02 PM 120,787 NCB Ck #101.jpg
> > > 11/21/2004 03:01 PM 149,374 NCB Ck #93.jpg
> > > 11/21/2004 02:45 PM 136,291 NCB Ck Template.jpg
> > > .......... ..... .. ....... ...................
> > > .......... ..... .. ....... ...................
> > > ************************************************** ********
> > >
> > > If I concatenate the columns created by the XL import array formula, I end
> > > up with:
> > >
> > > C:\BOBAMY Estates\BankDocuments
> > >
> > > not the actual path of
> > >
> > > C:\BOB AMY Estates\Bank Documents
> > >
> > > Is there a way to force the array formula above to capture the space after
> > > "BOB" to get "BOB " and also after
> > > "AMY Estates\Bank" to get "AMY Estates\Bank "?
> > >
> > > Yes, I can on-the-fly fix the data in the opeing XL file.
> > > But I may have to manually change each similar data line.
> > >
> > > I do not understand Array formula VBA parameter setup and coding. But
> > > saying that, it seems to me that the array formula above is trimming spaces
> > > before and after the data in the array elements above.
> > >
> > > Any help is welcomed!
> > >
> > > TIA Dennis

> >
> > --
> >
> > Dave Peterson
> >


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with data not getting plotted Scott Ehrlich Charts and Charting in Excel 1 January 23rd 05 05:15 PM
Extending a Chart Data Series from an Array - Can it be done? Jon Peltier Charts and Charting in Excel 4 November 30th 04 03:30 AM
Bar Chart for Text Data RichIII Charts and Charting in Excel 1 November 29th 04 07:03 PM
How do I import data from a SECURED website into Excel? Jimmy Lam Excel Discussion (Misc queries) 0 November 27th 04 04:12 PM
import csv file Patrick Excel Discussion (Misc queries) 2 November 27th 04 10:09 AM


All times are GMT +1. The time now is 04:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.