How to read data from file (not well formatted) with ssis

Question

How to read data from file (not well formatted) with ssis

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 1

asita (5/18/2010)
...so now i have the whole cleaned data in a string variable how can i create a file with the string variable data
so that i can use that file to input then ssis will read it into the sql table

As I said previously, if I'm doing so much of code manipulation I won't bother to write the text back to a text file and read through a file connection. Anways, if you want to write to the file the code would be similar to:

Dim MyWriter As System.IO.StreamWriter = System.IO.File.CreateText(Filename)

MyWriter.Write(Text)

MyWriter.Close()

asita (5/18/2010)
mean while, i will also try it with oposite to reading file

If I understand you correct then all you need is to create Output columns in the script data flow task and populate then after the second split that yields you values for each of your destination columns.

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

asita (5/18/2010)
Hello Rjv,
thanks alot for your code, it gave me good idea , and i substituted in my code it is working fine
so now i have the whole cleaned data in a string variable how can i create a file with the string variable data
so that i can use that file to input then ssis will read it into the sql table
mean while, i will also try it with oposite to reading file
for alll thanks for your valuable suggestions , i really greatful to u alllll
Thanks & Regards
asita

How big is the file you have to process?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

asita SSCarpal Tunnel Points: 4144 More actions · Answer 3

Hello Cozy,

its about 200 kb (around 7000 records in it)

thanks & Regards

asita

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

I am using this to write from variable to a file

Using writer As StreamWriter = New StreamWriter("C:\myfile.txt")

writer.Write(textFileOutput)

End Using

here my doubt is does it has any limits on it (like storage of a string variable)

if in future if it has like some 15000 rows or so does is capable of store that much????

thanks & regards

asita

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

asita (5/19/2010)
Hello Cozy,
its about 200 kb (around 7000 records in it)
thanks & Regards
asita

Okay. This is not very much. You can do it one line of code:

Call File.WriteAllText("<your input file path>", File.ReadAllText("<your input file path>").Replace(vbCrLf, String.Empty))

Then use the regular flat file source.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

asita SSCarpal Tunnel Points: 4144 More actions · Answer 6

hello please..,

i have a 4 lines of data (as below) in my source file

"DIAL" "MENENT" "NOBLENT NAME" "NETRK" "PDUCT" "PDUCT" "MTH" "EBC" "EBC NAME" "INV#" "INV COST" "INVUNTS" "TOTAL" "ACC" "PAY GOOD" "MTHUNIT" ;

" " "CODE" " " " " " " " " " " " " " " " " " " " " "PAYABLE" "UNITS" " " " " ;

"DMA" "CY FOUN" "AMERICANUN" "AEN" "ALF" "ALF" "FEB10" "4" "DRTV" " " ".00" " " "0.00" "12.0" "0.00" "12.0" ;

"DMA" "CY FOUN" "AMERICANUN" "APL" "ALF" "ALF" "FEB10" "4" "DRTV" " " ".00" " " "0.00" "10.0" "0.00" "10.0" ;

i am reading eachline and doing the below task

line = Trim(line.Replace(vbCr & vbLf, "").Replace(""" """, """,""").Replace(";", ""))

now after complete all lines in my source file then the value will be as beloe

"DIAL","MENENT","NOBLENT NAME","NETRK","PDUCT","PDUCT","MTH","EBC","EBC NAME","INV#","INV COST","INVUNTS","TOTAL","ACC","PAY GOOD","MTHUNIT" ;

"," "CODE"," "," "," "," "," "," "," "," "," "," ","PAYABLE","UNITS"," "," " ;

"DMA","CY FOUN","AMERICANUN","AEN","ALF","ALF","FEB10","4","DRTV"," ",".00"," ","0.00","12.0","0.00","12.0" ;

"DMA","CY FOUN","AMERICANUN","APL","ALF","ALF","FEB10","4","DRTV"," ",".00"," ","0.00","10.0","0.00","10.0" ;

i got what i want for all rows (except in second line when the first column value is null then it is having trouble)

how can i rectify that

please help me

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 7

asita (5/19/2010)
"," "CODE"," "," "," "," "," "," "," "," "," "," ","PAYABLE","UNITS"," "," " ;
"DMA","CY FOUN","AMERICANUN","AEN","ALF","ALF","FEB10","4","DRTV"," ",".00"," ","0.00","12.0","0.00","12.0" ;
"DMA","CY FOUN","AMERICANUN","APL","ALF","ALF","FEB10","4","DRTV"," ",".00"," ","0.00","10.0","0.00","10.0" ;
i got what i want for all rows (except in second line when the first column value is null then it is having trouble)
how can i rectify that
please help me

Are you sure that's a valid value in the file? To me it looks like it should be ignored.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 8

yes in my case, i need to consider this

please help me

Regards

asita

asita SSCarpal Tunnel Points: 4144 More actions · Answer 9

How to replace a string after first occurance

i.e

string source=" " "asas" "asde"

i would like to change " " to "," after first occurnace

so my expecting output of source variable is " ", "asas", "asde"

and not as " ,", "asas", "asde"

Regards

asita

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 10

Maybe there is a cleaner solution than I am proposing. From what I have you can base yours on below code to get rid of the nasty first empty value (the code is C#, use developerfusion to convert to vb.net)

string testString = @"""ABC"" ""CY FOUN"" ""CY FOUN"" ""AEN"" ""ALF"" ""XLF"" ""FEB10"" ""4"" ""TQV"" "" "" "".00"" "" "" ""0.00"" ""12.0"" ""0.00"" ""12.0"" ;

""ABC"" ""EGACY FOUN"" ""AMERIFOUN"" ""APL"" ""DLF"" ""AAF"" ""FEB10"" ""4"" ""QTV"" "" "" "".00"" "" "" ""0.00"" ""10.0"" ""0.00"" ""10.0"" ;

"" "" ""CODE"" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""PAYABLE"" ""UNITS"" "" "" "" "" ;";

string[] singleLines = testString.Split(";".ToCharArray(),StringSplitOptions.RemoveEmptyEntries);

string newOneLine = String.Empty;

foreach (string oneLine in singleLines)

{

newOneLine = oneLine.Replace("\r", "").TrimStart();

if (newOneLine.StartsWith("\" \""))

{

//remove the first space.

Regex r = new Regex("\" ",RegexOptions.IgnoreCase);

newOneLine = r.Replace(newOneLine, "\"~", 1);//Use a character you want.

}

string[] individualValues = newOneLine.Replace("\" \"", ",").Split(",".ToCharArray());

}

This will still not give you the nice output line that you are looking for but you can modify it to suit your purpose.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 11

thanks rjv

i will check it and let you know

best regards

asita

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 12

BCP and Bulk Insert really don't care how many "rows" make up a "record" or whether or not each row has the same number of "fields" so long as the "record" has the same number of "fields". Not sure I have time for it tonight or not but I'll try to reserve a little time to whip up an example. If I can't get Bulk Insert to swallow it, I do know a couple of T-SQL tricks that can help. Sounds like fun.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)