comma-delimited file with header and trailer rows

Naomi Nosonovsky 8,051

Hi everybody,

I have a csv file with the addition of header and trailer rows. Can you please point me to the clearly written blog / tutorial as how to deal with that (or explain the steps for newbie)? I'd like to load data into 3 separate tables.

Thanks in advance.

3 answers

Tom Phillips 17,741 Reputation points

2022-06-14T18:28:39.34+00:00

Although there are ways to do this in SSIS, I find it much easier and simpler to just import all csv files into a "staging" table with all varchar fields, then use TSQL INSERT/UPDATE/MERGE in a proc to put the contents into the target table(s). This way you can skip the header/footer and process them separately in your TSQL commands.
Please sign in to rate this answer.

1 person found this answer helpful.
Naomi Nosonovsky 8,051 Reputation points

2022-06-14T19:10:37.427+00:00

Hi Tom,

How would you import this CSV file? E.g. this is what I have in the file right now:

2206130909
PaymentNumber,PaymentNumberDisplay,NPI,StatusDate,Amount,PayeeTin,PayeeName,CompanyID,StatusDescription,VCPAccount,ID,PayerName
0

I do have staging table created for the Detail rows and originally the file didn't have the header/trailer rows. I have package already built using the original file which is now not going to work due to these two extra rows. So I'm at loss as what to do. I saw some old MSDN threads, but I'd rather follow a nice blog/video instead like I just followed this one https://www.youtube.com/watch?v=OYq1nMftUZI because I was not sure where we specify dynamic SQL for Lookup transformation (now I know it's done in the Data Flow expressions).

Tom Phillips 17,741 Reputation points

2022-06-14T20:13:00.707+00:00

Like this

create table staging ( PaymentNumber varchar(100), PaymentNumberDisplay varchar(100), NPI varchar(100), StatusDate varchar(100), Amount varchar(100), PayeeTin varchar(100), PayeeName varchar(100), CompanyID varchar(100), StatusDescription varchar(100), VCPAccount varchar(100), ID varchar(100), PayerName varchar(100), rown INT IDENTITY(1,1) ) -- header SELECT TOP 1 PaymentNumber as Header FROM staging ORDER BY rown -- footer SELECT TOP 1 PaymentNumber as Footer FROM staging ORDER BY rown DESC SELECT * FROM staging WHERE rown > 1 AND rown < (SELECT MAX(rown) FROM staging)
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Yitzhak Khabinsky 26,296 Reputation points

2022-06-14T20:29:19.557+00:00

Hi @Anonymous ,

Alternatively, you can use SSIS Script Component as a Source.
You would need to write there some c# code to filter out not need lines from the input .csv file.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
ZoeHui-MSFT 39,671 Reputation points

2022-06-15T02:01:30.65+00:00

Hi @Anonymous ,

Like YitzhakKhabinsky mentioned you could use C# to do that.

A simple tutorial for your reference below.

Import-a-Flat-file-with-Header-and-Detail-Rows-in

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

comma-delimited file with header and trailer rows

3 answers

Your answer