locked
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. RRS feed

  • 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

    • Edited by Frank008 Monday, March 4, 2013 3:13 AM
    Monday, March 4, 2013 2:59 AM

All replies

  • 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

    • Marked as answer by Frank008 Monday, March 4, 2013 3:37 PM
    • Unmarked as answer by Frank008 Tuesday, March 5, 2013 2:20 AM
    Monday, March 4, 2013 5:12 AM
  • 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.

    Monday, March 4, 2013 5:39 AM
  • You can use DTS wizard....
    Monday, March 4, 2013 7:12 AM
  • 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

    Tuesday, March 5, 2013 12:37 AM
  • 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

    • Marked as answer by Allen Li - MSFT Friday, March 15, 2013 2:31 AM
    • Unmarked as answer by Frank008 Tuesday, March 19, 2013 1:43 AM
    Tuesday, March 5, 2013 5:11 AM
  • 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.

    Tuesday, March 5, 2013 5:28 AM