I am trying to develop a
xml
from from the
csv
file...
I have a problem here - after every 13 items in a line of
csv
file, there is a repetition of 5 items we are not sure how many times it will repeat. so how can we get the data of the repeated columns using linq..
This is my code.
String[] FileContent = File.ReadAllLines(@"C:\Users\user\Desktop\myexcel.csv");
String XMLNS = "";
XElement Inv = new XElement("XML",
from items in FileContent
let fields = items.Split(',')
select new XElement("Capture",
new XElement("apiLoginID", fields[0]),
new XElement("apiKey", fields[1]),
new XElement("CustomerID", fields[2]),
new XElement("OrderID", fields[3]),
new XElement("dateAuthorised", fields[4]),
new XElement("dateCaptured", fields[5]),
new XElement("Address",
new XElement("shipToAddress", fields[6]),
new XElement("shipToCity", fields[7]),
new XElement("shipToState", fields[8]),
new XElement("shipToCountry", fields[9]),
new XElement("shipToZip", fields[10]),
new XElement("shipToZipExt", fields[11])),
new XElement("CartID", fields[12]),
new XElement("CartItems",
new XElement("CartItem",
new XElement("Index", fields[13]),
new XElement("ItemID", fields[14]),
new XElement("TIC", fields[15]),
new XElement("Price", fields[16]),
new XElement("Quantity", fields[17])
)))
);
File.WriteAllText(@"C:\Users\user\Desktop\csv.xml", XMLNS + Inv.ToString());
}
this is my csv file columns->
LoginID,Key,CustomerID,OrderID,dateAuthorised,dateCaptured,shipToAddress,shipToCity,shipToState,shipToCountry,shipToZip,shipToZipExt,cartID,Index,ItemID,TIC,Price,Qty,Index,ItemID,TIC,Price,Qty,Index,ItemID,TIC,Price,Qty
Here the items
"Index,ItemID,TIC,Price,Qty"
repeated for 3 times but this is not 3 every time.
please help me in solving this issue.
how to make below part repeated. as we know that loop doesn't work here since the total code is a single statement...
new XElement("Index", fields[13]),
new XElement("ItemID", fields[14]),
new XElement("TIC", fields[15]),
new XElement("Price", fields[16]),
new XElement("Quantity", fields[17])
I want the out put like this..
<XML>
- <Capture>
<apiLoginID>mylogin</apiLoginID>
<apiKey>mykey</apiKey>
<CustomerID>15</CustomerID>
<OrderID>12345</OrderID>
<dateAuthorised>28-11-2012</dateAuthorised>
<dateCaptured>29-11-2012</dateCaptured>
- <Address>
<shipToAddress>3101 Bridges St</shipToAddress>
<shipToCity>Morehead City</shipToCity>
<shipToState>NC</shipToState>
<shipToCountry>USA</shipToCountry>
<shipToZip>28557</shipToZip>
<shipToZipExt />
</Address>
<CartID>25</CartID>
- <CartItems>
- <CartItem>
<Index>11</Index>
<ItemID>pst25</ItemID>
<TIC>6</TIC>
<Price>25.9</Price>
<Quantity>2</Quantity>
<Index>12</Index>
<ItemID>pst25</ItemID>
<TIC>6</TIC>
<Price>25.9</Price>
<Quantity>2</Quantity>
</CartItem>
</CartItems>
</Capture>
- <Capture>
<apiLoginID>mylogin</apiLoginID>
<apiKey>mykey</apiKey>
<CustomerID>15</CustomerID>
<OrderID>786143</OrderID>
<dateAuthorised>28-11-2012</dateAuthorised>
<dateCaptured>29-11-2012</dateCaptured>
- <Address>
<shipToAddress>3101 Bridges St</shipToAddress>
<shipToCity>Morehead City</shipToCity>
<shipToState>NC</shipToState>
<shipToCountry>USA</shipToCountry>
<shipToZip>28557</shipToZip>
<shipToZipExt />
</Address>
<CartID>25</CartID>
- <CartItems>
- <CartItem>
<Index>21</Index>
<ItemID>qwe45</ItemID>
<TIC>5</TIC>
<Price>13.43</Price>
<Quantity>1</Quantity>
<Index>22</Index>
<ItemID>pst25</ItemID>
<TIC>6</TIC>
<Price>25.9</Price>
<Quantity>2</Quantity>
<Index>23</Index>
<ItemID>pst25</ItemID>
<TIC>6</TIC>
<Price>25.9</Price>
<Quantity>2</Quantity>
</CartItem>
</CartItems>
</Capture>
</XML>
the below items repeat with in a single row of
csv
and we don't know how many times it will repeat.
<Index>11</Index>
<ItemID>pst25</ItemID>
<TIC>6</TIC>
<Price>25.9</Price>
<Quantity>2</Quantity>
Waiting for the answer...!
Thank you in Advance
Rohith.