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?
+1
A:
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
2009-12-31 19:26:36
+5
A:
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
2009-12-31 19:31:55
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
2009-12-31 19:45:06
+2
A:
Adam Machanic had a good article on writing SQLCLR string parsers. Check this out:
Bob Pusateri
2009-12-31 19:33:27