0

I need to parse a backspace delimited flat file using sqlserver 2005 and update in some tables. What is the best way to go about it?

Thunderhashy
  • 5,291
  • 13
  • 43
  • 47
  • 1
    This looks awfully familiar... http://stackoverflow.com/questions/1983235/backspace-delimited-flat-files – Aaronaught Dec 31 '09 at 19:43
  • I tried to use the ParseString function found at this link http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103264 I gave the delimiter as '\b' but still I am not able to parse. I even tried with '\x08' with no success. Any ideas? – Thunderhashy Dec 31 '09 at 22:47

3 Answers3

5

Tried this?

BULK INSERT MyTable
FROM 'c:\file.csv' 
WITH 
( 
    FIRSTROW = 2, 
    MAXERRORS = 0, 
    FIELDTERMINATOR = '\b', 
    ROWTERMINATOR = '\n' 
)

It may or not work with that delimeter, can also try \x08

Paul Creasey
  • 28,321
  • 10
  • 54
  • 90
  • I think Paul has the best way. Import that file into a table and and run your update from there. I had some problems with SQL server 2000 and the flat file import option. I usually imported the file into excel saved it and imported it in SQL server. That is only feasible if you need to do it rather seldom. If you want to automate it, and above does not work. Run a search and replace on your delimeter and replace it with something useful. – Peter Schuetze Dec 31 '09 at 19:45
2

Adam Machanic had a good article on writing SQLCLR string parsers. Check this out:

http://dataeducation.com/faster-more-scalable-sqlclr-string-splitting/

Community
  • 1
  • 1
Bob Pusateri
  • 722
  • 5
  • 15
1

What you need is a C# Split like function in TSQL. Such a function doesn't exist. However, many people have written a function like this. For example:

http://blogs.vbcity.com/hotdog/archive/2008/06/04/9085.aspx

Randy

Randy Minder
  • 47,200
  • 49
  • 204
  • 358