[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

Data to XML Formatting

Hi,

I have about 500k records of data, here's a sample:

doubleclick.net      172823613      Services      Ad Networks and Servers      
atdmt.com      162598392      Services      Ad Networks and Servers      
trafficmp.com      148660547      Services      Ad Networks and Servers      
specificmedia.com      144601453      Services      Ad Networks and Servers      
tribalfusion.com      135750347      Services      Ad Networks and Servers      
yieldmanager.com      135224700      Services      Ad Networks and Servers      
realmedia.com      105436510      Services      Ad Networks and Servers      
interclick.com      102619007      Services      Ad Networks and Servers      

I'm interested in outputting the following, where <tr id="number"> will increase for each set of data.

<tr id="1">
<td>9/1/2010</td>
<td>doubleclick.net</td>
<td>172823280</td>
<td>Services</td>
<td>Ad Networks and Servers</td>
</tr>
<tr id="2">
<td>9/1/2010</td>
<td>atdmt.com</td>
<td>162598059</td>
<td>Services</td>
<td>Ad Networks and Servers</td>
</tr>
<tr id="3">
<td>9/1/2010</td>
<td>trafficmp.com</td>
<td>148660214</td>
<td>Services</td>
<td>Ad Networks and Servers</td>
</tr>
<tr id="4">
<td>9/1/2010</td>
<td>specificmedia.com</td>
<td>144601120</td>
<td>Services</td>
<td>Ad Networks and Servers</td>
</tr>
<tr id="5">
<td>9/1/2010</td>
<td>yieldmanager.com</td>
<td>135224367</td>
<td>Services</td>
<td>Ad Networks and Servers</td>
</tr>
<tr id="6">
<td>9/1/2010</td>
<td>realmedia.com</td>
<td>105436177</td>
<td>Services</td>
<td>Ad Networks and Servers</td>
</tr>
<tr id="7">
<td>9/1/2010</td>
<td>interclick.com</td>
<td>102618674</td>
<td>Services</td>
<td>Ad Networks and Servers</td>
</tr>

Thank you
0
faithless1
Asked:
faithless1
  • 3
  • 2
1 Solution
 
MikeOM_DBACommented:
Assuming the source file delimiter is "tab", try this:


crdt=`date +%m\/%d\/%y`
awk -F"\t" -v dt=$crdt '{
print "<tr id=\""++t"\">"
print "<td>"dt"</td>"
print "<td>"$1"</td>"
print "<td>"$2"</td>"
print "<td>"$3"</td>"
print "<td>"$4"</td>"
print "</tr>"
}' MyFile.txt

Open in new window

0
 
faithless1Author Commented:
Hi,

Just ran this on the command line and got the following error: line 1: syntax error at or near print


cat test.txt | crdt=`date +%m\/%d\/%y`| awk -F"\t" -v dt=$crdt '{print "<tr id=\""++t"\">"print "<td>"dt"</td>"print "<td>"$1"</td>"print "<td>"$2"</td>"print "<td>"$3"</td>"print "<td>"$4"</td>"print "</tr>"}'
awk: line 1: syntax error at or near print
0
 
faithless1Author Commented:
Hi,

Just ran this on the command line and got the following error: line 1: syntax error at or near print


cat test.txt | crdt=`date +%m\/%d\/%y`| awk -F"\t" -v dt=$crdt '{print "<tr id=\""++t"\">"print "<td>"dt"</td>"print "<td>"$1"</td>"print "<td>"$2"</td>"print "<td>"$3"</td>"print "<td>"$4"</td>"print "</tr>"}'
awk: line 1: syntax error at or near print
0
 
MikeOM_DBACommented:
You are missing semi-colons between the awk statements, try executing in a script..

0
 
MikeOM_DBACommented:

#
# Try this code in a script:
#
crdt=`date +%m\/%d\/%y`; 
awk -F"\t" -v dt=$crdt '{
print "<tr id=\""++t"\">";
print "<td>"dt"</td>";
for (i=1;i<=NF;++i) print "<td>"$i"</td>";
print "</tr>";
}' test.txt
#
# Or this in one line:
#
crdt=`date +%m\/%d\/%y`; awk -F"\t" -v dt=$crdt '{print "<tr id=\""++t"\">";print "<td>"dt"</td>";for (i=1;i<=NF;++i) print "<td>"$i"</td>";print "</tr>";}' test.txt
#

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now