x

CSV to XML conversion

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:

  1. No Column Names
  2. Extra Footer

I have multipart question:

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

asked Sep 07, 2011 at 03:08 PM in Default

Raj More gravatar image

Raj More
1.7k 79 82 84

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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
more ▼

answered Sep 08, 2011 at 04:54 AM

CGSJohnson gravatar image

CGSJohnson
11

(comments are locked)
10|1200 characters needed characters left

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

  1. Prior data flow run the Script Task which will handle the file and remove the unnecessary header and/or footer
  2. 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.
more ▼

answered Sep 08, 2011 at 12:27 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x927
x581
x146
x35
x3

asked: Sep 07, 2011 at 03:08 PM

Seen: 1444 times

Last Updated: Sep 07, 2011 at 03:08 PM