Converting VaLues to Field Names

[czarjosh]

i was given a very big txt file, that when i put into access is a complete mess. Though there are about 300000 records, i have identified in one of the field 163 unique values. I assume these are field names. I was wondering how i can make a table otu of these 163 unique values, so that i some can begin making sense of this data

 

[czarjosh]

i have figured out how to convert the 167 unique values in teh 5th field into field names in a seperate table. Now comes the bigger problem.

My data is all lumped into one bit file. Basically the file 5 fields. They are report_ID, Field_number, Field_ID, Field_Label, Value.

How do I begin to split these apart? I believe there should be a multiple tables, but I am very cnfused on how to begin splitting all of this apart.

 

[SkipVought]

czarjosh,

It's probably gonna take some code.

First is analyzing the file. Is there a pattern of data? Are fields of data separated by delimiters or are they fixed length? Do all the records have similar formats for parsing? You may have to trial and error before you identify and code all the rules to parse the data.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[czarjosh]

it is comma deliminated, and there is a patterrn to it. I know the first and last field of every data set.

 

[SkipVought]

How does a record from your text file map to the table fields?

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[czarjosh]

here is an example of the data after it gets put into the table

Report_ID Field_Number Field_ID Field_Label Value
00000000002207 1 CERT1a Facility ID MA01013308
00000000002207 2 CERT1b Test (T or F) .F.

****skip 1523 rows ****

00000000002207 1308 MA583b Administrator's telephone number 8148644081
00000000002207 1309 MA583c Administrator's fax number 8148680514
00000000001402 1 CERT1a Facility ID MA01007632


It makes drastically more sense if you copy and paste it into notepad.

 

[SkipVought]

It would be better to see the data BEFORE it gets into the table.

What is the TRANSFORM???

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[dhookom]

Can you create records using a crosstab where the Report_ID is the Row Heading, Field_ID or Field_Label is the column heading and Value is the Value. If you have too many Column headings, you may have to query out a limited subset of these values.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[czarjosh]

I am posting the data the way it exists in the text file now. I am nout sure about the crosstab option, i have never done that before.

I do nto know what the TRANSFORM is.

Data as it exist in the text file:
"00000000002207","MA01013308",1,"CERT1a","Facility ID"
"00000000002207",".F.",2,"CERT1b","Test (T or F)"
"00000000002207","ABINGTON CREST NURSING AND REHAB CENTER",3,"CERT1c","Facility name"

****skip 1522 rows ****

"00000000002207","8148644081",1308,"MA583b","Administrator's telephone number"
"00000000002207","8148680514",1309,"MA583c","Administrator's fax number"
"00000000001402","MA01007632",1,"CERT1a","Facility ID"
"00000000001402",".F.",2,"CERT1b","Test (T or F)"

 

[SkipVought]

Transform, Mapping, Logic .. ie

how do you get from the input data format to the table data format?

It seems that Report_ID is an identifier for a common group of records. Should ALL these records go to one table? The source data must be analyzed to determine how this data is related.

I assume that "****skip 1522 rows ****" is not literally in the source data, but that you are showing a few records at the BEGINNING of a singel Report_ID and a couple at the end -- in the case of 00000000002207, there are as many as 1309 occurences (where does 1522 come from???) Furthermore, the data may be TRANSFORMED from a VERTICAL or SEQUENTIAL ROW format to a HORIZONTAL or SEQUENTIAL COLUMN format.

So this format
[tt]
Facility ID
Test (T or F)
Facility name
Administrator's telephone number
Administrator's fax number
[/tt]
might transform to this format...
[tt]
Facility ID|Test (T or F)|Facility name|Administrator's telephone number|Administrator's fax number
[/tt]
resulting in a SINGLE ROW in the table.

OR...

Some data may need to go into another table to NORMALIZE the structure.

Analysis, analysis, analysis!

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[czarjosh]

i would liek to transform it into that for sure.

The rease i have the skip portion is, that literally, that is how many rows og information are skipped before the data set starts over, there is just countless bit of inmformation to be looked. I am doing a regular improt from a text file in the access database, and loowing it to create a new table. I need it to make a table with the 1,527 fields, noting when the import get to the FACILITY ID row, that is the firs row in the new data set.

 

[SkipVought]

Analysis, analysis, analysis!

I can't give you much more definitive help without analyzing the source data.

YOU need to thoroughly analyze your data and understand how the mapping takes place and formulate (write down) how this transform works. Thi will be the basis for you or someone else coding a solution.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[dhookom]

Import the information into an Access table and then create a crosstab as I suggested. A crosstab transforms your data to "flatten" it out. You will have an issue if you have more than about 250 unique field IDs. If this is a concern, you will need to group field IDs into categories of less than 250.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[SkipVought]

Duane,

I would consider that as part of the data analysis phase.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[dhookom]

Skip,
I agree. The first thing I would do after importing the table would be to write queries to view and count the number of unique values of Report_ID, Field_Number, Field_ID, and Field_Label.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[czarjosh]

would it enlighten the probelm at all if you knew that I also have a copy of this file in vssscc file format.

ANother Option, I also have each 'data set' broken apart into individual excel spreadsheets.

 

[lespaul]

Data as it exist in the text file:

"00000000002207","MA01013308",1,"CERT1a","Facility ID"
"00000000002207",".F.",2,"CERT1b","Test (T or F)"
"00000000002207","ABINGTON CREST NURSING AND REHAB CENTER",3,"CERT1c","Facility name"

****skip 1522 rows ****

"00000000002207","8148644081",1308,"MA583b","Administrator's telephone number"
"00000000002207","8148680514",1309,"MA583c","Administrator's fax number"
"00000000001402","MA01007632",1,"CERT1a","Facility ID"
"00000000001402",".F.",2,"CERT1b","Test (T or F)"

The next thing you need to decide (and optionally share with us for critiquing) is how you want this data to look in your database. What are the tables that this data is going to? What are the fields in the table? Which piece of data goes to which table? That's the analysis that needs to be done.


Leslie

 

[czarjosh]

Unique Values:

Report_ID = 1713
Field_Number = 1309
Field_ID = 1309
Field_Label = 167
Value = 303279

One problem is that Field_ID and Field Label are related. for every Field_ID there is value (or it may be null). But many Field_Labels are duplicated. Thus meaning one field ID can have many Field_Label's

Here is a link to a sample file of one data set.

http://www.joshferris.com/sample.xls

 

[dhookom]

Thanks for posting a sample that we can see. I don't however see a Report_ID field in the sample.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[lespaul]

Is that a single table? Are those the field names in the table? Where in your sample data are the values you listed in the latest posting?

"00000000002207","MA01013308",1,"CERT1a","Facility ID"


What does 2207 mean? Where do you want 2207 to go? THe table and field name.

What does MA01013308 mean? Where do you want MA01013308 to go? The table and field name.

Etc...



Leslie