none
how to insert (*.txt file) into (table) in sql server in the my example , please help with query ?

    Question

  • The next part of my example (file *.txt) :

    Keywords Date and Time Source Event ID Task Category
    Audit Success 15/02/2014 05:17:52 م Microsoft-Windows-Security-Auditing 4656 Other Object Access Events "A handle to an object was requested.

    Subject:
     Security ID:  SYSTEM
     Account Name:  SRAJ-PC$
     Account Domain:  WORKGROUP
     Logon ID:  0x3e7

    Object:
     Object Server:  PlugPlayManager
     Object Type:  Security
     Object Name:  PlugPlaySecurityObject
     Handle ID:  0x0

    Process Information:
     Process ID:  0x354
     Process Name:  C:\Windows\System32\svchost.exe

    Access Request Information:
     Transaction ID:  {00000000-0000-0000-0000-000000000000}
     Accesses:  Unknown specific access (bit 1)
        
     Access Reasons:  -
     Access Mask:  0x2
     Privileges Used for Access Check: -
     Restricted SID Count: 0"
    Audit Success 15/02/2014 05:17:51 م Microsoft-Windows-Security-Auditing 4656 Other Object Access Events "A handle to an object was requested.

    Subject:
     Security ID:  SYSTEM
     Account Name:  SRAJ-PC$
     Account Domain:  WORKGROUP
     Logon ID:  0x3e7

    Object:
     Object Server:  PlugPlayManager
     Object Type:  Security
     Object Name:  PlugPlaySecurityObject
     Handle ID:  0x0

    Process Information:
     Process ID:  0x354
     Process Name:  C:\Windows\System32\svchost.exe

    Access Request Information:
     Transaction ID:  {00000000-0000-0000-0000-000000000000}
     Accesses:  Unknown specific access (bit 1)
        
     Access Reasons:  -
     Access Mask:  0x2
     Privileges Used for Access Check: -
     Restricted SID Count: 0"
    Audit Success 15/02/2014 05:17:50 م Microsoft-Windows-Security-Auditing 4656 Other Object Access Events "A handle to an object was requested.

    ----------------------------------------------------------------------------------------

    convert all text bold and values exist in text file to columns in table in sql server :

                                  columns :

            Security ID       Account Name      Account Domain    Logon ID     Accesses    



    • Edited by srajmuneer Sunday, February 16, 2014 7:29 PM
    • Moved by Kalman Toth Wednesday, February 26, 2014 5:29 PM Not t-sql
    Saturday, February 15, 2014 2:24 PM

Answers

  • The query is the easy part:

    INSERT tbl(....)
       SELECT ... FROM CLR_function_that_parses_this_file(@filepath)

    The CLR function that parses the file and extracts the data is the difficult part, but nothing of that has any relevance to this forum. And there is not really any compelling reason why you would read the file from SQL Server is there? It seems more appropriate to write a standalone program that reads the file and then passes the data to SQL Server.

    No, there is nothing built-in for this type of file, if that is what you expected.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 10:02 PM
  • Erland is proposing to not use T-SQL at all for parsing your file. In your case you need to use some other means to get that data information into a table, as this is not a simple format that SQL Server (or SSIS) can easily understand.

    So, you need to write a parsing program in, say, C#.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by HimanshuSharma Tuesday, February 18, 2014 8:21 AM
    • Marked as answer by Kalman Toth Wednesday, February 26, 2014 5:23 PM
    Monday, February 17, 2014 6:56 PM
  • As Erland indicated I have good news for you: SSIS.

    Learn it to solve your problem and even get high-paid job with the new know-how.

    Limited conversion with SSIS Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    Full conversion ("Sky is the limit") with custom SSIS package.

    I am moving it to SSIS for additional assistance.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Marked as answer by srajmuneer Friday, February 28, 2014 7:46 PM
    Wednesday, February 26, 2014 5:29 PM

All replies

  • What exactly is the problem? Please explain more.

    sqldevelop.wordpress.com

    Saturday, February 15, 2014 2:50 PM
  • The query is the easy part:

    INSERT tbl(....)
       SELECT ... FROM CLR_function_that_parses_this_file(@filepath)

    The CLR function that parses the file and extracts the data is the difficult part, but nothing of that has any relevance to this forum. And there is not really any compelling reason why you would read the file from SQL Server is there? It seems more appropriate to write a standalone program that reads the file and then passes the data to SQL Server.

    No, there is nothing built-in for this type of file, if that is what you expected.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 10:02 PM
  • Erland is proposing to not use T-SQL at all for parsing your file. In your case you need to use some other means to get that data information into a table, as this is not a simple format that SQL Server (or SSIS) can easily understand.

    So, you need to write a parsing program in, say, C#.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by HimanshuSharma Tuesday, February 18, 2014 8:21 AM
    • Marked as answer by Kalman Toth Wednesday, February 26, 2014 5:23 PM
    Monday, February 17, 2014 6:56 PM
  • So, you need to write a parsing program in, say, C#.



    Thank you Naomi  N , who to write a parsing program in, say, C#,  please more details or example
    Monday, February 17, 2014 8:14 PM
  • Can you post the exact sample of your input file? In any case, looks like this problem is a bit too complex. I would ask help in C# forum for writing that parser.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, February 17, 2014 8:24 PM
  • I you don't know C# or some other programming language yourself, you need to find a programmer in your organisation who can help you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, February 17, 2014 10:58 PM
  • The query is the easy part:

    INSERT tbl(....)
       SELECT ... FROM CLR_function_that_parses_this_file(@filepath)

    Thank you Erland  Sommarskog , what is CLR_function and how to use the CLR_function and What is the ultimate form of query

    please the easy solution or example



    Tuesday, February 18, 2014 4:08 AM
  • The query is the easy part:

    INSERT tbl(....)
       SELECT ... FROM CLR_function_that_parses_this_file(@filepath)

    Thank you Erland  Sommarskog , what is CLR_function and how to use the CLR_function and What is the ultimate form of query

    please the easy solution or example



    CLR is Common Language Runtime, in terms of MSDN. But there is one more famous CLR that is Canonical LR Parsers. Look for both. Interesting topics.


    Please use Mark as Answer; if my reply solved your problem. Use Vote As Helpful if a post was useful.

    |A Wise Man Once Said Nothing| |WWW.FACEBOOK.COM/SONNIE.HIM | | +91-9742-354-384 |


    Tuesday, February 18, 2014 8:25 AM
  • This is a text and document problem. SQL is not the right tool for it. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, February 18, 2014 3:26 PM
  • Is there a way successful of insert (*.txt file) or (*.xml) or (*.csv) into (table) in sql server

    please step by step


    Tuesday, February 18, 2014 5:11 PM
  • The topic for BULK INSERT should show you how to load an XML file from disk. I believe there are examples of how to use the option SINGLE_BLOB.

    CSV files can be loaded with BCP or BULK INSERT, but it depends exactly on how they are formatted. If data is only quoted sometimes it will not work. But there is also SQL Server Integration Services (SSIS). Which you can access in a stripped-down version as the Import and Export Wizard in SSMS.

    Anyway, these forums are not really suitable for complete beginners, but more apt for someone who has a specific question. This is not where you get step-by-step answers. For instance, I can't give any help on SSIS at all, because I don't know it myself. And I don't use the Import/Export Wizard much. But maybe Kalman can jump in and post a link on it? I know that he is a great fan of that tool.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, February 18, 2014 11:01 PM
  • As Erland indicated I have good news for you: SSIS.

    Learn it to solve your problem and even get high-paid job with the new know-how.

    Limited conversion with SSIS Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    Full conversion ("Sky is the limit") with custom SSIS package.

    I am moving it to SSIS for additional assistance.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    • Marked as answer by srajmuneer Friday, February 28, 2014 7:46 PM
    Wednesday, February 26, 2014 5:29 PM