Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 30th 18, 04:13 AM 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

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  
Old December 30th 18, 11:36 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,060
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  
Old December 31st 18, 08:05 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 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  
Old December 31st 18, 11:50 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,060
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  
Old January 1st 19, 12:29 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,060
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  
Old January 2nd 19, 12:28 AM 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 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  
Old January 2nd 19, 12:39 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,060
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  
Old January 2nd 19, 04:41 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

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  
Old January 2nd 19, 04:48 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,060
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


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 11:14 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