.csv File import 2

[rhofing]

I need to import a .csv file into a table. The file however is not straight forward. It is divided into sections, each section has a 4-line header. The first line is a series of dash characters, the second is a long string that describes the contents of that section of the report and the third is another line of dash characters, the fourth are the actual column headers.

In between each section are the actual rows of data that I need. I dont need any of these header rows, only the actual data itself.

This pattern is repeated a number of times in the file. If I try to do a straight import I get very weird results, the columns don't match up, etc.

This needs to be an automated process (a DTS package) that runs once a week and processes the file (the name is always the same and the structure is always the same)

Can anyone suggest a way to get around this? I thought maybe using VB to go through the file and get rid of the rows but I am not a VB programmer though if required I think I could learn enough to accomplish the task reasonably well.

 

[fluteplr]

You could try to import the data into a a holding table then try to write a sql delete statement to delete all of the header rows. Then once the table is clean import that data to where ever it needs to go.

I frequently use this sort of pattern to insure that the data has been scrubbed and is valid before doing the actual import, you can use dts to automate these steps with appropriate failure messages if the data cannot be imported.

 

[rhofing]

I actually tried that but it turned out to be too error-prone. That is why I was thinking of trying to find some way to scrub the file before the import.

 

[fluteplr]

Then you will have to use some other language to scrub it. If you are not up to VB, you might be able to import the csv file into Excel or some other spreadsheet and write a macro to delete the offending rows.

 

[SQLSister]

Sometimes we get so hung up on the technical wizardry it takes to fix something like this, we forget the easiest solution of all. First, I would contact whoever you get this file from and ask them to give it to you in a better format for import. Maybe you'll be lucky and they will. This has worked for me 9 times out of ten. It's worth a try anyway!

 

[tlbroadbent]

ActiveX scripts in DTS are quite powerful. I prefer to use VBScript. With VBScript you can test each input row and skip it if it doesn't meet import criteria. Here is an example that skips a row containing "----" in the first column. Other criteria can be easily added.

'*********************************************
' Visual Basic Transformation Script
'*********************************************

' Copy each source column to the destination column
Function Main()

If DTSSource(1) = "----" Then
Main = DTSTransformStat_SkipRow

Else

DTSDestination("Col_1&quot = Trim(DTSSource(1))
DTSDestination("Col_2&quot = DTSSource(2)
DTSDestination("Col_3&quot = DTSSource(3)
DTSDestination("Col_4&quot = Trim(DTSSource(4))
DTSDestination("Col_5&quot = DTSSource(5)
Main = DTSTransformStat_OK

End If

End Function Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sguslan]

How do i give Terry broadbent a star ...
Very useful tips as always..
Thanx Terry

 

[SQLSister]

sguslan
at the bottom of each response is a link which says:

Mark this post as a helpful/expert post.

Just click on it and follow the directions from there.