Tables

[twosides]

I have an access table that contains an exported file from another application, its electrical consumption data actually.
My problem is that the export function on the metering equipment will export it in only one way.
It exports it in a vertical fashion down to a set number of rows but then for some reason starts a new column near the top of the page and repeats itself like thus depending upon how many readings are stored.
The table is like thus:

Field1 = contains the date
Field2 = time (set at every 10secs on the meter at present)
Field3 = Current 1
Field4 = Current 2
Field5 = Current 3
Field6 = Voltage
Then after so many readings it jumps back to the top of the table but increments the column so that the following is now occurring but leaves 1 column blank as a separator almost:

Field8 = date
Field9 = time
Field10 = Cur 1
Field11 = cur 2
And so on until the same line is reached and then does the same again.
All I want to do is combine the “dates” in one column, the “times” in another one, the Current 1’s in another and so on so that all I have is a Date, a time, Current 1, Current 2, etc for each data series. But obviously keep the right date and time for each sample.
Any help would be appreciated.

Cheers

 

[KenHigg]

Can you post a sample db with some of this data in it?

 

[neileg]

Looks like an Oracle Forms output. A real pain. Can't you connect to the underlying database?

 

[twosides]

Here is the sample of the data. I have obviously deleted many of the rows below to reduce the file size. The rows went upto around 8100 or so !
Cheers

 

[KenHigg]

I suspect that if this is all you get to work with, you're going to have to manually import it each time. Unless it is in an extremely consistant format each time you get it.

Failing that, the only other thing I can thing of is to see if you can get to it through odbc or maybe they have other export options from the master db system...

 

[twosides]

Hi Ken,
Thanks for the replys.
Yep its consistant and I dont mind importing it as this is what I have done.
Well just a direct import into Access. But the thing is once its there, can I use Access to manipulate or rehandle it in to columns?
Cheers

 

[KenHigg]

So you always have the exact same number of rows and cols in the xls?

 

[twosides]

Sorry no the table grows verticaly and then branches out to the right via column expansion.
Any thoughts
ta

 

[mresann]

Some basic notes:

1. Expanding the columns horizontally is a **really bad idea**. You wuold have to create a loop that checks the relative references under several different circumstances.

2. For a database, you need to have as few columns as possible. Increasing the number of rows, if properly indexed, is much more preferable.

3. If you have to deal with using Excel as a database, then consider creating a new worksheet for each date, if you don't want to create a new filename (workboook).

4. If you need two separate columns separating the data and the time, then use "DateValue(MyDate)" for the date format, and "TimeValue(MyDate)" for the time format. Notice the date field is the same in both.

Speaking of date values, your date fields are actually WRONG. I looked at the formula for your first date, 01-Jul-05. HOWEVER, your date formula is as follows:
=DATE(105,7,1)
That means your date is 1 July 105 (NOT 2005)

 

[Pat Hartman]

Rather than having the other application export the data in an Excel format, can you link to its tables directly or at least have the application export a .csv file which has no vertical limit.

 

[twosides]

Sadly not, the export is as it comes from the very basic logging software.
It seems such a silly thing to try and overcome, however famous last words I feel.
ta