[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Excel to text file

Hello all,

I need your help to modify a macro i have.

The macro will take all columns from the excel sheet and transfer data in a text file based on this part of the code:
 vFieldArray = Array(1, 1, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)

Open in new window


But here is my problem.

There is about 25 columns where I have always the same value. All the rest varies and populated by me.

So what i want to do, is to have only 16 columns, and in my macro, extract them to the text file but force some vFieldArray  to have specific text.


Ex:

Column A = Test 1
Column B = Test 2
Column C = Test 3

But what i will get has a final result would be:
Array1  = "H"
Array1  = "A"
Array12  = Column A text = Test 1
Array6  = Column B text = Test 2 etc...

Is that possible?

Does it make sense?

Thanks again for your help





Full code
Public Sub GenerateFormattedOrderImportFile()
        Const DELIMITER As String = "" 'Normally none
        Const PAD As String = " "   'or other character
        Dim vFieldArray As Variant
        Dim myRecord As Range
        Dim nFileNum As Long
        Dim i As Long
        Dim sOut As String
        
        'vFieldArray contains field lengths, in characters, from field 1 to N
        vFieldArray = Array(1, 1, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)
        nFileNum = FreeFile
        Open "C:\Documents and Settings\All Users\Desktop\ordtemp." For Output As #nFileNum
        For Each myRecord In Range("A11:A" & _
                Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                For i = 0 To 41
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
                Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
                For i = 42 To UBound(vFieldArray)
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
                Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
            End With
        Next myRecord
        Close #nFileNum
        
       
        
        
 ' Open our source file
  f1 = FreeFile
  Open "C:\Documents and Settings\All Users\Desktop\ordtemp." For Input As #f1

  ' Open our target file
  f2 = FreeFile
  Open "C:\Documents and Settings\All Users\Desktop\ord.02" For Output As #f2

  ' Start processing till we reach the end
  Do While Not EOF(f1)
    ' Read a line from the source file
    Line Input #f1, x

    ' And if the line isn't empty, write it to the target file
    If x <> "                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            " Then
      If x <> "                                                                                                                                                      " Then
      Print #f2, x
    End If
    End If
  Loop

  ' Close the two opened files
  Close #f2
  Close #f1
        
        'Delete the unecessary temp file
        strFile = "C:\Documents and Settings\All Users\Desktop\ordtemp."

        Set fs = CreateObject("Scripting.FileSystemObject")

        fs.deletefile strFile


        
         'This formats name of the text file to be ord.YYYYMMDDHHMI
        OldFile1 = "C:\Documents and Settings\All Users\Desktop\ord.02"
        newfile1 = Format(Now, "yymmddhhmmss")
        newfile1 = OldFile1 & newfile1
        Name OldFile1 As newfile1
        

        MsgBox "File Creation Complete"
    End Sub

Open in new window

0
Wilder1626
Asked:
Wilder1626
  • 12
  • 11
1 Solution
 
Arno KosterCommented:
That certainly is possible.
If I get it right you want to minimize the amount of information stored in the text files.

if the columns with fixed data are always the same columns, you could completely take these out of the text file and insert constant values instead.
Otherwise, you could insert a number of definition lines such as e.g.

constant value 'H' column 1
constant value 'A' column 2, 3,  12, 26
0
 
Wilder1626Author Commented:
You are almost there.

I want to remove in excel some columns where it is always the same value, but still have it in the text file when created.

Ex: "H" and "A" are default value. So i don't what them in the excel file, but when i will create the text file, it will add them in.
0
 
regmigrantCommented:
I'm not entirely sure I got the same from your question as akoster but I *think* what you need to do is add the constants into the 'i' loops where you are building the output string using a case statement:-

For  example:

For i = 0 To 41
  select Case i
    Case 12
       sOut = sOut & DELIMITER & "Test1"
    Case 6
       sOut = sOut & DELIMITER & "Test2"
    Case 13,14
      sOut = sOut & DELIMITER & "Test3"
    Case 21 to 25
      sOut = sOut & DELIMITER & "Test4"
    Case Else
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
Next i

Perhaps a before and after example (with a smaller array!) would help ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Arno KosterCommented:
Ah.

do you want to visually get rid of the columns or you want them to be removed ?

if visibility is the problem, the easiest way would be to hide the columns because you then would not have to change the macro.

when you want to remove the columns it might be wise to write out the export functionality like this :

for row = 11 to range("A" & rows.count).end(xlup).row
   sOut = process(cells(1,1), 1)                                      '-- first entry so no delimiter
   sOut = sOut & DELIMITER & process(cells(2,1), 1)     '-- second entry, dynamic
   sOut = sOut & DELIMITER & process(cells(3,1), 12)   '-- dynamic entry
   sOut = sOut & DELIMITER & process(cells(4,1), 6)     '-- dynamic entry
   sOut = sOut & DELIMITER & process(cells(5,1), 6)     '-- dynamic entry
   sOut = sOut & DELIMITER & process(cells(6,1), 12)   '-- dynamic entry   
   sOut = sOut & DELIMITER & "fixed text"                    '-- static entry
   sOut = sOut & DELIMITER & process(cells(26,1), 11)   '-- dynamic entry
   [....]

Open in new window


and include the process function

function process(value, size) as string
    process = left(value & string(size, " "), size)
end function

Open in new window


another option (less work when only a small amount of fixed columns involved) is to change the macro to

For Each myRecord In Range("A11:A" & _
   Range("A" & Rows.Count).End(xlUp).Row)
   With myRecord
      For i = 0 To 41
         select case i
            case 5
               sOut = sOut & DELIMITER & "fixed text for column 5"
            case 12
               sOut = sOut & DELIMITER & "fixed text for column 12"
            case else
               sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
         end select
      Next i

Open in new window


0
 
Wilder1626Author Commented:
That would be an example of what i need to do.

Excel sheet, you will see the 3 column with data, and the .doc file, just remove the.doc and you will have the text file example.
ord.02111004105914.doc
ex.xlsx
0
 
Arno KosterCommented:
with this updated version
example-excel-text-to-file.xls

i get this result
ordtemp
0
 
Arno KosterCommented:
in order to use the ex.xlsx version as template, it would come down to

 ex.xlsm
0
 
Wilder1626Author Commented:
Hello akoster

From the example you gave me, you have added the H and A in column A and B.

I want to be able to put Test1 in column A and it would still put in the text file HA before the Test1..
0
 
Arno KosterCommented:
my bad, should have directly used the supplied ex.xlsx file to build upon.
Have you checked the ex.xlsm file ?
0
 
Wilder1626Author Commented:
yes it is working.


If i need to add more automation like the H and A, but after column C, i'm not to sure how to manage that based on the code:


Public Sub GenerateFormattedOrderImportFile()
        Const DELIMITER As String = "" 'Normally none
        Const PAD As String = " "   'or other character
        Dim vFieldArray As Variant
        Dim myRecord As Range
        Dim nFileNum As Long
        Dim i As Long
        Dim sOut As String
        
        'vFieldArray contains field lengths, in characters, from field 1 to N
        vFieldArray = Array(36, 26, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)
        nFileNum = FreeFile
        Open "C:\Documents and Settings\All Users\Desktop\ordtemp." For Output As #nFileNum
        'Open "C:\Documents and Settings\All Users\Desktop\temp\ordtemp.txt" For Output As #nFileNum
        For Each myRecord In Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                .Select
                sOut = "H" & DELIMITER & "A"
                For i = 0 To 41
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
                Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
            End With
        Next myRecord
        Close #nFileNum
        
End Sub

Open in new window

0
 
Arno KosterCommented:
if you want to place 'X' after column C and 'Q' after column E, use :


[...]
For i = 0 To 41
   sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
   '-- column C : i=2
   if i = 2 then sOut = sOut & DELIMITER & "X"
   if i = 4 then sOut = sOut & DELIMITER & "Q"
Next i
[...]

Open in new window


when you place the additional code below the sout = sout  & delimiter & left([...] line, entries will be placed after the indicated column.
when you place the additional code above the sout = sout  & delimiter & left([...] line, entries will be placed before the indicated column.
0
 
Wilder1626Author Commented:
Hello akoster

Sorry for the delay,

OK, let me try this. I will give you the feed back.

Thanks again
0
 
Arno KosterCommented:
no problem !
0
 
Wilder1626Author Commented:
Hello akoster,

It is working. But small question, If for example, after the 3\4 of the excel row that as been transferred in the text file, i need to transfer the last columns in the row just under the first on in the text file, how can i do that:

Ex:
HAMH2440446001WESTOBHOST  24          Example                   20100925110020100926170020100925000120100926170024                                             COL                                                                       M                                                                                                                                                                                                                                                                                  
P MH2440446001WESTOB100   1100                                  15                               

Open in new window


In the excel sheet, this is only 1 row, but in the text file, it is 2, the second, starting with "P".

Is that possible?
0
 
Wilder1626Author Commented:
Ohh that is working like that

 For Each myRecord In Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row)
            With myRecord
                .Select
                sOut = "H" & DELIMITER & "A"
             For i = 0 To 4
   sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
   '-- column C : i=2
   If i = 2 Then sOut = sOut & DELIMITER & "H"
   If i = 4 Then sOut = sOut & DELIMITER & "A"
   If i = 6 Then sOut = sOut & DELIMITER & Format(Now, "yyyymmddhhmmss")
     Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
   

                sOut = "P" & DELIMITER
    For i = 5 To UBound(vFieldArray)
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
                            
                             Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
                
        End With

Open in new window

0
 
Wilder1626Author Commented:
Hello again,

I'm doing lots of test right now and i dont see that it follow these criterias:
vFieldArray = Array(36, 26, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)

Open in new window


The arrays should be where the column data / value starts on the text file.

But is does not if i use :
   If i = 0 Then sOut = sOut & DELIMITER & Range("E1").Text
   If i = 1 Then sOut = sOut & DELIMITER & "HOST"
   

Open in new window



If i = 0 Then sOut = sOut & DELIMITER & Range("E1").Text should be just after column A, at position 19 on the test file,

and

 If i = 1 Then sOut = sOut & DELIMITER & "HOST" should be just after but at position 25 on the text file.

after these 2, then it return to column B of the excel sheet.

but if i remove this conditions, everything is in place, based on Array(36, 26, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)



0
 
Wilder1626Author Commented:
In a quick word,  all if condition does nor respect the vFieldArray . It just follow right away the column data result.

But if a result from a column is transferred in the text file, the IF condition may not be just right after it.

ex:

column 2 = Test 1
If condition = Test2

The result could be: "Test1            Test 2" and not automatically "Test1Test2"

That is way i need to follow the vFieldArray : (36, 26, 12, 6, 6, 12, 12, 11, 3, 12, 12, 12, 12, 12, 1, 20, 12, 1, 1, 3, 1, 12, 20, 11, 20, 7, 20, 11, 1, 12, 12, 12, 12, 1, 20, 12, 12, 12, 1, 50, 12, 75, 1, 1, 12, 6, 6, 9, 6, 6, 12, 5, 35, 1, 50)

Does it make sense?
0
 
Arno KosterCommented:
that is correct. If you want to have the added texts to also follow the vfieldarray text lengths, you can update lines like

If i = 2 Then sOut = sOut & DELIMITER & "H"

Open in new window


to

If i = 2 Then sOut = sOut & DELIMITER & Left("H" & String(vFieldArray(i), PAD), vFieldArray(i))

Open in new window


pay attention though that the use of variable i to link to the 'i' est column in the worksheet requires carefull consideration on the setup of the vFieldArray.
when the i=2 variable inserts contents of cell C2 with length 12 characters, the "H" letter will also have a length of 12 characters.
0
 
Arno KosterCommented:
instead it might be a good idea to insert defined texts with hardcoded lengths like this :

[...]
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & String(vFieldArray(i), PAD), vFieldArray(i))
[...]
If i = 0 Then sOut = sOut & DELIMITER & left(Range("E1").Text & string(10, PAD), 10)
if i = 1 Then sOut = sOut & DELIMITER & left("HOST" & string(20, PAD), 20)
If i = 2 Then sOut = sOut & DELIMITER & Left("H" & String(12, PAD), 12)
[...]

Open in new window

0
 
Wilder1626Author Commented:
Hello akoster

Something is strange here

if i do this:
If i = 0 Then sOut = sOut & DELIMITER & Left(Range("E2").Text & String(16, PAD), 6)
If i = 0 Then sOut = sOut & DELIMITER & Left("HOST" & String(21, PAD), 4)

or

If i = 0 Then sOut = sOut & DELIMITER & Left(Range("E2").Text & String(20, PAD), 6)
If i = 0 Then sOut = sOut & DELIMITER & Left("HOST" & String(28, PAD), 4)

I have the same result.


Question for you, is there a way to use this for everything? even to manage the filed for the column?

If i = 0 Then sOut = sOut & DELIMITER & Left("H" & String(1, PAD), 1)
If i = 1 Then sOut = sOut & DELIMITER & Left("A" & String(2, PAD), 1)
If i = 2 Then sOut = sOut & DELIMITER & Left(COLUMN A.Text & String(3, PAD), 12)
If i = 2 Then sOut = sOut & DELIMITER & Left(Range("E2").Text & String(15, PAD), 6)
If i = 3 Then sOut = sOut & DELIMITER & Left("HOST" & String(22, PAD), 4)


Ex: (0, PAD), 1) = Space 1 on the file, with a length of 1 digit.
(1, PAD), 1) =  Space 2 on the file, with a length of 1 digit.
(3, PAD), 12) =  Space 3 on the file, with a length of 12 digit......
0
 
Arno KosterCommented:
this is not strange, it's the way it is supposed to be.

the part after the delimiter consists of two functions :

 - left
 - string

for simplicity, let's say that E2 contains 'A'
if you say range("E2").text & string(16, "*"); thus command is 'add 16 characters after text in E2'; you will get
[A****************]
if you then use left(range("E2").text & string(16, "*"), 6); thus command is 'take the first 6 characters of this'; you will get
[A*****]

which is the same as saying 'take the first 6 characters of (add 20 characters after text in E2)'



0
 
Arno KosterCommented:
to answer you second question : yes, that is certainly possible, e.g.

If i = 0 Then sOut = sOut & DELIMITER & Left("H" & String(1, PAD), 1)
If i = 1 Then sOut = sOut & DELIMITER & Left("A" & String(2, PAD), 1)
If i = 2 Then sOut = sOut & DELIMITER & Left(.Offset(0, 0).Text & String(3, PAD), 12)
If i = 2 Then sOut = sOut & DELIMITER & Left(Range("E2").Text & String(15, PAD), 6)
If i = 3 Then sOut = sOut & DELIMITER & Left("HOST" & String(22, PAD), 4)

the .offset function works on myRecord like this :
myrecord is a cell in column A

.offset(0,0) is the same cell
.offset(0,1) is the cell in B column
.offset(0,2) is the cell in C column
etc

instead of .offset(0,0).text you can also directly write .text because it takes into account the same cell. For other columns the offset function need to be used.
0
 
Wilder1626Author Commented:
Thanks,

I will try this and let you know the result.
0
 
Wilder1626Author Commented:
Hello,

Sorry for the delay. Thanks again for your help, now it work.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now