Poorly Formatted Table

[ferrisj]

I am working with data that is CSV, however it is truly bizarre in its format (

http://www.pama11.com/Downloads/MA11Data.TXT)

There are 4 fields. this data is data submitted from reports. the first field is the report ID. this is the same value for 1309 records, Field 2 is the data submitted, Field 3 is the field number, field 4 is the field ID, and field 5 is the Field caption.

each record is 1309 fields long, but because of the format of the data the table looks liek it has 3169926 records!!

 

[SkipVought]

Hi,

This is not a poorly formatted table.

If you had the specification for the data in the output file, you could write code for an input routine.

But short of having the output spec, you, me and everyone else would just be guessing.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[ferrisj]

if one had to make some guesses about the specifications where would you even begin? I am willing to make many stabs until i at least get close.

 

[SkipVought]

You've got to analyze the data that you have.

Please post a representative sample of the data from your file.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[ferrisj]

the link in the first post will show you the data and how it is laid out

 

[SkipVought]

Like I said, it's going to take some code to read each record and map it into the proper field.

Are you stating that the table has 1309 fields?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[Blorf]

I completely agree with SkipVought. This does not appear to be poorly designed, but also is not what I would call a normal table.

Example,
"00000000002207","222953",328,"C13G","Nursing facility allocation dollars"

Just looking at this suggests that 222953, is the value of "Nursing facility allocation dollars"

The rest of course is just a guess, since we have no specs. This is similar in appearance, at first glance, to an XML format, or other variable data structure format. Or even just data exported from some report, laid out in a neat kind of way.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[ferrisj]

you are correct


this is the number of the report (primary key) = 00000000002207
this is the actual value = 222953
this is the line number from the report = 328
This is a unique code for each field = C13G
the field label = Nursing facility allocation dollars

 

[SkipVought]

Help us out here! This is your baby. You know MORE about this than ANY of us.

One thing that you might want to do as an analysis tool, is to COPY the 1309 records for ONE key, and paste it into an Excel worksheet.

Parse the input rows, DELIMITING on the [commas]

You'll be able to see the 1309 values, FieldID's, captions.

This may help you to figgure out what to do with this data.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[ferrisj]

I inherited this data so the only thing I really know about it is what it looks like on paper.

I have moved this whole thing into an Access table at one point so I could look at it in a table format (it maxed out Excel.)

To my best guess i believe it should be understoof like this. The data needs to be tranposed with report number being the unique ID. The field names are actually the unique field code, because otherwise you would have duplicate field names in the table.

This is what the manual i got says:
"The text file option was created for use by software vendors whose MA-11 program
can be modified to create a text file from the tables used by the MA-11 program to
store cost report data. For the text file option, create a table with one field. The size
for the field should be 100 characters (the greatest maximum field size in the
standard spreadsheet template). Record 1 should contain "DATA V3.0." Following
the sequence of data in the Excel spreadsheet template or the numbered cost report
file in Appendix D, append each data item as a subsequent record in text format. For
example, record 2 would be sequence number 1, Facility ID. Record 3 would be
sequence number 2, Test (T or F), and so forth. If a data item is blank, append a
blank record as a placeholder. When completed, the table should contain only 1,310
records, the number of records to create a text file for one provider’s cost report.
Copy the table to an ASCII text file with the extension as .txt. Each record should be
terminated with a carriage return and line feed characters. Blank records should
contain only the carriage return and line feed.

 

[SkipVought]

The text file option was created for use by software vendors whose MA-11 program can be modified to create a text file from the tables used by the MA-11 program to store cost report data.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[BobRodes]

It would help a great deal if you were to tell us what you are trying to do!! "Working with" the file hardly explains what you're trying to accomplish.

That aside, this is clearly an attempt to put a whole set of related tables in one big flat file, suggesting that the program that spits out the data feed predates normalization theory by 10 or 15 years. Meaning written in about 1957. (Last one I saw like this was in a government data feed. Looks like this is another.)

Here's the layout, as you have already said.

1. ID number of entity.
2. Value of data for given field.
3. Field location in record, given as number (there are 1309 total fields in a record).
4. An attempt to codify all these fields in such a way as to show relationships in the data for one record.
5. A verbal description of the field, giving more clues as to what was intended.

Now, what it sounds like you're trying to do is put all the values with the same id number in a report format. To do this, you could just import the whole table into Access, as I see you have done. Then, query the table with one ID, and paste the result into Excel. Just like Skip says, although I wouldn't include the line numbers.

However, if your task is to take the data feed and put it into a relational database structure, you have more work cut out for you.

Feel free to explain what you're trying to accomplish in more detail.

HTH

Bob