Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default covert data from text file into columns

The data is in a text file (amounting to 1.5 M rows) - Sample attached here

Location = "San Bernandino CA"

Store ID = 1
Stock ID = 1
Address = 1001 Parkway Ave
Xroad ID = 1001
ID = 1001

Location = "San Francisco CA"

Store ID = 2
Stock ID = 2
Address = 2001 Morrison Ave
Xroad ID = 2001
ID = 2001


Output should look like:
Location Store ID Stock ID Address Xroad ID ID
San Bernandino
San Francisco

If there is NO Location = " " it is end of row data in the file

since the raw text file is large 1.3M data is running bat file to create csv an option


Thank you
J C
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default covert data from text file into columns

A few Q's:
Do you want to deliberately omit the state from Location field data as
exampled?
Location source data "San Bernadino CA" parses as "San Bernadino"

Must the fieldnames be EXACTLY as depicted...
Location,Store ID,Stock ID,Address,Xroad ID,ID
...or can they be...
Location,StoreID,StockID,Address,XroadID,ID?

The structure of the source data file is important in that it must be
CONSISTENTLY identical file-to-file. So...

Each block of data MUST be structured IDENTICALLY so...
Is the 1st line in the file always going to start with "Location = "?
OR: Will it be blank and "Location = " be line 2?

Is the filename variable or fixed?

Does each line in the source file ALWAYS end with CarriageReturn+LineFeed?
OR Carriagereturn only?
OR LineFeed only?

Can you post a download link to an actual file sample containing only say 10
blocks of data (1st 9 and last 1)?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default covert data from text file into columns

On Sunday, December 30, 2018 at 6:36:25 AM UTC-5, GS wrote:
A few Q's:
Do you want to deliberately omit the state from Location field data as
exampled?
Location source data "San Bernadino CA" parses as "San Bernadino"

Must the fieldnames be EXACTLY as depicted...
Location,Store ID,Stock ID,Address,Xroad ID,ID
..or can they be...
Location,StoreID,StockID,Address,XroadID,ID?

The structure of the source data file is important in that it must be
CONSISTENTLY identical file-to-file. So...

Each block of data MUST be structured IDENTICALLY so...
Is the 1st line in the file always going to start with "Location = "?
OR: Will it be blank and "Location = " be line 2?

Is the filename variable or fixed?

Does each line in the source file ALWAYS end with CarriageReturn+LineFeed?
OR Carriagereturn only?
OR LineFeed only?

Can you post a download link to an actual file sample containing only say 10
blocks of data (1st 9 and last 1)?

--
Garry

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


Thanks for helping me out.

The structure of the raw text file is
Location
Blank
Data until Line 22 then two blanks - Carriage return Line feed
and then next set of data


textfile
Location = "San Bernandino CA"

Store ID = 1
Stock ID = 1
Address = 1001 Parkway Ave
Xroad ID = 1001
ID = 1001
Blank
Location = "San Francisco CA"
Blank
Store ID = 2
Stock ID = 2
Address = 2001 Morrison Ave
Xroad ID = 2001
ID = 2001
Blank

Location,StoreID,StockID,Address,XroadID,ID these can be the column names

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default covert data from text file into columns

You didn't confirm/deny stripping of the state from Location so I included
that; - comment it out if not using.
Paste this into a new module and let me know how things go...

'<begin code
Option Explicit

'[EnableFastCode() Type Declarations]
Type udtAppModes
'Default types
Events As Boolean: CalcMode As XlCalculation: Display As Boolean: CallerID As
String
'Project-specific types
End Type
Public AppMode As udtAppModes


Sub Parse_TxtFileBlockData()
Const sSrc$ = "Parse_TxtFileBlockData" '//AppMode.CallerID

Dim vData, vTmp, v
Dim lRow&, n&, k&, sFile$, sVPD$, sTag$, sFields$
sVPD = " = ": sTag = "~": sFields =
"Location,StoreID,StockID,Address,XroadID,ID"

'Setup header row
lRow = 1: vTmp = Split(sFields, ",")
Cells(lRow, 1).Resize(1, UBound(vTmp) + 1) = vTmp

'Get the filename
sFile = Application.GetOpenFilename
If sFile = "" Then Beep: Exit Sub

'Load the file into an array of data blocks
vData = Split(ReadTextFile(sFile), "Location = ")

EnableFastCode sSrc
'Parse the data into useable bits
For n = LBound(vData) To UBound(vData)
'Parse each data block into an array
If Not vData(n) = "" Then
vTmp = Split(vData(n), vbCrLf)
For k = LBound(vTmp) To UBound(vTmp)

'Strip out unwanted data from Location
If k = 0 Then
'Remove quote characters
vTmp(k) = Replace(vTmp(k), Chr(34), "")
'Remove state
v = Split(vTmp(k), Chr(32)) '//format is "city<spacestate"
v(UBound(v)) = sTag: vTmp(k) = Join(Filter(v, sTag, False), Chr(32))
End If 'k = 0

'Strip out unwanted data from other fields
If vTmp(k) = "" Then '//tag blank lines
vTmp(k) = sTag
Else '//parse value pairs
If InStr(vTmp(k), sVPD) 0 Then vTmp(k) = Split(vTmp(k), sVPD)(1)
End If 'vTmp(k) = ""
Next 'k
vTmp = Filter(vTmp, sTag, False) '//remove tagged elements

'Dump the data into next empty row
lRow = lRow + 1
Cells(lRow, 1).Resize(1, UBound(vTmp) + 1) = vTmp
End If 'Not vData(n) = ""
Next 'n
EnableFastCode sSrc, False

End Sub 'Parse_TxtFileBlockData

Function ReadTextFile$(Filename$)
' Reads large amounts of data from a text file in a single step.
Dim iNum%
On Error GoTo ErrHandler
iNum = FreeFile(): Open Filename For Input As #iNum
ReadTextFile = Space$(LOF(iNum))
ReadTextFile = Input(LOF(iNum), iNum) '//return the entire file

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description
End Function 'ReadTextFile()

'--------------------------------------------------------------------------------------
' **Note: EnableFastCode requires the following declarations be in a standard
module.
'--------------------------------------------------------------------------------------
'[EnableFastCode() Type Declarations]
'Type udtAppModes
' 'Default types
' Events As Boolean: CalcMode As XlCalculation: Display As Boolean: CallerID
As String
' 'Project-specific types
'End Type
'Public AppMode As udtAppModes
'--------------------------------------------------------------------------------------
Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True)
' **Note: Requires 'Type udtAppModes' and 'Public AppMode As udtAppModes'
declarations

'The following will make sure only the Caller has control,
'and allows any Caller to take control when not in use.
If AppMode.CallerID < Caller Then _
If AppMode.CallerID < "" Then Exit Sub

With Application
If SetFast Then
AppMode.Display = .ScreenUpdating: .ScreenUpdating = False
AppMode.CalcMode = .Calculation: .Calculation = xlCalculationManual
AppMode.Events = .EnableEvents: .EnableEvents = False
AppMode.CallerID = Caller
Else
.ScreenUpdating = AppMode.Display
.Calculation = AppMode.CalcMode
.EnableEvents = AppMode.Events
AppMode.CallerID = ""
End If
End With
End Sub 'EnableFastCode()

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default covert data from text file into columns

Revise the following procedure's 'Setup header row block of code if you want to
freeze the headers.


Sub Parse_TxtFileBlockData()
Const sSrc$ = "Parse_TxtFileBlockData" '//AppMode.CallerID

Dim vData, vTmp, v
Dim lRow&, n&, k&, sFile$, sVPD$, sTag$, sFields$
sVPD = " = ": sTag = "~": sFields =
"Location,StoreID,StockID,Address,XroadID,ID"

'Setup header row
lRow = 1: vTmp = Split(sFields, ",")
Cells(lRow, 1).Resize(1, UBound(vTmp) + 1) = vTmp
Application.Goto (Cells(2, 1))
CommandBars(1).Controls("Window").Controls("&Freez e Panes").Execute

'Get the filename
sFile = Application.GetOpenFilename
If sFile = "" Then Beep: Exit Sub

'Load the file into an array of data blocks
vData = Split(ReadTextFile(sFile), "Location = ")

EnableFastCode sSrc
'Parse the data into useable bits
For n = LBound(vData) To UBound(vData)
'Parse each data block into an array
If Not vData(n) = "" Then
vTmp = Split(vData(n), vbCrLf)
For k = LBound(vTmp) To UBound(vTmp)

'Strip out unwanted data from Location
If k = 0 Then
'Remove quote characters
vTmp(k) = Replace(vTmp(k), Chr(34), "")
'Remove state
v = Split(vTmp(k), Chr(32)) '//format is "city<spacestate"
v(UBound(v)) = sTag: vTmp(k) = Join(Filter(v, sTag, False),
Chr(32))
End If 'k = 0

'Strip out unwanted data from other fields
If vTmp(k) = "" Then '//tag blank lines
vTmp(k) = sTag
Else '//parse value pairs
If InStr(vTmp(k), sVPD) 0 Then vTmp(k) = Split(vTmp(k), sVPD)(1)
End If 'vTmp(k) = ""
Next 'k
vTmp = Filter(vTmp, sTag, False) '//remove tagged elements

'Dump the data into next empty row
lRow = lRow + 1
Cells(lRow, 1).Resize(1, UBound(vTmp) + 1) = vTmp
End If 'Not vData(n) = ""
Next 'n
EnableFastCode sSrc, False

End Sub 'Parse_TxtFileBlockData


--
Garry

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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default covert data from text file into columns

On Monday, December 31, 2018 at 7:29:22 PM UTC-5, GS wrote:
Revise the following procedure's 'Setup header row block of code if you want to
freeze the headers.


Sub Parse_TxtFileBlockData()
Const sSrc$ = "Parse_TxtFileBlockData" '//AppMode.CallerID

Dim vData, vTmp, v
Dim lRow&, n&, k&, sFile$, sVPD$, sTag$, sFields$
sVPD = " = ": sTag = "~": sFields =
"Location,StoreID,StockID,Address,XroadID,ID"

'Setup header row
lRow = 1: vTmp = Split(sFields, ",")
Cells(lRow, 1).Resize(1, UBound(vTmp) + 1) = vTmp
Application.Goto (Cells(2, 1))
CommandBars(1).Controls("Window").Controls("&Freez e Panes").Execute

'Get the filename
sFile = Application.GetOpenFilename
If sFile = "" Then Beep: Exit Sub

'Load the file into an array of data blocks
vData = Split(ReadTextFile(sFile), "Location = ")

EnableFastCode sSrc
'Parse the data into useable bits
For n = LBound(vData) To UBound(vData)
'Parse each data block into an array
If Not vData(n) = "" Then
vTmp = Split(vData(n), vbCrLf)
For k = LBound(vTmp) To UBound(vTmp)

'Strip out unwanted data from Location
If k = 0 Then
'Remove quote characters
vTmp(k) = Replace(vTmp(k), Chr(34), "")
'Remove state
v = Split(vTmp(k), Chr(32)) '//format is "city<spacestate"
v(UBound(v)) = sTag: vTmp(k) = Join(Filter(v, sTag, False),
Chr(32))
End If 'k = 0

'Strip out unwanted data from other fields
If vTmp(k) = "" Then '//tag blank lines
vTmp(k) = sTag
Else '//parse value pairs
If InStr(vTmp(k), sVPD) 0 Then vTmp(k) = Split(vTmp(k), sVPD)(1)
End If 'vTmp(k) = ""
Next 'k
vTmp = Filter(vTmp, sTag, False) '//remove tagged elements

'Dump the data into next empty row
lRow = lRow + 1
Cells(lRow, 1).Resize(1, UBound(vTmp) + 1) = vTmp
End If 'Not vData(n) = ""
Next 'n
EnableFastCode sSrc, False

End Sub 'Parse_TxtFileBlockData


--
Garry

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


Hello Garry,

It gave a compile error
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default covert data from text file into columns

On Monday, December 31, 2018 at 7:29:22 PM UTC-5, GS wrote:
Revise the following procedure's 'Setup header row block of code if you want
to freeze the headers.


Sub Parse_TxtFileBlockData()
Const sSrc$ = "Parse_TxtFileBlockData" '//AppMode.CallerID

Dim vData, vTmp, v
Dim lRow&, n&, k&, sFile$, sVPD$, sTag$, sFields$
sVPD = " = ": sTag = "~": sFields =
"Location,StoreID,StockID,Address,XroadID,ID"

'Setup header row
lRow = 1: vTmp = Split(sFields, ",")
Cells(lRow, 1).Resize(1, UBound(vTmp) + 1) = vTmp
Application.Goto (Cells(2, 1))
CommandBars(1).Controls("Window").Controls("&Freez e Panes").Execute

'Get the filename
sFile = Application.GetOpenFilename
If sFile = "" Then Beep: Exit Sub

'Load the file into an array of data blocks
vData = Split(ReadTextFile(sFile), "Location = ")

EnableFastCode sSrc
'Parse the data into useable bits
For n = LBound(vData) To UBound(vData)
'Parse each data block into an array
If Not vData(n) = "" Then
vTmp = Split(vData(n), vbCrLf)
For k = LBound(vTmp) To UBound(vTmp)

'Strip out unwanted data from Location
If k = 0 Then
'Remove quote characters
vTmp(k) = Replace(vTmp(k), Chr(34), "")
'Remove state
v = Split(vTmp(k), Chr(32)) '//format is "city<spacestate"
v(UBound(v)) = sTag: vTmp(k) = Join(Filter(v, sTag, False),
Chr(32))
End If 'k = 0

'Strip out unwanted data from other fields
If vTmp(k) = "" Then '//tag blank lines
vTmp(k) = sTag
Else '//parse value pairs
If InStr(vTmp(k), sVPD) 0 Then vTmp(k) = Split(vTmp(k),
sVPD)(1) End If 'vTmp(k) = ""
Next 'k
vTmp = Filter(vTmp, sTag, False) '//remove tagged elements

'Dump the data into next empty row
lRow = lRow + 1
Cells(lRow, 1).Resize(1, UBound(vTmp) + 1) = vTmp
End If 'Not vData(n) = ""
Next 'n
EnableFastCode sSrc, False

End Sub 'Parse_TxtFileBlockData


--
Garry

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


Hello Garry,

It gave a compile error


Probably line wraps happened. The culprit is any red text should be 1 line of
code. Use the Compile item on the Debug menu to locate syntax errors; - if it
compiles without error you are good-to-go!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5
Default covert data from text file into columns

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
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default covert data from text file into columns

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


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
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 11:09 PM
Covert Columns to rows Macro EJR Excel Programming 0 January 16th 08 10: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 12:02 PM.

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

About Us

"It's about Microsoft Excel"