import text to database
Hi,
I have a text file that looks like this:
Chapter 1
Line 1: some text here some text here
Line 2: some text here some text here
Line 3: some text here some text here
Chapter 2
Line 1: some text here some text here
Line 2: some text here some text here
Line 3: some text here some text here
I want to import this into a database so that the table will look something like this:
Chapter, Line, Text
1,1,some text here some text here
1,2,some text here some text here
1,3,some text here some text here
2,1,some text here some text here
2,2,some text here some text here
I guess I need to wrote some custom C#/VB code to get this done, is this correct or can I use SSIS with / without the custome code.
TIA
eddiec :-)
October 21st, 2010 8:50am
First create a staging table for the upload:
CREATE TABLE stageBook (
BookID INT IDENTITY(1,1) PRIMARY KEY,
Line varchar(128));
Then use the SSIS Import/Export wizard to upload the text into the table.
Renumber the uploaded text by parsing to desire into the final (Book) table.
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 9:13am
CREATE TABLE TmpStList
(
stFName varchar (10) NOT NULL,
stLName varchar (10) NOT NULL,
stEmail varchar (30) NOT NULL
)
go
The data file (hawk.dat):
"Kelly","Reynold","kelly@reynold.com"
"John","Smith","bill@smith.com"
"Sara","Parker","sara@parker.com"
The format file (hawk.bcp):
8.0
4
1 SQLCHAR 0 1 "\"" 0 first_quote ""
2 SQLCHAR 0 10 "\",\"" 1 stFName ""
3 SQLCHAR 0 10 "\",\"" 2 stLName ""
4 SQLCHAR 0 30 "\"\r\n" 3 stEmail ""
bulk insert TmpStList from 'C:\Staging\hawk.dat'
with (formatfile = 'C:\Staging\hawk.bcp')
select * from TmpStList
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
October 21st, 2010 10:21am
Uri,
The upload order of text lines is not guaranteed that way (Chapter 10 may preceed Chapter 2). You need an INT IDENTITY PRIMARY KEY on the table.Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 10:46am
Do I need to care about order while doing insertion? Later on use ROW_NUMBER() ,whatever function to return ordered setBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
October 21st, 2010 11:55am