Matrix Import Help

[HansD]

Hi, I hope that somebody can help me.

Every month we receive a CSV file file produced by an outside company, which
consists of approx. 52 columns and about 100 rows of data.

The problem we have is that neither the columns, nor the rows are ever in
the same order as our file. All of the information needs to be extracted from
this file and copied into our own file in a set format.

Is the best way forward to use a macro or write a VBA script to re-arrange
both the columns and rows

What is the best way of doing this.

thanks -

 

[paul.robinson]

Hi
Use a macro and write VBA script are the same thing.
If your rows and columns have headers you will be able to re-arrange
them to a set format. How does the info come in exactly, and how do
you want it to look (exactly).
regards
Paul

 

[HansD]

The file comes in with the header row listing 52 various "Reference Items",
which we need to put in order, these columns contain both numerics and text
items, but never mixed, a numeric column will only ever have numeric data in
it.

The rows, of which there are normally about 100 off again contain both
numerical and text data.

The received data looks like -
col16 col35 col3 col9 col50 col4 col27 col15 col6 col42
etc
row7
row19
row65
row1
row82
row17


Each of the columns and rows need to be put into a set order.
col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11
etc.
row1
row2
row3
row4
row5
etc.

I hope that this primative example explains things a bit better

Thans

 

[Rick Rothstein \(MVP - VB\)]

I'm sure those column and row headers are simplified examples. Can you tell
us, based on the column and row headers, how you know the order they are
supposed to go in? For example, is there a numerical index of **any** kind
that can be used in a loop to determine the final column and row ordering?

Rick

 

[HansD]

Hi Rick,

The row and column order that we required are based upon the order required
for up loading to our mainframe, which was hard coded in a few years ago. The
order that it comes in to us is based on the monthly useage for each area.

 

[Rick Rothstein \(MVP - VB\)]

Okay, so I am completely sure what you are saying, is this summary correct?
There are always the same number of columns and rows in the data file and
the are always in the same order. Hence, the "header row listing 52 various
'Reference Items'" you mentioned in your previous message is immaterial and
simply needs to be skipped over, correct? The rows do not have headers; they
are simply in a set order that you need to re-order. You know, beforehand,
the proper re-ordering of both the rows and columns that are required. For
example, using your posted example, the 1st column of incoming data will
always be placed in the 16th column on the resulting worksheet, the 2nd
column of incoming data will always be placed in the 35th column on the
resulting worksheet, etc. Similarly, the 1st row of incoming data will end
up on the 7th row of the resulting worksheet, etc. Is that an accurate
summary?

Oh, by the way, what kind of file are you getting from your mainframe... a
plain text file? If so, what is its line separator character... a simple
Line Feed (like a UNIX system would produce) or a real Windows line
separator, namely, a Carriage Return followed by a Line Feed?

Rick

 

[HansD]

Hi Rick,

At the moment, the data that comes in has 52 columns and 100 rows, the order
they come in is determined by sales, performance and KPI's for each area of
the business, so this will change each and every month.

What we require is a way to take all of this data and put it in the correct
order for uploading to our mainframe. At the moment everything is done
manually, the rows are given a value and then sorted, then each of the
columns are again manually cut and pasted into the correct order. This
information is then used to produce various monthly financials and stats in
Excel, as well as being uploaded to the main frame.

Is this making things any clearer

Hans

 

[Rick Rothstein \(MVP - VB\)]

Yes, it makes it clearer, but I don't know kind of code you are expecting.
If the ordering can change monthly, how is the code supposed to be able to
figure out how to re-order it? It needs something to lock onto. Let's go
back to the headers... are they fixed? That is, are the column headers in
the first row the same text as the column headers in your worksheet? If not,
how do you figure out how to manually number them? And what about the
rows... are there headers in the first column for them? If not, how do you
figure out how to re-order them. What you need to remember is that **no
one** here knows anything about your needs, so you have to tell us, in as
much detail as possible, what you have, what you want to do with it and how
you presently go about doing what you do. We need the details... it is
impossible to write code for a procedure unless you can provide details on
how you would do it manually.

Rick

 

[HansD]

Hi Rick

Would it be easier if I sent you a copy of what we receive and a copy of how
we need it to look

Hans

 

[Rick Rothstein \(MVP - VB\)]

Yes, you can do that, although posting in on a webpage for downloading would
be better as that would allow others to possibly offer a solution (there is
no guarantee that I will be able to). However, you will still need to
provide a description of how you decide how to re-order the columns and
rows.

Rick

 

[Jon Peltier]

If the headers are recognizable, you can load the incoming data into an
array, find which column of the array begins with a particular header, put
this data into the appropriate column of an output array, and repeat for all
columns, Then dump the output array into a new worksheet as your file to
send to the mainframe.

- Jon