Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create an "import-friendly" text-file from excel?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create an "import-friendly" text-file from excel?
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/textf...tml#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? "Mikael Lindqvist" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create an "import-friendly" text-file from excel?
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/groups?threadm=015...0a% 40phx.gbl Mikael Lindqvist wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Create an "import-friendly" text-file from excel?
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 "Mikael Lindqvist" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to create an ".iif" file from Excel for QuickBooks Pro 2001. | Excel Worksheet Functions | |||
Import text doc to XL. Need items seperated by " " in new column | Excel Discussion (Misc queries) | |||
create links to check boxes marked "good" fair"and "bad" | Excel Worksheet Functions | |||
Utility to "clean up" or "defrag" large Excel file | Excel Discussion (Misc queries) | |||
.csv drops "0" when saved, even if file was set as "text". | Excel Discussion (Misc queries) |