Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Text import and transpose - pretty difficult
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default Text import and transpose - pretty difficult
Hi
I have a text file (tabe delimitted) that gives tha cash flow of intial
investment. This looks like:
ValDate PoolNo CF_date CF_Amt
08/01/2007 12345 01/01/2007 100.00
08/01/2007 12345 02/01/2007 200.00
08/01/2007 12346 01/01/2007 100.00
08/01/2007 12346 02/01/2007 200.00
08/01/2007 12346 03/01/2007 250.00
08/01/2007 12346 04/01/2007 175.00

I am using the follwoing code to import:

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

This macro basically imports the file in the same row/column format. I want
to transpose the result in excel to look like :
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2 ....
and so on for as many as cashflows given for the same pool no. (so its not a
set number of columns. If there are two rows in the text file for one pool
number (see sample pool no 12345 above) it should look like
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2
08/01/2007 12345 01/01/2007 100.00 02/01/2007 200.00

If there are 10 rows for the same pool no, then I get the ValDate, Pool# and
20 columns for the CF_date (1 to 10) and CF_amt (1 to 10) - All together 22
columns

How can I modify this code to achive this? or any other suggestions...

total cash flows will not exceed 60 months. I dont have to worry about
exceeding the column limitation in Excel 2003.

Thanks
Mohan
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default Text import and transpose - pretty difficult
Are the pool numbers in the original file strictly in order?
--
p45cal


"Mohan" wrote:

Hi
I have a text file (tabe delimitted) that gives tha cash flow of intial
investment. This looks like:
ValDate PoolNo CF_date CF_Amt
08/01/2007 12345 01/01/2007 100.00
08/01/2007 12345 02/01/2007 200.00
08/01/2007 12346 01/01/2007 100.00
08/01/2007 12346 02/01/2007 200.00
08/01/2007 12346 03/01/2007 250.00
08/01/2007 12346 04/01/2007 175.00

I am using the follwoing code to import:

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

This macro basically imports the file in the same row/column format. I want
to transpose the result in excel to look like :
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2 ....
and so on for as many as cashflows given for the same pool no. (so its not a
set number of columns. If there are two rows in the text file for one pool
number (see sample pool no 12345 above) it should look like
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2
08/01/2007 12345 01/01/2007 100.00 02/01/2007 200.00

If there are 10 rows for the same pool no, then I get the ValDate, Pool# and
20 columns for the CF_date (1 to 10) and CF_amt (1 to 10) - All together 22
columns

How can I modify this code to achive this? or any other suggestions...

total cash flows will not exceed 60 months. I dont have to worry about
exceeding the column limitation in Excel 2003.

Thanks
Mohan
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default Text import and transpose - pretty difficult
No.. they are not sorted.
But I could this data into Excel in the same row/column format, sort them
and then copy to another worksheet within the same workbook and this time I
need to transform the cash flow date and amoutn into columns instead of rows.

"p45cal" wrote:

Are the pool numbers in the original file strictly in order?
--
p45cal


"Mohan" wrote:

Hi
I have a text file (tabe delimitted) that gives tha cash flow of intial
investment. This looks like:
ValDate PoolNo CF_date CF_Amt
08/01/2007 12345 01/01/2007 100.00
08/01/2007 12345 02/01/2007 200.00
08/01/2007 12346 01/01/2007 100.00
08/01/2007 12346 02/01/2007 200.00
08/01/2007 12346 03/01/2007 250.00
08/01/2007 12346 04/01/2007 175.00

I am using the follwoing code to import:

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

This macro basically imports the file in the same row/column format. I want
to transpose the result in excel to look like :
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2 ....
and so on for as many as cashflows given for the same pool no. (so its not a
set number of columns. If there are two rows in the text file for one pool
number (see sample pool no 12345 above) it should look like
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2
08/01/2007 12345 01/01/2007 100.00 02/01/2007 200.00

If there are 10 rows for the same pool no, then I get the ValDate, Pool# and
20 columns for the CF_date (1 to 10) and CF_amt (1 to 10) - All together 22
columns

How can I modify this code to achive this? or any other suggestions...

total cash flows will not exceed 60 months. I dont have to worry about
exceeding the column limitation in Excel 2003.

Thanks
Mohan
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default Text import and transpose - pretty difficult

However, all pool numbers will be together. Not necessarily sorted in any
order.
same pool # will not be scattered in the file. so I could look at the next
record (or previous record) to see if the pool # changed (if thats what you
are thinking of doing...)

"Mohan" wrote:

No.. they are not sorted.
But I could this data into Excel in the same row/column format, sort them
and then copy to another worksheet within the same workbook and this time I
need to transform the cash flow date and amoutn into columns instead of rows.

"p45cal" wrote:

Are the pool numbers in the original file strictly in order?
--
p45cal


"Mohan" wrote:

Hi
I have a text file (tabe delimitted) that gives tha cash flow of intial
investment. This looks like:
ValDate PoolNo CF_date CF_Amt
08/01/2007 12345 01/01/2007 100.00
08/01/2007 12345 02/01/2007 200.00
08/01/2007 12346 01/01/2007 100.00
08/01/2007 12346 02/01/2007 200.00
08/01/2007 12346 03/01/2007 250.00
08/01/2007 12346 04/01/2007 175.00

I am using the follwoing code to import:

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

This macro basically imports the file in the same row/column format. I want
to transpose the result in excel to look like :
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2 ....
and so on for as many as cashflows given for the same pool no. (so its not a
set number of columns. If there are two rows in the text file for one pool
number (see sample pool no 12345 above) it should look like
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2
08/01/2007 12345 01/01/2007 100.00 02/01/2007 200.00

If there are 10 rows for the same pool no, then I get the ValDate, Pool# and
20 columns for the CF_date (1 to 10) and CF_amt (1 to 10) - All together 22
columns

How can I modify this code to achive this? or any other suggestions...

total cash flows will not exceed 60 months. I dont have to worry about
exceeding the column limitation in Excel 2003.

Thanks
Mohan
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default Text import and transpose - pretty difficult
See if this does it for you. First the test routine which calls the import
sub:

Sub test()
ImportTextFile "cashflow.txt", Chr(9)
End Sub

You say tab delimited, which is what Chr(9) is doing there.
Now the main bit (if you want headers right across the top, say so):

Public Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim WholeLine As String
Dim ColNdx As Integer
Dim b As Range
Dim HeadersDone As Boolean
Application.ScreenUpdating = False
'On Error GoTo EndMacro:

ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
a = Split(WholeLine, Chr(9))
If a(1) = Cells(RowNdx, ColNdx + 1) Then
Set b = Cells(RowNdx, ColNdx).End(xlToRight)
b.Offset(, 1) = a(2): b.Offset(, 2) = a(3)
Else
If HeadersDone Then RowNdx = RowNdx + 1
Cells(RowNdx, ColNdx).Resize(, 4) = a
HeadersDone = True
End If
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub

--
p45cal


"Mohan" wrote:


However, all pool numbers will be together. Not necessarily sorted in any
order.
same pool # will not be scattered in the file. so I could look at the next
record (or previous record) to see if the pool # changed (if thats what you
are thinking of doing...)

"Mohan" wrote:

No.. they are not sorted.
But I could this data into Excel in the same row/column format, sort them
and then copy to another worksheet within the same workbook and this time I
need to transform the cash flow date and amoutn into columns instead of rows.

"p45cal" wrote:

Are the pool numbers in the original file strictly in order?
--
p45cal


"Mohan" wrote:

Hi
I have a text file (tabe delimitted) that gives tha cash flow of intial
investment. This looks like:
ValDate PoolNo CF_date CF_Amt
08/01/2007 12345 01/01/2007 100.00
08/01/2007 12345 02/01/2007 200.00
08/01/2007 12346 01/01/2007 100.00
08/01/2007 12346 02/01/2007 200.00
08/01/2007 12346 03/01/2007 250.00
08/01/2007 12346 04/01/2007 175.00

I am using the follwoing code to import:

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

This macro basically imports the file in the same row/column format. I want
to transpose the result in excel to look like :
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2 ....
and so on for as many as cashflows given for the same pool no. (so its not a
set number of columns. If there are two rows in the text file for one pool
number (see sample pool no 12345 above) it should look like
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2
08/01/2007 12345 01/01/2007 100.00 02/01/2007 200.00

If there are 10 rows for the same pool no, then I get the ValDate, Pool# and
20 columns for the CF_date (1 to 10) and CF_amt (1 to 10) - All together 22
columns

How can I modify this code to achive this? or any other suggestions...

total cash flows will not exceed 60 months. I dont have to worry about
exceeding the column limitation in Excel 2003.

Thanks
Mohan
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 107
Default Text import and transpose - pretty difficult
I wish there was a way to etract posts. It's disappointing not to get a
response from the opriginal poster after you've clearly spent significant
time and effort solving THEIR problem.
--
p45cal


"p45cal" wrote:

See if this does it for you. First the test routine which calls the import
sub:

Sub test()
ImportTextFile "cashflow.txt", Chr(9)
End Sub

You say tab delimited, which is what Chr(9) is doing there.
Now the main bit (if you want headers right across the top, say so):

Public Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim WholeLine As String
Dim ColNdx As Integer
Dim b As Range
Dim HeadersDone As Boolean
Application.ScreenUpdating = False
'On Error GoTo EndMacro:

ColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
a = Split(WholeLine, Chr(9))
If a(1) = Cells(RowNdx, ColNdx + 1) Then
Set b = Cells(RowNdx, ColNdx).End(xlToRight)
b.Offset(, 1) = a(2): b.Offset(, 2) = a(3)
Else
If HeadersDone Then RowNdx = RowNdx + 1
Cells(RowNdx, ColNdx).Resize(, 4) = a
HeadersDone = True
End If
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
End Sub

--
p45cal


"Mohan" wrote:


However, all pool numbers will be together. Not necessarily sorted in any
order.
same pool # will not be scattered in the file. so I could look at the next
record (or previous record) to see if the pool # changed (if thats what you
are thinking of doing...)

"Mohan" wrote:

No.. they are not sorted.
But I could this data into Excel in the same row/column format, sort them
and then copy to another worksheet within the same workbook and this time I
need to transform the cash flow date and amoutn into columns instead of rows.

"p45cal" wrote:

Are the pool numbers in the original file strictly in order?
--
p45cal


"Mohan" wrote:

Hi
I have a text file (tabe delimitted) that gives tha cash flow of intial
investment. This looks like:
ValDate PoolNo CF_date CF_Amt
08/01/2007 12345 01/01/2007 100.00
08/01/2007 12345 02/01/2007 200.00
08/01/2007 12346 01/01/2007 100.00
08/01/2007 12346 02/01/2007 200.00
08/01/2007 12346 03/01/2007 250.00
08/01/2007 12346 04/01/2007 175.00

I am using the follwoing code to import:

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

This macro basically imports the file in the same row/column format. I want
to transpose the result in excel to look like :
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2 ....
and so on for as many as cashflows given for the same pool no. (so its not a
set number of columns. If there are two rows in the text file for one pool
number (see sample pool no 12345 above) it should look like
Val Date Pool # CF_date1 CF_Amt1 CF_date2 CF_Amt2
08/01/2007 12345 01/01/2007 100.00 02/01/2007 200.00

If there are 10 rows for the same pool no, then I get the ValDate, Pool# and
20 columns for the CF_date (1 to 10) and CF_amt (1 to 10) - All together 22
columns

How can I modify this code to achive this? or any other suggestions...

total cash flows will not exceed 60 months. I dont have to worry about
exceeding the column limitation in Excel 2003.

Thanks
Mohan
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
Automate 'Difficult' importing text file MArk Excel Programming 4 May 29th 07 05:30 AM
Help: Import text transpose append to new worksheet javamom Excel Programming 4 April 20th 06 03:29 AM
difficult transpose? redb Excel Programming 6 October 4th 05 10:06 AM
Import and transpose tab seperated data from text file phillip harrison Excel Programming 0 July 22nd 03 05:44 PM
Import and transpose tab seperated data from text file phillip harrison Excel Programming 1 July 17th 03 03:48 PM


All times are GMT +1. The time now is 03:51 AM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement