Filling Out a Template with Consecutive Fields (1 Viewer)

[RogerCooper]

I need to fill out an Excel template with information on imports, see the attached file Template.txt. The data I need to fill it out from is here under Special Duty.txt or Special Duty Crosstab.txt

To show it visually.

The template
"Import #","Entry Line.","HTS2","HTS3","HTS4","HTS5","HTS6","HTS7","Rate 2","Rate 3","Rate 4","Rate 5","Rate 6","Rate 7"

The data

Entry Summary Number	Entry Summary Line Number	HTS Number - Full	Line Tariff Duty Amount	Line Tariff Goods Value Amount	Rate
32253291508	99030124	20
32253291508	99030125	10
32253291508	99030124	20
32253291508	99030125	10
32253291508	99030124	20
32253291508	99030125	10
32253291508	99030124	20
32253291508	99030125	10
32253291508	99038803	25
32253291508	99030125	10
32253291508	99030124	20
32253291508	99038803	25
32253291508	99030124	20
32253291508	99030125	10

Special Duty

[td]

1​

[/td]​

[td]

6494.4​

[/td][td]

32472​

[/td]​

[td]

1​

[/td]​

[td]

3247.2​

[/td][td]

32472​

[/td]​

[td]

2​

[/td]​

[td]

6278​

[/td][td]

31390​

[/td]​

[td]

2​

[/td]​

[td]

3139​

[/td][td]

31390​

[/td]​

[td]

3​

[/td]​

[td]

2973.6​

[/td][td]

14868​

[/td]​

[td]

3​

[/td]​

[td]

1486.8​

[/td][td]

14868​

[/td]​

[td]

4​

[/td]​

[td]

51.8​

[/td][td]

259​

[/td]​

[td]

4​

[/td]​

[td]

25.9​

[/td][td]

259​

[/td]​

[td]

4​

[/td]​

[td]

64.75​

[/td][td]

259​

[/td]​

[td]

5​

[/td]​

[td]

423.4​

[/td][td]

4234​

[/td]​

[td]

5​

[/td]​

[td]

846.8​

[/td][td]

4234​

[/td]​

[td]

5​

[/td]​

[td]

1058.5​

[/td][td]

4234​

[/td]​

[td]

6​

[/td]​

[td]

1408​

[/td][td]

7040​

[/td]​

[td]

6​

[/td]​

[td]

704​

[/td][td]

7040​

[/td]​

 

[RogerCooper]

or

Entry Summary Number	Entry Summary Line Number	99030110	99030124	99030125	99030260	99037801	99038508	99038803	99038815
32253291508		20	10
32253291508		20	10
32253291508		20	10
32253291508		20	10				25
32253291508		20	10				25
32253291508		20	10

Special Duty Crosstab

[td]

1​

[/td]​

[td]

2​

[/td]​

[td]

3​

[/td]​

[td]

4​

[/td]​

[td]

5​

[/td]​

[td]

6​

[/td]​

What I need to do is enter the data using the template in consecutive columns, ignoring the blanks. For example line 1 should be

32253291508,1,"99030124","99030125","","","","","20","10","","","",""

Line 4 should be

32253291508,1,"99030124","99030125","99038803","","","","20","10","25","","",""

I know that I can solve this by walking through the data with loops and updating one cell at a time. Is there is a more elegant solution to this problem?

 

[DHookom]

I'm confused since your data file does not contain the value 99030110.
This crosstab is as close as I can get/guess based on your requirements and Special Duty text file.

TRANSFORM Min([Special Duty].Rate) AS MinOfRate
SELECT [Special Duty].[Entry Summary Number], [Special Duty].[Entry Summary Line Number]
FROM [Special Duty]
GROUP BY [Special Duty].[Entry Summary Number], [Special Duty].[Entry Summary Line Number]
PIVOT [Special Duty].[HTS Number - Full];

 

[RogerCooper]

I'm confused since your data file does not contain the value 99030110.
This crosstab is as close as I can get/guess based on your requirements and Special Duty text file.

TRANSFORM Min([Special Duty].Rate) AS MinOfRate
SELECT [Special Duty].[Entry Summary Number], [Special Duty].[Entry Summary Line Number]
FROM [Special Duty]
GROUP BY [Special Duty].[Entry Summary Number], [Special Duty].[Entry Summary Line Number]
PIVOT [Special Duty].[HTS Number - Full];

The crosstab had been run against all the data, but I only excerpted a single entry.

 

[DHookom]

Can you please get your data and results to match exactly?

 

[tvanstiphout]

I have a few questions. Please answer all of them. There are good reasons for each of these.
1. Where does Special Duty.txt come from? Do you have any control over its format?
2. Special Duty Crosstab.txt: seems to be the results of a crosstab query over Special Duty data. How was that created? If it was created from Special Duty, then it may not be necessary to create the output file.
3. Special Duty Crosstab.txt: contains the column 99030110 but that is not in the data of Special Duty. It therefore seems there is additional data needed to create this crosstab. Where is that data?
4. Can Special Duty have more than one line with the same [Entry Summary Number] and [Entry Summary Line Number] combination? That is not so in the data you gave us, but can in happen in the more general case?
Thanks,

 

[RogerCooper]

I have a few questions. Please answer all of them. There are good reasons for each of these.
1. Where does Special Duty.txt come from? Do you have any control over its format?
2. Special Duty Crosstab.txt: seems to be the results of a crosstab query over Special Duty data. How was that created? If it was created from Special Duty, then it may not be necessary to create the output file.
3. Special Duty Crosstab.txt: contains the column 99030110 but that is not in the data of Special Duty. It therefore seems there is additional data needed to create this crosstab. Where is that data?
4. Can Special Duty have more than one line with the same [Entry Summary Number] and [Entry Summary Line Number] combination? That is not so in the data you gave us, but can in happen in the more general case?
Thanks,

1. Special duty is a subset of fields from a larger table. I can change the format.

2. Special duty crosstab is a crosstab query of Special Duty. It seemed easier to work with the crosstab. I still need this information to fill out the template. Note that I am not showing all the fields of the template, just the fields relating to special duties

3. The crosstab query was created off all the data, I am just displaying the records relating to one entry to show what the problem is.

4. Special duty is unique with regards to Entry Summary Number & Entry Summary Line Number. It is not unique with regards to the larger template but that is different (and easily solvable) issue.

 

[arnelgp]

share us the real excel template.

 

[RogerCooper]

share us the real excel template.

I converted this to a text file, as the website does not allow upload of Excel files.

 

[Gasman]

A csv file would have been better, surely?
Even if you had to add a .txt extension to upload it.

 

[RogerCooper]

A csv file would have been better, surely?
Even if you had to add a .txt extension to upload it.

I opened the txt file with Excel and all the fields appeared. But the details of the template are of little importance, I am just trying to find the best way of entering values into consecutive fields in general. The other fields have their values pulled directly from somewhere in my database.

 

[tvanstiphout]

1. Special duty is a subset of fields from a larger table. I can change the format.

2. Special duty crosstab is a crosstab query of Special Duty. It seemed easier to work with the crosstab. I still need this information to fill out the template. Note that I am not showing all the fields of the template, just the fields relating to special duties

3. The crosstab query was created off all the data, I am just displaying the records relating to one entry to show what the problem is.

4. Special duty is unique with regards to Entry Summary Number & Entry Summary Line Number. It is not unique with regards to the larger template but that is different (and easily solvable) issue.

Thanks for clarifying. Since you attached text files, I wasn't sure if these were sent to you by some other entity.
You could have attached a zipped copy of your database, stripped to the bare essentials.

I'm thinking your crosstab is a good attempt to get the data for HTS2 - 7, and now you're stuck with getting Rate 2 - 7 and you're asking if there is one query that can export it all. Is that correct? It seems weird since Rate is what is in the crosstab cells.

 

[RogerCooper]

I am trying to see if there is a better way than iterating through every cell of the crosstab and then updating the template for every non-zero value. Maybe there isn't.

 

[MarkK]

I am trying to see if there is a better way than iterating through every cell of the crosstab and then updating the template for every non-zero value. Maybe there isn't.

It doesn't seem like a problem to me to iterate over items in a list and deal with each one individually.

I have not studied your problem, but I would avoid a crosstab and deal directly with the raw data. A crosstab is slower to generate, requires two loops, and the fields are drawn from the data itself, so with a crosstab you are effectively reformatting the data in a way that makes it harder to parse programmatically.