CSV processing to XML


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting CSV processing to XML
# 1  
Data CSV processing to XML

Hi,
i am really fresh with shell scripting and programming,
i have an issue i am not able to solve to populate data on my server for Cisco IP phones.
I have CSV file within the following format:
;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;
;;CUSTOME NAME :;;CompanyX;;;;;;;;;PACKAGES;;;;;English
;;;;;;;;;;;;;standard;;;;;English
;;;;;;;;; ;;;;secretary;;;;;German
;;;;;;;;;;;;;;;;;;
;No;NAME;FAMILYNAME;TELEPHONE NUMBER;EXTENSION;E-MAIL;PREFERRED LANGUAGE;CUSTOMER PACKAGE;COUNTRY CODE;SIP PASSWORD;TIMEZONE
;1;test;customer;01235870;200;test@customer.org;English;secretary;English;1234;1234;ECT;;;;;;
;2;test;tester;01235871;201;test@customer.org;English;secretary;English;1234;1234;ECT;;;;;;
;3;user;customer;01235872;202;test@customer.org;English;secretary;English;1234;1234;ECT;;;;;;
;4;john;wayne;01235873;203;test@customer.org;English;secretary;English;1234;1234;ECT;;;;;;
;5;;;;;;;;;;;;;;;;;
;6;;;;;;;;;;;;;;;;;

and i would like to take a data from this CVS and file out XML format that Cisco IP phones can understand.
I care for CUSTOMER NAME, NAME, FAMILYNAME and TELEPHONE NUMBER other fields can be ignored.

<CiscoIPPhoneDirectory>
<Title>Directory title goes here</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>The name of the directory entry</Name>
<Telephone>The telephone number for the entry</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>

Example:
<CiscoIPPhoneDirectory>
<Title>CompanyX</Title>
<Prompt>People reachable via VoIP</Prompt>
<DirectoryEntry>
<Name>Test Customerr</Name>
<Telephone>012345870</Telephone>
</DirectoryEntry>
<CiscoIPPhoneDirectory>

Thank you all in advance
# 2  
if you have Python
Code:
#!/usr/bin/env python

import string
s=string.Template("""
<CiscoIPPhoneDirectory>
<Title>$company</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>$name</Name>
<Telephone>$telephone</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>
""")

f=0
for line in open("file"):
    line=line.strip()    
    if "CUSTOMER NAME" in line:
        COMP=line.split(";")[4]
    elif "FAMILYNAME" in line: f=1;continue
    if f :
        sline=line.split(";")
        if sline[2]:
            NAME =' '.join(sline[2:4] )
            TEL=sline[4]
            print s.substitute(company=COMP, name=NAME,telephone=TEL)

output
Code:
# ./test.py

<CiscoIPPhoneDirectory>
<Title>CompanyX</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>test customer</Name>
<Telephone>01235870</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>


<CiscoIPPhoneDirectory>
<Title>CompanyX</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>test tester</Name>
<Telephone>01235871</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>


<CiscoIPPhoneDirectory>
<Title>CompanyX</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>user customer</Name>
<Telephone>01235872</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>


<CiscoIPPhoneDirectory>
<Title>CompanyX</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>john wayne</Name>
<Telephone>01235873</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>

# 3  
what have you tried till now??
I can give you some hints please give a try..
in awk first check for those fields with delimitar as ";"
if they are present print them in your req format.
NOTE:if that CSV is very huge it will take bit time
# 4  
as i am new with shell scripting i have tried to write something using sed/awk but i am stocked
# 5  
Quote:
Originally Posted by angel2008
as i am new with shell scripting i have tried to write something using sed/awk but i am stocked
can you post the code which you tried may be we help you to correct that...
# 6  
thanks to ghostdog74
seems it works fine.
shell scripting, python and this kind of staff seems a very powerful

once again thx a lot
# 7  
i have tried this way, cause i find out sed/awk are good for text processing

Code:
# sed -e 's/;/ /g' file | awk 'NR==10 { print $3 } '
to get Company name
and to get entries
# sed -e 's/;/ /g' file | awk 'NR==21 { print $2, $3, $4 } '
so i get something like this:
test customer 01235870

but did not know what to do with this
sorry but i did not much to correct
i am not sure if shell can handle this

 -----Post Update-----

Hi,

can someone take a look into and help me out a bit. Python script posted by ghostdog74
works fine except puting compayn name into and when i start script i get this error

./test.py 
Traceback (most recent call last):
  File "./test.py", line 26, in ?
    print s.substitute(company=COMP, name=NAME,telephone=TEL)
NameError: name 'COMP' is not defined

but to quickly things working i did this:

and i got output like this:
CODE:
test.py 

#!/usr/bin/env python

import string
s=string.Template("""
<CiscoIPPhoneDirectory>
<Title>$company</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>$name</Name>
<Telephone>$telephone</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>
""")

f=0
for line in open("filee"):
    line=line.strip()
    if "CUSTOMER NAME" in line:
        COMP=line.split(";")[4]
    elif "FAMILYNAME" in line: f=1;continue
    if f :
        sline=line.split(";")
        if sline[2]:
            NAME =' '.join(sline[2:4] )
            TEL=sline[4]
            print s.substitute(company="CUSTOMER NAME", name=NAME,telephone=TEL)

and OUTPUT:
<CiscoIPPhoneDirectory>
<Title>CUSTOMER NAME</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>test customer</Name>
<Telephone>01235870</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>


<CiscoIPPhoneDirectory>
<Title>CUSTOMER NAME</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>test tester</Name>
<Telephone>01235871</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>

i am not able to get Customer Name variable into 

any help i would be greatful

 -----Post Update-----

Hi,

can someone take a look into and help me out a bit. Python script posted by ghostdog74
works fine except puting compayn name into and when i start script i get this error

./test.py 
Traceback (most recent call last):
  File "./test.py", line 26, in ?
    print s.substitute(company=COMP, name=NAME,telephone=TEL)
NameError: name 'COMP' is not defined

but to quickly things working i did this:

and i got output like this:
CODE:
test.py 

#!/usr/bin/env python

import string
s=string.Template("""
<CiscoIPPhoneDirectory>
<Title>$company</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>$name</Name>
<Telephone>$telephone</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>
""")

f=0
for line in open("filee"):
    line=line.strip()
    if "CUSTOMER NAME" in line:
        COMP=line.split(";")[4]
    elif "FAMILYNAME" in line: f=1;continue
    if f :
        sline=line.split(";")
        if sline[2]:
            NAME =' '.join(sline[2:4] )
            TEL=sline[4]
            print s.substitute(company="CUSTOMER NAME", name=NAME,telephone=TEL)

and OUTPUT:
<CiscoIPPhoneDirectory>
<Title>CUSTOMER NAME</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>test customer</Name>
<Telephone>01235870</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>


<CiscoIPPhoneDirectory>
<Title>CUSTOMER NAME</Title>
<Prompt>Prompt text goes here</Prompt>
<DirectoryEntry>
<Name>test tester</Name>
<Telephone>01235871</Telephone>
</DirectoryEntry>
</CiscoIPPhoneDirectory>

i am not able to get Customer Name variable into

any help i would be grateful
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #312
Difficulty: Easy
Linus Torvalds created Unix and Git.
True or False?

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

General Purpose XML Processing

I've been kicking this around for a while now, I might as well post it here. v0.0.9, now properly supporting self-closing tags. v0.0.8, an important quoting fix and a minor change which should handle special <? <!-- etc. tags without seizing up as often. Otherwise the code hasn't changed much.... (6 Replies)
Discussion started by: Corona688
6 Replies

2. Shell Programming and Scripting

Shell script for .Txt to .csv conversion with data processing

Hi experts, I want to convert a txt file having rows and columns (CNAI_DUMP_raw.txt) by comparing it with another text file (paramaters.txt) and generate a output in CSV which contains only 3rd column from CNAI_DUMP_raw.txt, and the columns mentioned in parameters.txt. FYI: There are two... (16 Replies)
Discussion started by: Gautam Banerjee
16 Replies

3. Shell Programming and Scripting

processing xml with awk

With the following input sample extracted from a xml file <rel ver="123"> <mod name="on"> <node env="ac" env="1"> <ins ip="10.192.0.1"/> <ins ip="10.192.0.2"/> ... (1 Reply)
Discussion started by: cabrao
1 Replies

4. Shell Programming and Scripting

Help with XML file processing

I need to get all session_ID 's for product="D-0002" from a XML file: Sample input: <session session_ID="6411206" create_date="2012-04-10-10.22.13.000000"> <marketing_info> <program_id>D4AWFU</program_id> <subchannel_id>abc</subchannel_id> </marketing_info> ... (1 Reply)
Discussion started by: karumudi7
1 Replies

5. Programming

help me with perl script xml processing

Hi everyone, I have Xml files in a folder, I need to extract some attribute values form xml files and store in a hash. My xml file look like this. <?xml version="1.0" encoding="UTF-8"?> <Servicelist xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"... (0 Replies)
Discussion started by: pavani reddy
0 Replies

6. Shell Programming and Scripting

Simple Script needed for Processing CSV file perl

I am new to perl, and need a script to pull a CSV file, strip out 2 leading columns, and 2 ending columns, and resave the file in a new location. The file is basic and has less than 10,000 records. Secondly, can I schedule perl scripts to run daily? Can someone provide the basic script to... (1 Reply)
Discussion started by: cobbjob
1 Replies

7. Shell Programming and Scripting

need help on xml processing

I am trying to divide a xml file(my.xml) like this: <?xml version="1.0" encoding="UTF-8"?> <Proto PName="hmmmmmmm"> <Menu id="A" ver="1"> <P> <P name="AA" Type="X"/> <P name="BB" Type="Y"/> <P name="CC" Type="Z"/> </P> ... (4 Replies)
Discussion started by: demoprog
4 Replies

8. Shell Programming and Scripting

Shell Script syntax for XML processing

Hi All, I am new to Shell scripting. I have a log file containing XML Messages.Each XML Message is accompanied with a timestamp.I need to count the the number of messages that get logged in a particular timeinterval.Is there any command/Syntax to achieve this. Any code/example is... (5 Replies)
Discussion started by: vignesh53
5 Replies

9. Programming

Processing a CSV file

Hi I'm reading a comma separated file record by record. After reading a record, I'm tokenizing it using strtok() function and moving the tokens into a structure. But suppose if a record is of the following format,i.e two commas appear one after another, then the strtok function skips the... (1 Reply)
Discussion started by: janemary.a
1 Replies

Featured Tech Videos