Xml to csv

 
Thread Tools Search this Thread
# 8  
Old 03-13-2017
Hi.

Use this version, s2:
Code:
#!/usr/bin/env bash

# @(#) s2       Demonstrate string extraction from XML file, xml2.

# Utility functions: print-as-echo, print-line-with-visual-space, debug.
# export PATH="/usr/local/bin:/usr/bin:/bin"
LC_ALL=C ; LANG=C ; export LC_ALL LANG
pe() { for _i;do printf "%s" "$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "$*"; }
em() { pe "$*" >&2 ; }
db() { ( printf " db, ";for _i;do printf "%s" "$_i";done;printf "\n" ) >&2 ; }
db() { : ; }
# C=$HOME/bin/context && [ -f $C ] && $C specimen xml2 grep awk tr dixf

FILE=${1-data1}
# E=expected-output.txt

pl " Sampled lines from data file $FILE:"
# specimen -n $FILE
head $FILE

# Look for a001, b203, and j151
pl " Results, warning message expected:"
xml2 < $FILE |
tee f1 |
grep -E '(a001|b203|j151)=' |
tee f2 |
awk -F/ '{print $NF}'|
tee f3 |
awk -F= '{print $2}'|
tee f4 |
tr '\n' '\t' ; echo ""

rm -f f?
exit 0

producing, by using your file name for data1 here:
Code:
$ ./s2 data1

Environment: LC_ALL = C, LANG = C
(Versions displayed with local utility "version")
OS, ker|rel, machine: Linux, 3.16.0-4-amd64, x86_64
Distribution        : Debian 8.7 (jessie) 
bash GNU bash 4.3.30
specimen (local) 1.17
xml2 - ( /usr/bin/xml2, 2012-04-16 )
grep (GNU grep) 2.20
awk GNU Awk 4.1.1, API: 1.1 (GNU MPFR 3.1.2-p3, GNU MP 6.0.0)
tr (GNU coreutils) 8.23
dixf (local) 1.42

-----
 Sampled lines from data file data1:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE ONIXmessage SYSTEM "http://www.editeur.org/onix/2.1/short/onix-international.dtd" >
<ONIXmessage release="2.1">
<header><m174>Houghton Mifflin</m174><m175>Catherine Toolan 978-465-7755</m175><m283>eloquence@firebrandtech.com</m283><m182>20170201</m182><m183>Title information from Houghton Mifflin</m183><m184>eng</m184><m185>01</m185><m186>USD</m186><m187>in</m187><m193>General Trade</m193></header>
  <product>
    <a001>9781328740472</a001>
    <a002>02</a002>
    <a197>HMH</a197>
    <productidentifier>
      <b221>02</b221>

-----
 Results, warning message expected:
error: Extra content at the end of the document
9781328740472   Peepers 7.99    10.99

Best wishes ... cheers, drl

PS:
It looks like brew,fink,port have some version of xml2 for an old system like:
Code:
OS, ker|rel, machine: Apple/BSD, Darwin 9.8.0, Power Macintosh
Distribution        : Mac OS X 10.5.8 (leopard, workstation)


Last edited by drl; 03-13-2017 at 06:37 PM.. Reason: Add stuff foir Mac
# 9  
Old 03-14-2017
drysdalk - It will be a weekly task, and there are about 2.5M lines per file, so Excel would not be practical, unfortunately. Thanks, though!

---------- Post updated 03-14-17 at 07:13 AM ---------- Previous update was 03-13-17 at 09:10 PM ----------

dlr - Thanks again. Yes, I still have that issue with xml2 (line 24). Also, would there be an issue with commenting out

Code:
pl() { pe;pe "-----" ;pe "$*"; }

to eliminate the line break?

Code:
-----
 Sampled lines from data file zzz:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE ONIXmessage SYSTEM "http://www.editeur.org/onix/2.1/short/onix-international.dtd" >
<ONIXmessage release="2.1">
<header><m174>Houghton Mifflin</m174><m175>Catherine Toolan 978-465-7755</m175><m283>eloquence@firebrandtech.com</m283><m182>20170201</m182><m183>Title information from Houghton Mifflin</m183><m184>eng</m184><m185>01</m185><m186>USD</m186><m187>in</m187><m193>General Trade</m193></header>
  <product>
    <a001>9781328740472</a001>
    <a002>02</a002>
    <a197>HMH</a197>
    <productidentifier>
      <b221>02</b221>

-----
 Results, warning message expected:
./z: line 24: xml2: command not found

# 10  
Old 03-14-2017
As you didn't specify any restrictions on neither input (e.g. pattern repetitions) nor output structure (field ordering, multiple lines), this easy approach might be of some interest:
Code:
awk '/a001|b203|j151/ {gsub (/ *<[^>]*> */, _); printf "%s\t", $0} END {printf RS}' file
9781328740472    Peepers    7.99    10.99

# 11  
Old 03-14-2017
RudiC - Yes, thanks so much, that is essentially what I need. The output line would be one of many with the same structure, so I think I need a
Code:
\n

somewhere, but couldn't quite get it to work.
# 12  
Old 03-14-2017
Where do you need the <line feed>? If after the j151, be aware that there can be several in one record. If you can be sure there's just one, try
Code:
awk '/a001|b203|j151/ {TRS=/j151/?RS:"\t"; gsub (/ *<[^>]*> */, _); printf "%s%s", $0, TRS}' file
9781328740472    Peepers    7.99

# 13  
Old 03-14-2017
Hi, palex.

You need to have xml2 in your system. As I wrote, it is available for installing in at least the version of MacOS that I have, albeit from 3rd parties.

If the solution from RudiC works for you, then use it -- it is simpler than xml2.

Best wishes ... cheers, drl
This User Gave Thanks to drl For This Post:
palex (03-14-2017)
# 14  
Old 03-14-2017
Very close... The following is the first four lines of output when I run the command on the entire data file:

Code:
9781328740472   Peepers 7.99
10.99
9780544503205   Curious George Fire Dog Rescue (CGTV reader)    3.99
5.99
9780544574786   Mistakes Were Made (but Not by Me)      15.95
22.50
9781328683786   Tools of Titans 28.00
40.00

I'm not sure where that extra field is coming from. Desired output:

Code:
9781328740472   Peepers 7.99
9780544503205   Curious George Fire Dog Rescue (CGTV reader)    3.99
9780544574786   Mistakes Were Made (but Not by Me)      15.95
9781328683786   Tools of Titans 28.00

Sorry if I was unclear! Thanks again!

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Need to read a csv and create xml - Perl Tuxidow Shell Programming and Scripting 1 04-24-2018 03:26 PM
Splitting CSV into variables then to XML file jeffs42885 Shell Programming and Scripting 15 07-27-2017 04:34 PM
XML to CSV pareshkp Shell Programming and Scripting 6 12-02-2014 01:24 PM
How to convert xml to csv ? rspwilliam Shell Programming and Scripting 1 11-16-2014 07:07 AM
XML to TXT or CSV A-V UNIX for Dummies Questions & Answers 7 05-16-2012 01:10 PM
Convert xml to csv dineshydv Shell Programming and Scripting 7 01-19-2012 04:14 AM
awk convert xml to csv research3 Shell Programming and Scripting 6 06-08-2011 12:40 PM
XML to CSV specific mohan705 Shell Programming and Scripting 4 09-19-2010 11:48 AM
urgent help to convert xml to xsl or csv umapearl UNIX for Dummies Questions & Answers 9 03-02-2010 02:34 AM
XML to csv transformation siba.s.nayak Shell Programming and Scripting 2 08-14-2009 06:15 AM
Convert XML to CSV format kumar04 Shell Programming and Scripting 2 07-31-2009 02:07 AM
CSV processing to XML angel2008 Shell Programming and Scripting 9 06-09-2009 06:04 AM
CSV to XML pjanakir Shell Programming and Scripting 15 05-12-2009 05:50 AM
Parse XML file into CSV with shell? Pcushing Shell Programming and Scripting 7 04-14-2009 11:27 PM
Help to convert XML to CSV rossingi_33 Shell Programming and Scripting 2 02-22-2008 07:18 AM