Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 2nd 19, 06:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2018
Posts: 5
Default covert data from text file into columns

On Wednesday, January 2, 2019 at 11:48:23 AM UTC-5, GS wrote:
Garry, thanks for providing the solution. i have another similar file and
tried to use it but it did not work.

i can try to upload the text file if you would like to see it


Yes, I can test with it here to see why because my test file just repeats your
2 sample blocks a gazillion times. Meanwhile, try Claus' suggestion...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



I tried Claus solution but that did not work. Yours did.

Im adding below contents of the file. I am only interested in column data for some fields NOT all which are Station Name, Hostname, Default window, default usercode. I don't know if there is a way to send you the raw file

I just took a small portion of the actual file which has 2M records. The 1st line is blank - you can copy the text below into notepad


STATION NAME = CPNTA/F2066

INSTALLATION DATA = NONE
HOSTNAME = CPNTA
DEFAULT WINDOW = MARC
DEFAULT TRANCODE = NONE
TRANCODE OVERRIDE ALLOWED = NO
DEVICE TYPE = DEFAULTDEVICE
MAP LIST = NONE
DEFAULT USERCODE = NONE
DEFAULT ACCESSCODE = NONE
DEFAULT CHARGECODE = NONE
TRANCODE POSITION = 1
TIMEOUT INTERVAL = 0:00
CONTROL STATION = NO
SUPER USER = NO
SYSTEM USER = YES
PRIVILEGED USER = YES
CONTINUOUS LOG-ON = NO
VALID SECURITY CATEGORY LIST = ALL
CLOSE ACTION = 1 (Close Window)
CLOSE WINDOW = MARC


STATION NAME = C6713

INSTALLATION DATA = NONE
HOSTNAME = 10_73_24_147
DEFAULT WINDOW = MARC
DEFAULT TRANCODE = NONE
TRANCODE OVERRIDE ALLOWED = NO
DEVICE TYPE = DEFAULTDEVICE
MAP LIST = NONE
DEFAULT USERCODE = NONE
DEFAULT ACCESSCODE = NONE
DEFAULT CHARGECODE = NONE
TRANCODE POSITION = 1
TIMEOUT INTERVAL = 0:00
CONTROL STATION = YES
SUPER USER = YES
SYSTEM USER = YES
PRIVILEGED USER = YES
CONTINUOUS LOG-ON = NO
VALID SECURITY CATEGORY LIST = ALL
CLOSE ACTION = 1 (Close Window)
CLOSE WINDOW = MARC


STATION NAME = DEFAULTSTATION

INSTALLATION DATA = NONE
HOSTNAME = LOCALHOST
DEFAULT WINDOW = MARC
DEFAULT TRANCODE = NONE
TRANCODE OVERRIDE ALLOWED = NO
DEVICE TYPE = DEFAULTDEVICE
MAP LIST = NONE
DEFAULT USERCODE = NONE
DEFAULT ACCESSCODE = NONE
DEFAULT CHARGECODE = NONE
TRANCODE POSITION = 1
TIMEOUT INTERVAL = 0:00
CONTROL STATION = NO
SUPER USER = NO
SYSTEM USER = YES
PRIVILEGED USER = YES
CONTINUOUS LOG-ON = NO
VALID SECURITY CATEGORY LIST = ALL
CLOSE ACTION = 1 (Close Window)
CLOSE WINDOW = MARC


STATION NAME = "C1001T"

INSTALLATION DATA = NONE
HOSTNAME = CPNTA
DEFAULT WINDOW = MARC
DEFAULT TRANCODE = NONE
TRANCODE OVERRIDE ALLOWED = NO
DEVICE TYPE = DEFAULTDEVICE
MAP LIST = NONE
DEFAULT USERCODE = NONE
DEFAULT ACCESSCODE = NONE
DEFAULT CHARGECODE = NONE
TRANCODE POSITION = 1
TIMEOUT INTERVAL = 0:00
CONTROL STATION = NO
SUPER USER = NO
SYSTEM USER = YES
PRIVILEGED USER = YES
CONTINUOUS LOG-ON = NO
VALID SECURITY CATEGORY LIST = ALL
CLOSE ACTION = 1 (Close Window)
CLOSE WINDOW = MARC



  #12   Report Post  
Old January 2nd 19, 06:43 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,001
Default covert data from text file into columns

Ok, this is different text than your sample. Both mine & Claus' solutions use a
textual delimiter to distinguish start of each block of data; - that means code
has to account for that with each file. Also, the headers are not the same and
these must also be accounted for.

Claus makes a good point for cleaning the non-printable characters out of the
file so I'll build this into my revision...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #13   Report Post  
Old January 2nd 19, 09:36 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,669
Default covert data from text file into columns

Hi,

Am Wed, 2 Jan 2019 10:04:26 -0800 (PST) schrieb :

I tried Claus solution but that did not work. Yours did.


download the workbook from here and test it (Macros are disabled in
OneDrive):
https://1drv.ms/x/s!AqMiGBK2qniTgeAkJjRyB6GtmVNFpg
In Sheet1 you see your new data, in sheet2 the previous data.

Regards
Claus B.
--
Windows10
Office 2016
  #14   Report Post  
Old January 3rd 19, 11:25 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,001
Default covert data from text file into columns

Hi,

Am Wed, 2 Jan 2019 10:04:26 -0800 (PST) schrieb :

I tried Claus solution but that did not work. Yours did.


download the workbook from here and test it (Macros are disabled in
OneDrive):
https://1drv.ms/x/s!AqMiGBK2qniTgeAkJjRyB6GtmVNFpg
In Sheet1 you see your new data, in sheet2 the previous data.

Regards
Claus B.


FWIW+FYI:
FSO needs to be destroyed when you're finished with it so its memory space gets
released. Each instance you create stays in memory until it's destroyed and so
'programmer best practice' suggests that any code that deliberately creates an
object should also deliberately 'Set objVarName = Nothing' when no longer
needed.

The downside to the nature of this task is the 1st line of the blocks of text
varies file-to-file. This needs to be able to pull the block delimiter text and
headers from the file rather than these be hard-coded.

Thanks for the suggestion to 'clean' the text for unwanted chars.
Unfortunately, WorksheetFunction.Clean doesn't include any non-printable chars
above 32 so I made my own function that specifies chars to keep and include
chars...

Function FilterString$(ByVal TextIn$, Optional IncludeChars$, _
Optional IncludeLetters As Boolean = True, _
Optional IncludeNumbers As Boolean = True)
' Filters out all unwanted characters in a string.
' Arguments: TextIn The string being filtered.
' IncludeChars [Optional] Any non alpha-numeric characters to
keep.
' IncludeLetters [Optional] Keeps any letters.
' IncludeNumbers [Optional] Keeps any numbers.
'
' Returns: String containing only wanted characters.
' Comments: Works very fast using the Mid$() function over other methods.

Const sSource$ = "FilterString()"

'The basic characters to always keep by default
Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
Const sNumbers As String = "0123456789"

Dim i&, sKeepers$

sKeepers = IncludeChars
If IncludeLetters Then _
sKeepers = sKeepers & sLetters & UCase(sLetters)
If IncludeNumbers Then sKeepers = sKeepers & sNumbers

For i = 1 To Len(TextIn)
If InStr(sKeepers, Mid$(TextIn, i, 1)) Then _
FilterString = FilterString & Mid$(TextIn, i, 1)
Next
End Function 'FilterString()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #15   Report Post  
Old January 5th 19, 01:36 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,001
Default covert data from text file into columns

Sorry for the delay; - I had to clear my plate!

Download this example https://app.box.com/s/9zaeackub8jbe45kwjd6ua2h8072nyqd

This project
inserts a new sheet for each file;
pulls Fieldnames from the file;
lets you select which Fields to return data from (optional);
removes all unwanted characters before parsing.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Covert Text String Data Pat Excel Worksheet Functions 1 August 13th 08 07:21 PM
batch process to covert file to excel file [email protected] Excel Programming 4 February 22nd 08 10:09 PM
Covert Columns to rows Macro EJR Excel Programming 0 January 16th 08 09:52 PM
How do i covert a number to its text value i.e 1 to one Manish Sadanand Excel Programming 1 October 13th 06 01:30 PM
covert columns into rows balsrin Excel Discussion (Misc queries) 2 August 18th 06 10:34 PM


All times are GMT +1. The time now is 05:42 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017