text matrix translation

[nir_k]

Hi experts,

i have a challenge.

i have a text file with the following data


12345
-----
A |xxxxx
A1 |.x...
A2 |..x.x

I need to get an output better on text (excell is limited with rows and the matrix can have up to 5000 rows and lines)
i tried to open the file with excel but every line is a string and i can't separet it

the output will be

A 1,2,3,4,5
A1 ,2,
A2 ,3, ,5


it will save me a lot of time

tnx

Nir

 

[jindon]

Yeah I missed one word "Preserve"

Sub test()
Dim fn As String, delim As String, ff As Integer
Dim a(), i As Long, n As Long, txt As String
delim = "."  '<- change here for delimiter of .txt file
fn = "c:\temp\test.txt"
ff = FreeFile
Open fn For Input As #ff
     Do While Not EOF(ff)
          Line Input #ff, txt
          txt = Replace(txt,"|",delim)
          n = n + 1
          ReDim Preserve a(1 To n) '<- this line
          a(n) = Split(txt,delim)
     Loop
Close #ff
With ThisWorkbook.Sheets(1).Range("a1")
     For i = 1 To n
          .Offset(i-1).Resize(,UBound(a(i))+1).Value = a(i)
     Next
End With
End Sub

 

[nir_k]

jindon,

many tnx

i run the macro and got
‏123456789
----------
A xxxxxxxxxx
A1 x
A2 x x
A3 xx
A4 xx
A5 x x
A6 x
A7 x
A8 x x
A9 x
A10 x

some of the "x" are not separeted

but i need to see this

1 2 3 4 5 6 7 8 9 10
----------
A 1 2 3 4 5 6 7 8 9
A1 2
A2 3 8
A3 3 4
A4 4 5
A5 1 5
A6 6
A7 7
A8 4 7
A9 8
A10 9

my problem is to read the matrix and i need the translation to numbers

tnx

nir

 

[jindon]

nir,

It is hard to see guess what you wanted from wha you have posted.

It is fully depends on how your text data constructed.

the code:
1) Replace "|" with a period "."
2) split one line of text data by a comma
3) output in each column as it splited.

therefore, it any line in the text file has inappropreate number of periods, it will fail to allocate the values into the appropreate column.

If you could show us a screenshot of your original data and how you wanted as a result, it may help to understand.

 

[nir_k]

jindon,

how can i send you the spreadsheet?

Mail?

nir

 

[nir_k]

an explanantion for my need

Hi ,


the text file i have look like this (it can be bigger matrix)


i need to get it in to the excel
like this


and then transform it to look like this:


this i do all day long
i have matrix in the text file in all sizes, here is another example (all in the same format)



thank in advance

Nir

 

[jindon]

Can you just enlarge the text file?

 

[nir_k]

or you can copy this to a text file

000100, xxxxxxxxxxxxx 2007-07-26 17:20 xxxx xxxxxxxxxxx 1992-2006 Page 1
------------------------------------------------------------------------------
LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL
123456789111111111122222222223333333333444444444455555555556666666666777777
012345678901234567890123456789012345678901234567890123456789012345

---------------------------------------------------------------------------
K1 |xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
K2 |......xxxxxx.xx..xxx..xx....xx..x......xxxxxxxx.xxxxx.xxxxxxx...x.xxxxxxxxx
K3 |......xxxxxx.xx..xxx..xx........x......x.xxxxxx..xx.x.xxxxx.x...x.xxxxx..xx
K4 |......xxxxxx......x......x.................................................
K5 |......xxxxxx..x...x...xx.x...............xx........................xx.x..x.
K6 |xx........xxx.xxxxxxxxx..x.......................xx.x.xx.xx.x.x.xx.........
K7 |.........................x......x.......x...x..xx.................x......xx
K8 |xxxxxx....xxx..x.xxxxx.......................xxx.xx.x.xx..xxx.x.xx.........
K9 |..x.......xxx..xxxxxxxx.....................x..x.......x..xxx.x.xx.........
K10|xxxxxx....xxx...xxxxxx...x............................xxx.xxxxxxxx.........
K11|.xxxx.....xxx...x.x.x..................................x..xxxxxxxx.........
K12|..........xx......x....................................x..x...x..........xx
K13|..........xx......x.xx.................................x..x...x............
K14|............................................xxxx......x..xx.x...x..........
K15|......xxxxx.x.....x.........................xxx..........xx.....x..........
K16|..........x.................................xxx..........xx.....x..........
K17|..........x.................................xxx..........xx.....x..........
K18|..........x.x.....x........................................................
K19|..........x.x.....x......................................xx.x...x..........
K20|..........x................................................................
K21|...........................................................................
K22|...........................................................................
K23|............................................xxxx.....xx..x..x...x..........
K24|.........................................................xx.....x..........
K25|xxxxxx.........x.xxxxxx.....................xxx.......x..xx.....x..........
K26|............x....xxx.....................x..x.x.......x..xx.....x.x...x...x
K27|..........................xx.xxx.xxxxxxxx..................................
K28|............................x..............xxxx....x..x..xxxx...x.x..x.xx..
K29|...........................................................................
K30|...........................................................................
K31|...........................................................................
K32|...........................................................................
K33|...........................................................................
K34|...........................................................................

tnx

nir

 

[jindon]

1) CCCC or LLLL what ever the letter is, does the length of its line always match with the numbers in next two lines?
i.e. in above example, Len(LLLLL...) = 75?

2) do you really need to output like table 2 above?