Creating text file from a range of numbers

[arishy]

I have csv file of n lines formatted as follows:
40600 usec, 3560 usec
1560 usec, 580 usec
260 usec, 580 usec ....etc

For EACH line I need to create TWO lines as follows:

delayMicroseconds(40600);
pulseIR(3560); ...etc

I tried to record a macro to start me off , but I failed to simulate the situation.
Can any one direct me to a link to kickstart this VBA macro

 

[Marc L]

Post the macro you tried !

Join a source file and a desired result file …​

 

[Hui]

Pls find a Formula Solution

Assuming your data is in Column A
In B1:
=CHOOSE(2-MOD(ROW(),2),"delay Microseconds("&LEFT(SUBSTITUTE(OFFSET(A$1,INT(ROW()/2-0.1),)," usec",""),FIND(",",SUBSTITUTE(OFFSET(A$1,INT(ROW()/2-0.1),)," usec",""))-1)&");","pulseIR("&RIGHT(SUBSTITUTE(OFFSET(A$1,INT(ROW()/2-0.1),)," usec",""),LEN(SUBSTITUTE(OFFSET(A$1,INT(ROW()/2-0.1),)," usec",""))-FIND(", ",SUBSTITUTE(OFFSET(A$1,INT(ROW()/2-0.1),)," usec",""))-1)&");")

Copy down

See attached file:

 

[arishy]

The code is really not worth looking at, but here it is anyway

Sub Macro1()
'
' Macro1 Macro
'

'
  Range("J1").Select
  ActiveCell.FormulaR1C1 = "delayMicroseconds("
  Range("A1").Select
  Selection.Copy
  ActiveSheet.Paste
  Range("J1").Select
  ActiveSheet.Paste
  ActiveCell.FormulaR1C1 = ");"
End Sub

 

[arishy]

Hui,

Master piece of work.....

The Remote I am working on has about 50 such files (But I can create just one to join them all)
So, to automate it ; the VBA code must do :
1. Read the file
2. Dump each key data in a separate sheet ( 50 sheets)
3. Insert your magic formula into double the Rows for each sheet
4. Create a New file
5. Extract the result of your formula for each sheet and output it to the file
6. repeat 50 times

WoW....Am I on the right track ??

That will keep me busy for sometime...

 

[Hui]

Try some code like below:

Sub Import_File()
Dim myArr As Variant
Dim cr As Integer, i As Integer

  ChDir "C:\Users\Ian\Downloads" 'Change path to suit
  Workbooks.OpenText _
  Filename:="C:\Users\Dads\Downloads\IN.txt", _
  Origin:=xlMSDOS, _
  StartRow:=5, _
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=False, _
  Tab:=True, _
  Semicolon:=False, _
  Comma:=True, _
  Space:=False, _
  Other:=False, _
  FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
  TrailingMinusNumbers:=True  'Change path to suit

  Windows("IN.txt").Activate

  Range("C1").Select

  myArr = Range("A1").CurrentRegion.Value

  cr = 1
  For i = 1 To UBound(myArr, 1)
  Cells(cr, 3) = "delayMicroseconds(" + CStr(Left(myArr(i, 1), InStr(1, myArr(i, 1), " usec"))) + ");"
  cr = cr + 1
  Cells(cr, 3) = "pulseIR(" + CStr(Left(myArr(i, 2), InStr(1, myArr(i, 2), " usec"))) + ");"
  cr = cr + 1
  Next

  Range("A:B").Delete

End Sub

 

[Hui]

This code will loop through every txt file in the directory and convert them all into a xlsx file

Sub Import_File()
Dim myArr As Variant
Dim cr As Integer, i As Integer
Dim StrFile As String
Dim myPath As String

myPath = "C:\Users\Ian\Downloads\"
StrFile = Dir(myPath + "*.txt") 'Change Directory to suit
Do While Len(StrFile) > 0
   
  Workbooks.OpenText _
  Filename:=myPath + StrFile, _
  Origin:=xlMSDOS, _
  StartRow:=5, _
  DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, _
  ConsecutiveDelimiter:=False, _
  Tab:=True, _
  Semicolon:=False, _
  Comma:=True, _
  Space:=False, _
  Other:=False, _
  FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
  TrailingMinusNumbers:=True
 
  Windows(StrFile).Activate
 
  Range("C1").Select
 
  myArr = Range("A1").CurrentRegion.Value
 
  cr = 1
  For i = 1 To UBound(myArr, 1)
  Cells(cr, 3) = "delayMicroseconds(" + CStr(Left(myArr(i, 1), InStr(1, myArr(i, 1), " usec"))) + ");"
  cr = cr + 1
  Cells(cr, 3) = "pulseIR(" + CStr(Left(myArr(i, 2), InStr(1, myArr(i, 2), " usec"))) + ");"
  cr = cr + 1
  Next
 
  Range("A:B").Delete

  Range("A:A").EntireColumn.AutoFit
 
  ActiveWorkbook.SaveAs _
  Filename:=myPath + Left(StrFile, Len(StrFile) - 4) + ".xlsx", _
  FileFormat:=xlOpenXMLWorkbook, _
  CreateBackup:=False
  ActiveWorkbook.Close

StrFile = Dir
Loop

End Sub

 

[Marc L]

Arishy,

1) the source file is overwrited after processing or need a specific output file ?

Check your attached file OUT.txt :
2a) First line starts with a space, typo or not ?

2b) Need tabulation char between number & parenthesis or a constant spaces number ?

Like a fixed format text file …​

 

[arishy]

Hui,

Both macros worked like charm. This way I do not have to create ID for each key, the file IS the key.
I am ready to program my remote.

Thank Hui, I really hope I can do (one day !!!) what you are doing to the members of this forum.
You ARE star, guiding us and helping us to master this wonderful tool.

God bless you

 

[arishy]

Marc L,

The input file is IN.txt , the Macros should create the OUT.txt file.
Also , spacing is not important these lines(in the OUT.txt) will be part of a program that accept loose formatting of spaces.
I hope I answered your questions.

 

[Marc L]

This is a pure VBA memory way :​

Sub ArrangeFile(INfile$, OUTfile$)
    If Dir(INfile) = "" Then Exit Sub
    F% = FreeFile
    Open INfile For Input As #F:  LF = Split(Input(LOF(F), #F), vbNewLine):  Close #F

    For Each L In LF
        If InStr(L, " usec, ") Then
            SP = Split(L)
            If UBound(SP) > 1 Then If Val(SP(0)) And Val(SP(2)) Then _
                S$ = S$ & IIf(S$ > "", vbNewLine, "") & "delayMicroseconds(" & _
                     vbTab & SP(0) & vbTab & ");" & vbNewLine & "pulseIR(" & _
                     vbTab & SP(2) & vbTab & ");"
        End If
    Next

    Erase LF, SP
    If S > "" Then Open OUTfile For Output As #F: Print #F, S: Close #F
End Sub


Sub Demo()
    ArrangeFile "D:\Tests\IN.txt", "D:\Tests\OUT.txt"
End Sub

 

[arishy]

Marc L,

The macro worked. Thank you for taking the time to show me how it is done.
Since I have 50+ IN's I will modify it to loop through the bunch.
It is really funny, this solution has nothing to do with Excel. It could be ms word and it will still work.
This is a elegant solution.

There is no limit to the human imagination, that what makes life worth living.
Thank you again