Heres a challenge - Flate file creation

[dignityy]

Ok I need to create a flat file to upload to our peoplesoft gl database. I have all the data in one tab and I know the placement and spacing of each data point. What I need is some method to create the flat file. I thought a VBA macro could do the trick but I'm not sure how to proceed. The following is an example of the flat file structure:
BJS001Budget_FSBudget_FS 2300 10 3452 112.20

Thanks

 

[dignityy]

Ok I mean FLAT file not flate

 

[xlbo]

and what format is your data in currently ?

we ain't mind readers ya know ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bong]

First you want to open a file for output:
Open [red]<filename>[/red] For Output As #1

Then you want to print (or write) your text:
Print #1, [red]<your text>[/red]

<your text> can be taken from cells:
cells(x,y) & cells(x,z) & ...

Then you want to close the file:
Close #1


_________________
Bob Rashkin

 

[dignityy]

The data is currently in excel seperated in seperate cells.


Bong,
Can I somehow set the spacing to each data point, for example if the $ start in data point 135 how do I write a macro for that?

 

[SkipVought]

Hi,

Set the FONT to Courrier

Make sure each column has at least ONE cell that contains the MAX number of characters for that field.

Auto Fit the columns

SaveAs .prn (space delimited text)

Skip,

_[red][/red]

 

[Bong]

There are several way's depending on how precisely you want to control the position.

Firstly, you can use tabs (chr(9)) in your print string to position the contributions from different cells:
cells(x,y) & chr(9) & chr(9) & cells(x2,y2)

That doesn't always yield the best results, however, as the string before the tabs can vary in length, changing the behavior of the tab.

The easiest way I've found to control the output completely (i.e., to make columnar data in a text file) is to allocate a string with the total number of spaces I will use:
s1= string(totalNum, " ")

and then substitute in the values I get from the cells (in this case). Let's say you want to replace characters (spaces) in s1 with the string, s2, starting at position i and occupying however many spaces as the length of s2:
mid(s1,i,len(s2))=s2


_________________
Bob Rashkin

 

[xlbo]

apologies - I thought you meant you had normalised data that you wished to de-normalise.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dignityy]

Bong,

looks like I have some thinking to do... I'm a little to new right now to fully understand how to write the code you are talking about but learning more everyday.

 

[Bong]

Well, just think about what you want the flat file to look like. Each line the same length? What is that length? Where do you want the contents of which cell to appear in that line?

_________________
Bob Rashkin

 

[Bong]

By the way, separating the arguments of the print statement with commas basically does the same thing as inserting a tab. So if your spreadsheet data are reasonably "conformal", you can dispense with that nonsense I wrote earlier and use:
print #1, cells(x,a),cells(x,b),...,cells(x,n)

if that's what you want to do.

_________________
Bob Rashkin

 

[SkipVought]

The EXAMPLE posted originally...
[tt]
BJS001Budget_FSBudget_FS 2300 10 3452 112.20
[/tt]
is a SPACE DELIMITED format rather than a TAB or COMMA DELIMITED file format.

Simple, NO CODE solution. See my FIRST post in this thread.

Skip,

_[red][/red]

 

[dignityy]

Bong,
Here is the layout by position and length location(ie between each position are blank spaces)
1 code length = 1
2 company - length = 5
7 ledger - length = 9
26 account - length = 10
46 department - length = 10
173 year length - budget period
177 period length - 1
208 $ length - 20

That is just about all of it, what do you think? I would have asked our IT department to do it but just whole buch or red tape to get anything.

 

[Bong]

Then, if I understand correctly, your output string will be 228 characters long and look like:
caaaaa bbbbbbbbb cccccccccc dddddddddd...

etc.

Again, if it's important to control exactly where everything goes:

s1 = string(228," ")
mid(s1,1,1)=right(code,1)'in case "code" has more than 1 character
mid(s1,2,5)=company
            [red]and so on[/red]

_________________
Bob Rashkin