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]

try

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:\test\test.txt"  '<- change here
ff = FreeFile
Open fn For Input As #ff
     Do While EOF(ff)
          Line Input #ff, txt
          n = n + 1
          ReDim a(1 To n)
          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]

tnx for the repalay,

i tried to run it on

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.


but it didnt work
is it suppose to open a new text file?

Nir

 

[jindon]

How is your txt file delimited?
Post your code with the change

 

[nir_k]

Jindon,

Sorry i dont understand what you mean
my text file looks like this (just copy it to *.text file)
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.

i didnt open the text file with excel becouse every line in the text file goes into one cell and ican't separet it

so i dont andeerstand what you ment by
delim = "," '<- change here for delimiter of .txt file

what should it be now that you know how my text file looks

tnx

Nir

 

[Thorin]

Try Data--> Import Extarnal Data --> Import Data, select your file, and make sure the file type is delimited, choose the row number to start the import from, I assume from your data that it will be row 3.

Click next, and in delimiters untick the comma box, and under Other : type in the pipe symbol (in other words the | symbol, copy and paste the one here if you are unsure).

Click finish and select where you want to copy it to.

Does this solve it for you ?

 

[jindon]

If it is exactly so.

Sub test()
Dim fn As String, delim As String, ff As Integer
Dim a(), i As Long, n As Long, txt As String, x, y, z()
delim = "."  '<- change here for delimiter of .txt file
fn = "c:\test\test.txt"  '<- change here
ff = FreeFile
Open fn For Input As #ff
     Do While EOF(ff)
          Line Input #ff, txt
          x = Split(txt,"|")
          y = Split(x(1),delim)
          ReDim z(UBound(y) + 1)
          z(0) = x(0)
          For i = 0 To UBound(y)
               z(i+1) = y(i)
          Next
          n = n + 1
          ReDim a(1 To n)
          a(n) = z
     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,

Please discribe me the process what i need to do

i copy the code into the excel in a new modul

i have a text file name test.txt with the matrix in c:\temp

and...

i believe uts somthing i do wrong

when i follow the code it's entering the "Do While EOF(ff)" loop
but goes to the end of the code

tnx

nir

 

[jindon]

Sorry my mistake
Few changes made

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) '<- this line
          Line Input #ff, txt
          txt = Replace(txt,"|",delim)
          n = n + 1
          ReDim a(1 To n)
          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]

tnx for the time

istill have a debag massege

in this line

.Offset(i - 1).Resize(, UBound(a(i)) + 1).Value = a(i)


nir