CSV to XML conversion

Question

I have twenty four (24) incoming CSV files in the following generalized format:

HeaderRow FileName DateTime RecordCount
DataRow   Column01 Column02 Column03 Column04 Column05
DataRow   Column01 Column02 Column03 Column04 Column05
DataRow   Column01 Column02 Column03 Column04 Column05
DataRow   Column01 Column02 Column03 Column04 Column05
DataRow   Column01 Column02 Column03 Column04 Column05
FooterRow FileName DateTime RecordCount

I want to import into a table using SSIS.

My issues with this file are:

No Column Names
Extra Footer

I have multipart question:

How can I change this to an XML structure? (Third party products welcome)
How do I ignore the last row?

Answer 1

In general you can go by two ways how to process this kind of file.

Prior data flow run the Script Task which will handle the file and remove the unnecessary header and/or footer
Use script component in the data flow as a source. There you can ignore the row if you recognize it is header or footer and divide the rows to appropriate columns.

Related to the how to create an XML, you can easily handle this gain in Script Component as destination. You can easily build the XML in the ProcessInputRow method of the Script Component using eg. LINQ to XML (XDocument, XElement). The implementation is very easy and you do not need any third party component for this.

Answer 2

0

Do you want the files changed to XML before input or you just want them converted to XML? If it's the latter, then just proceed with the import via SSIS and then look into using the FOR XML clause in selecting items from the table to which you import.

As for ignoring the last row, in the table into which you insert, add an identity column and select every row except the last one (the maximum value of the identity column).

I hope that this helps.

Chris

answered Sep 08 '11 at 04:54 AM

CGSJohnson
11

(comments are locked)

CSV to XML conversion

Follow this question

Related Questions