Export Data to Text file with fix format

  • Thread starter Thread starter Rushna
  • Start date Start date
R

Rushna

Hello All,
I am using Excel 2003 and have data in Range D10:Q(20,000+ rows).
I wish to export the all active rows in the range in the Excel file
to a text file (Notepad).

Details are as follow:

Col C (currently 4 digits) – to be exported (total 9 digits) with
trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank)
(blank)(blank)
Col F (currently 8 digits) – to be exported (total 10 digits) with two
leading blanks (eg 12345678 should show as 0012345678)
Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col K (currently -43,413.20) – to be exported as total 15 digits with
leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should
display as 000000004341320 and -1,234.23 should display as
000000000123423 etc)
Col L – to be exported with two trailing blanks (currently EUR –
should display as EUR(blank)(blank).
Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is
blank nothing should be exported

The output on each line in the text file should be a total width of 66
(including spaces) from various Columns in Excel as explained above
and displayed as follows:

1234(blank)(blank)(blank)(blank)
(blank)00123456782008022120080221000000004341320EUR(blank)
(blank)AABBCCDDXXX (this counts to 66 including blank spaces)

Thanks in advance.

Rushna
 
Saved from a previous post:

You could concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Drag it down the column to get all that fixed width stuff.

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/[email protected]
 
Can you clarify a couple of things? You say your data is in the range
D10:Q(20000+rows), but your field description shows the first column as C,
not D... which is it? If C is correct, what about column's D and E; if C
should have been D, then what about column E? I added up all the "data" you
laid out and get 63 characters, not the 66 you said would be there... if 66
is correct, and assuming I did not miscount, where do the additional 3
spaces go... in front or at the back of the 66 character record?

Rick


Hello All,
I am using Excel 2003 and have data in Range D10:Q(20,000+ rows).
I wish to export the all active rows in the range in the Excel file
to a text file (Notepad).

Details are as follow:

Col C (currently 4 digits) – to be exported (total 9 digits) with
trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank)
(blank)(blank)
Col F (currently 8 digits) – to be exported (total 10 digits) with two
leading blanks (eg 12345678 should show as 0012345678)
Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221)
Col K (currently -43,413.20) – to be exported as total 15 digits with
leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should
display as 000000004341320 and -1,234.23 should display as
000000000123423 etc)
Col L – to be exported with two trailing blanks (currently EUR –
should display as EUR(blank)(blank).
Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is
blank nothing should be exported

The output on each line in the text file should be a total width of 66
(including spaces) from various Columns in Excel as explained above
and displayed as follows:

1234(blank)(blank)(blank)(blank)
(blank)00123456782008022120080221000000004341320EUR(blank)
(blank)AABBCCDDXXX (this counts to 66 including blank spaces)

Thanks in advance.

Rushna
 
You description has some wholes. I did the best I could. The code may
require minor changes.

Sub WriteText()
Const myFileName = "c:\temp\myfile.csv"
Const ForReading = 1, ForWriting = 2, _
ForAppending = 3

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName = False Then
MsgBox ("Cannot Save File - exiting Sub")
Exit Sub
End If


Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile _
(fileSaveName, True)

LastRow = Range("C" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow

MyNumber = Format(Range("C" & RowCount).Value, "####")
outputLine = MyNumber & " "
MyNumber = Format(Range("F" & RowCount).Value, " ########")
outputLine = outputLine & MyNumber
MyDate = Format(Range("H" & RowCount).Value, "YYYYMMDD")
outputLine = outputLine & MyNumber
MyDate = Format(Range("I" & RowCount).Value, "YYYYMMDD")
outputLine = outputLine & MyNumber
MyNumber = Format(Abs(Range("K" & RowCount).Value), _
"#000000000000000.00")
outputLine = outputLine & MyNumber
outputLine = outputLine & Range("L" & RowCount).Value & " "
If Range("Q" & RowCount) <> "" Then
outputLine = outputLine & Range("Q" & RowCount) & _
String(10 - Len(Range("Q" & RowCount)), " ")
End If
f.writeline outputLine
Next RowCount
f.Close
End Sub
 
Hi Rick,
Thanks for the prompt reply.
Sorry. Data starts from Col C as you have pointed out. It was my
mistake.

Since we import the data, other columns are blank. I need to only
export the columns (digit count shown) as follows:
Col C 1-9 5894 – should display as 5894(and 5 spaces) – total
9
Col F 10-19 Invoice number 12345678 – should display as
001234567 – total 10
Col H 20-27 Invoice date 21.02.2008 should display as 20080221 –
total 8
Col I 28-35 Payment date 21.02.2008 should display as 20080221
– total 8
Col K 36-50 Payment amount, leading zeros, 2 decimals, no
decimal sign – total 15
Col L 51-55 Currency code – EUR, should display EUR(and 2
spaces) – total 5
Col Q 56-66 BIC code bank, with or without branch – total 11

Thanks
 
You description has some wholes.  I did the best I could.  The code may
require minor changes.

Sub WriteText()
Const myFileName = "c:\temp\myfile.csv"
Const ForReading = 1, ForWriting = 2, _
   ForAppending = 3

fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName = False Then
    MsgBox ("Cannot Save File - exiting Sub")
    Exit Sub
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile _
   (fileSaveName, True)

LastRow = Range("C" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow

   MyNumber = Format(Range("C" & RowCount).Value, "####")
   outputLine = MyNumber & "     "
   MyNumber = Format(Range("F" & RowCount).Value, "  ########")
   outputLine = outputLine & MyNumber
   MyDate = Format(Range("H" & RowCount).Value, "YYYYMMDD")
   outputLine = outputLine & MyNumber
   MyDate = Format(Range("I" & RowCount).Value, "YYYYMMDD")
   outputLine = outputLine & MyNumber
   MyNumber = Format(Abs(Range("K" & RowCount).Value), _
      "#000000000000000.00")
   outputLine = outputLine & MyNumber
   outputLine = outputLine & Range("L" & RowCount).Value & "  "
   If Range("Q" & RowCount) <> "" Then
      outputLine = outputLine & Range("Q" & RowCount) & _
         String(10 - Len(Range("Q" & RowCount)), " ")
   End If
   f.writeline outputLine
Next RowCount
f.Close
End Sub

:










- Show quoted text -
Click to expand...

Hello Joel
When I run your macro it gives me "Run time error-13. Type Mismatch"

Please help

Thanks a lot
 
Okay, give the following (untested, but it should work fine) macro a try.
Just assign the appropriate worksheet reference in the With statement and
assign the filename (with its path) in the variable named FileNameAndPath
(replacing my example text, of course).

Sub WriteDataOut()
Dim X As Long
Dim FF As Long
Dim LastRow As Long
Dim Record As String
Dim TotalFile As String
Dim FileNameAndPath As String
FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt"
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
For X = 10 To LastRow
Record = Space$(66)
Mid$(Record, 1) = .Cells(X, "C").Value
Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000")
Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd")
Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd")
Mid$(Record, 36) = Format$(Abs(.Cells(X, "K").Value),
"000000000000000")
Mid$(Record, 51) = .Cells(X, "L").Value
Mid$(Record, 56) = .Cells(X, "Q").Value
TotalFile = TotalFile & vbCrLf & Record
Next
FF = FreeFile
Open FileNameAndPath For Output As #FF
Print #FF, Record
Close #FF
End With
End Sub

Rick


You description has some wholes. I did the best I could. The code may
require minor changes.

Sub WriteText()
Const myFileName = "c:\temp\myfile.csv"
Const ForReading = 1, ForWriting = 2, _
ForAppending = 3

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName = False Then
MsgBox ("Cannot Save File - exiting Sub")
Exit Sub
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile _
(fileSaveName, True)

LastRow = Range("C" & Rows.Count).End(xlUp).Row

For RowCount = 1 To LastRow

MyNumber = Format(Range("C" & RowCount).Value, "####")
outputLine = MyNumber & " "
MyNumber = Format(Range("F" & RowCount).Value, " ########")
outputLine = outputLine & MyNumber
MyDate = Format(Range("H" & RowCount).Value, "YYYYMMDD")
outputLine = outputLine & MyNumber
MyDate = Format(Range("I" & RowCount).Value, "YYYYMMDD")
outputLine = outputLine & MyNumber
MyNumber = Format(Abs(Range("K" & RowCount).Value), _
"#000000000000000.00")
outputLine = outputLine & MyNumber
outputLine = outputLine & Range("L" & RowCount).Value & " "
If Range("Q" & RowCount) <> "" Then
outputLine = outputLine & Range("Q" & RowCount) & _
String(10 - Len(Range("Q" & RowCount)), " ")
End If
f.writeline outputLine
Next RowCount
f.Close
End Sub

:










- Show quoted text -
Click to expand...

Hello Joel
When I run your macro it gives me "Run time error-13. Type Mismatch"

Please help

Thanks a lot
 
I see my newsreader wrapped one of the statements, so I am guessing yours
did too. Here is the code again, this time using a line continuation so that
you can simply copy/paste the code in...

Sub WriteDataOut()
Dim X As Long
Dim FF As Long
Dim LastRow As Long
Dim Record As String
Dim TotalFile As String
Dim FileNameAndPath As String
FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt"
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
For X = 10 To LastRow
Record = Space$(66)
Mid$(Record, 1) = .Cells(X, "C").Value
Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000")
Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd")
Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd")
Mid$(Record, 36) = Format$(Abs(.Cells(X, "K").Value) _
, "000000000000000")
Mid$(Record, 51) = .Cells(X, "L").Value
Mid$(Record, 56) = .Cells(X, "Q").Value
TotalFile = TotalFile & vbCrLf & Record
Next
FF = FreeFile
Open FileNameAndPath For Output As #FF
Print #FF, Record
Close #FF
End With
End Sub

Rick
 
I see my newsreader wrapped one of the statements, so I am guessing yours
did too. Here is the code again, this time using a line continuation so that
you can simply copy/paste the code in...

Sub WriteDataOut()
  Dim X As Long
  Dim FF As Long
  Dim LastRow As Long
  Dim Record As String
  Dim TotalFile As String
  Dim FileNameAndPath As String
  FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt"
  With Worksheets("Sheet1")
    LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
    For X = 10 To LastRow
      Record = Space$(66)
      Mid$(Record, 1) = .Cells(X, "C").Value
      Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000")
      Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd")
      Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd")
      Mid$(Record, 36) = Format$(Abs(.Cells(X, "K").Value) _
                                 , "000000000000000")
      Mid$(Record, 51) = .Cells(X, "L").Value
      Mid$(Record, 56) = .Cells(X, "Q").Value
      TotalFile = TotalFile & vbCrLf & Record
    Next
    FF = FreeFile
    Open FileNameAndPath For Output As #FF
    Print #FF, Record
    Close #FF
  End With
End Sub

Rick
message







- Show quoted text -
Click to expand...

Hi Rick,
Your macro exported only one record i.e the last record as follows.
5894 003000096215.03.2015.03.20000000000049184USD AABBCCDDXXX

Please note the following errors
1) Col H and I - Date is exported as 15.03.20 (please note that the
year is 2006) - it should be displayed as 20060315 in both case.
2) Col K (value is -49,183.80) the output is displayed as
000000000049184 - it should be displayed as 000000004318380 (i.e
leading zeros, two decimal places without any decimal signs)

Please help
 
Your macro exported only one record i.e the last record as follows.
5894 003000096215.03.2015.03.20000000000049184USD AABBCCDDXXX

Please note the following errors
1) Col H and I - Date is exported as 15.03.20 (please note that the
year is 2006) - it should be displayed as 20060315 in both case.
2) Col K (value is -49,183.80) the output is displayed as
000000000049184 - it should be displayed as 000000004318380 (i.e
leading zeros, two decimal places without any decimal signs)
Click to expand...

Argh! The single record print out was because I specified the wrong variable
in the Print statement. The other errors were because of my misreading what
you wrote. Give this revised code a try...

Sub WriteDataOut()
Dim X As Long
Dim FF As Long
Dim LastRow As Long
Dim Dte As String
Dim Record As String
Dim TotalFile As String
Dim FileNameAndPath As String
FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt"
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
For X = 10 To LastRow
Record = Space$(66)
Mid$(Record, 1) = .Cells(X, "C").Value
Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000")
Dte = .Cells(X, "H").Value
Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
Dte = .Cells(X, "H").Value
Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value), _
"000000000000000")
Mid$(Record, 51) = .Cells(X, "L").Value
Mid$(Record, 56) = .Cells(X, "Q").Value
TotalFile = TotalFile & vbCrLf & Record
Next
FF = FreeFile
Open FileNameAndPath For Output As #FF
Print #FF, TotalFile
Close #FF
End With
End Sub

Rick
 
Argh! The single record print out was because I specified the wrong variable
in the Print statement. The other errors were because of my misreading what
you wrote. Give this revised code a try...

Sub WriteDataOut()
  Dim X As Long
  Dim FF As Long
  Dim LastRow As Long
  Dim Dte As String
  Dim Record As String
  Dim TotalFile As String
  Dim FileNameAndPath As String
  FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt"
  With Worksheets("Sheet1")
    LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
    For X = 10 To LastRow
      Record = Space$(66)
      Mid$(Record, 1) = .Cells(X, "C").Value
      Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000")
      Dte = .Cells(X, "H").Value
      Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
      Dte = .Cells(X, "H").Value
      Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2)
      Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value),_
                                              "000000000000000")
      Mid$(Record, 51) = .Cells(X, "L").Value
      Mid$(Record, 56) = .Cells(X, "Q").Value
      TotalFile = TotalFile & vbCrLf & Record
    Next
    FF = FreeFile
    Open FileNameAndPath For Output As #FF
    Print #FF, TotalFile
    Close #FF
  End With
End Sub

Rick
Click to expand...

Works perfect. Thanks a lot for your help.

Rushna.
 
Works perfect. Thanks a lot for your help.

Rushna.- Hide quoted text -

- Show quoted text -
Click to expand...

Hello All,
The macro below provided by Mr. Rick Rothstein works perfect. It
export the worksheet to a text file. I wish to change it further to
suit to my needs.

Changes required:
The file should be saved in the given Folder Path using the Cell Value
(E5) with date and time stamped e.g if cell value (E5) in the excel
file is 1234 then the file should be saved as "1234 (date)
(time)".txt . If the value in E5 is 3456 then it should be saved as
"3456 (date) (time).txt"

At present it saves as filename.txt and it overwirtes the previous
version too. I need to have all the old files with date and time
stamped for future references using the Cell Value in E5.

Can someone help me please?

Thanks in advance

Rushna
 
Back
Top