I have Two Flat File one is Delimiter and other one is Fixed Length I want load this files into One table in below format by using stored Procedure dynamically.

Question

All the require information realted to this  files are given in one table.

For Ex,FileName,FileType,Startposition(Fixed only),EndPosition),Length(Delimited),Delimitor(,),Textquilfer("),Endoffileflag('\n')

For Creation of Stored Procedure you can use all above information as dynamic variable.Please find two Flat file and require output Excel sheet in the below image. SQL Server Stored Procedure requires to load this Files dynamically in below output format.

I appreciate your help in advance.

Some what how i can able to attache Image insert data here

OutPut data

FileKey	(Column)	(Raw)	Value
1	1	1	Toyota
1	2	1	501
1	3	1	Memphis
1	4	1	1000
1	5	1	John
1	1	2	Honda
1	2	2	502
1	3	2	Atlanta
1	4	2	5000
1	5	2	Mettaw
2	1	1	Toyota
2	2	1	501
2	3	1	Memphis
2	4	1	1000
2	5	1	John
2	1	2	Honda
2	2	2	502
2	3	2	Atlanta
2	4	2	5000
2	5	2	Mettaw

Fixed Length Files

Toyota 501 Memphis 1000 John
Honda  502 Atlanta 5000 Mettaw

Delimited File

Toyota,501,Memphis,1000,John
Honda,502,Atlanta,5000,Mettaw

Thanks

Frank

Answer 1

Hi,

You can also use bcp to load the file into your DB

http://msdn.microsoft.com/en-us/library/ms162802.aspx

---

Many Thanks & Best Regards, Hua Min

Answer 2

you can try SSIS or DTS package to load files.

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

Answer 3

You can use DTS wizard....

Answer 4

Thanks Hua Min and Rakesh.

Actually the requirement is Stored Procedure to make it happen whether it is Fixed length File or Delimited File(you will have all the information as i mention in above question.So Please let me know if you guys can help me with that one.

I am guessing that BCP will not work for both files as well the format which we are looking for.

Thanks

Frank

Answer 5

Try something like this in your SP

INSERT INTO myTable(FileName, FileType, Document) 
   SELECT 'Text1.txt' AS FileName, 
      '.txt' AS FileType, 
      * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO
and read
http://msdn.microsoft.com/en-us/library/ms190312.aspx

---

Many Thanks & Best Regards, Hua Min

Answer 6

You can achive in following ways

1.By using SQL script(Some what complex)

2.Using SQL import and export wizard(Some what OK for your senerio)

3.using SSIS (Very easy for senerio).

 

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.