Create an "import-friendly" text-file from excel?

[Mikael Lindqvist]

Hi,

I have a basic excel file with a few columns with data.

Now I need to import this data to my financial system (iScala).

It seems iScala can only import textfile where each value starts at a
predefined position; hence it does not support "tab" or "space" delimited
values.

Does anyone know how to create a text file where all records are
"surrounded" by space and each record starts (at the same) predefined
position?

Example:

3 columns (A, B, C):

Name1 071001 452
Name123 071201 1200
Nam1 070905 51

Notice that "date" (column B) always start at position 9 and column C at
position 18... How to make this from a excel-file?

Kindly,
Mikael
Sweden

 

[JLatham]

What you are looking for is a way to export your Excel data as a 'Fixed Field
Length' text file. Since each use of such a file requires you to know the
required length (or starting point) of each field, they're not commonly used.

J.E. McGimpsey has some code posted on his site that you can adapt to do
this. Of course this assumes you are familiar with how to get the code into
your Excel workbook and modify it as required for your specific purpose.

Here's the link to his code:
http://www.mcgimpsey.com/excel/textfiles.html#fixedfield

Do you need help in working with the code to get it into your Excel workbook
or in changing it for your worksheet setup?

 

[Dave Peterson]

Saved from a previous post:

There's a limit of 240 characters per line when you save as .prn files. So if
your data wouldn't create a record that was longer than 240 characters, you can
save the file as .prn.

I like to use a fixed width font (courier new) and adjust the column widths
manually. But this can take a while to get it perfect. (Save it, check the
output in a text editor, back to excel, adjust, save, and recheck in that text
editor. Lather, rinse, and repeat!)

Alternatively, 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]

 

[Bernie Deitrick]

Below is a macro that will export the table starting in cell A1 to a file named C:\Excel\Scala
Export.prn.

You just need to change the widths array size and values to suit your needs.

HTH,
Bernie
MS Excel MVP


Sub ExportToPRN()

Dim fName As String
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim Widths(1 To 4) As Integer

Widths(1) = 8
Widths(2) = 9
Widths(3) = 10
Widths(4) = 10

fName = "C:\Excel\Scala Export.prn"

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile


With Range("A1").CurrentRegion 'Change A1 to upper left cell of range
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open fName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
WholeLine = WholeLine & Left(Cells(RowNdx, ColNdx).Text & _
Application.WorksheetFunction.Rept(" ", Widths(ColNdx)), Widths(ColNdx))
Next ColNdx
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub