federalprogrammer
New member
- Joined
- Nov 4, 2005
- Messages
- 3
- Programming Experience
- 5-10
Hi there,
The tool should be able to map Textfiles (of up to 1GB) to an MS-Access Table.
Here are the rules for doing so:
The tool should "SUM-UP the values of each row in the text file by the first 5 fields"
To do this, the tool would read the entire file and commit it to the database. Then it would query the table using an aggragate SUM:
"Select Sum(col7), sum(col8)
From table
Group by Col1, col2, col3, col4, col5"
Then the result of this query would replact the original data in the table. This is a very timely process, however (the delay occurs on DataAdaptor.Update since there are up to 1GB of data that has to be written to the DB and Queried, ad written back to the DB again!!)
So, as a result, I want to do the Aggragate sum in memory before writing the parsed-data to the Database. I use the Column Expression:
SUM(CHILD.ColumnName)
So,
1) Parse all the data into dtCHILD
2) Parse 5 fields of all data into dtPARENT
3) Create a relation on 5 fields of dtPARENT and dtCHILD
4) Add the relation to a Dataset
5) Add the rest of the columns to dtPARENT, by applying the above column expression
6) Commit dtParent to DB
The problem is w/ my step4: Adding the relation to the dataset provides the following error message:
"This constraint cannot be enabled as not all values have corresponding parent values."
your help is much apreciated
The tool should be able to map Textfiles (of up to 1GB) to an MS-Access Table.
Here are the rules for doing so:
- Field1 to Field5 of the TEXT-FILE are mapped to Column1 to Column5 of the DB-TABLE
- Field6 to Field16 of the TEXT-FILE are translated into an integer (through a black-box) and the integer is mapped to column6 of the DB-TABLE
- Field17 to Field47 of the TEXT-FILE are translated into Column7 to column37 of the DB-TABLE
- Column0 of the DB-TABLE is an auto-Number and the only PK-Field
- THE MOST IMPORTANT RULE: Column0 which is the PK, should contain unique values for columns1 through 5 (In fact, columns1 through 5 are the actual primary key set)
The tool should "SUM-UP the values of each row in the text file by the first 5 fields"
To do this, the tool would read the entire file and commit it to the database. Then it would query the table using an aggragate SUM:
"Select Sum(col7), sum(col8)
From table
Group by Col1, col2, col3, col4, col5"
Then the result of this query would replact the original data in the table. This is a very timely process, however (the delay occurs on DataAdaptor.Update since there are up to 1GB of data that has to be written to the DB and Queried, ad written back to the DB again!!)
So, as a result, I want to do the Aggragate sum in memory before writing the parsed-data to the Database. I use the Column Expression:
SUM(CHILD.ColumnName)
So,
1) Parse all the data into dtCHILD
2) Parse 5 fields of all data into dtPARENT
3) Create a relation on 5 fields of dtPARENT and dtCHILD
4) Add the relation to a Dataset
5) Add the rest of the columns to dtPARENT, by applying the above column expression
6) Commit dtParent to DB
The problem is w/ my step4: Adding the relation to the dataset provides the following error message:
"This constraint cannot be enabled as not all values have corresponding parent values."
your help is much apreciated
