Post reply

Reading .TXT File

  • June 3, 2010 at 2:49 pm

    #157002

    I have a text file with the following format that are generated after every few hours. I want to have anything before (=) sign as a column names and after (=) will be the rows.

    Line=001

    Machine=3333B

    Description=Machine System

    PartNumber=H00000

    NominalCycle=30.00

    Line=002

    Machine=3333C

    Description=System

    PartNumber=HF0939

    NominalCycle=20.00

  • June 3, 2010 at 2:51 pm

    #1176212

    What is your question?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 3, 2010 at 3:01 pm

    #1176218

    How would you have everything before (=) sign as name of the column and after the sign would be the rows.

    here is an example:

    Line Machine Description PartNumer NomincalCycle

    001 3333B Machine System H00000 30.00

    002 45333B System J00000 20.00

  • June 3, 2010 at 3:06 pm

    #1176220

    To get the data into SQL server, look into bcp or OPENROWSET, maybe even linked server (depends on the -unknown- scenario).

    You could use CHARINDEX() and LEFT/RIGHT/STUFF to separate the two values. You could also use a split string function to separate the data.

    Once you have your data separated you could either use PIVOT or the CrossTab solution (see the link in my signature) to transform it into columns and rows.

    I hope that helps to get you started...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • June 4, 2010 at 12:05 pm

    #1176676

    Since this was posted in an SSIS forum I am assuming you want to do this in SSIS..

    The built-in data sources CANNOT easily do this, you might be able to build a command that MIGHT be able to pull it off but I think you will have trouble with that.

    Also, I see some additional problems with doing this in SSIS, the column meta-data such as column name and type MUST be set at design-time, it CANNOT change at run-time. So if the file structures are highly variable this is going to be problematic, you would need a way to pick out which file has which structure and send it to the right data-flow.

    I *believe* that a CozyRoc product does allow meta-data changes at run-time, you will have to look at that. http://www.cozyroc.com/.

    You will still need to find a good way to get the data into a usable structure.

    CEWII

  • June 5, 2010 at 6:22 am

    #1176788

    A script component would also be able to do the job (read the lines, form a single record, then output it).

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 5, 2010 at 1:30 pm

    #1176806

    I was able to solve your issue.

    As mentioned in this thread you can use the Pivot transformation to convert the file. But for Pivot you would need 3 columns else if will give you Pivot has duplicate rows error.

    To overcome this I have givena unique row number using script component in data flow task.

    Attached is the code for the same.

    Team,

    Can you please review the code and evaluate it in terms of Performance and optimization.

    thanks

  • June 7, 2010 at 6:52 am

    #1177059

    SSIS_NewBi (6/5/2010)

    Team,

    Can you please review the code and evaluate it in terms of Performance and optimization.

    thanks

    :ermm:

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 7, 2010 at 6:53 am

    #1177060

    da-zero (6/7/2010)

    SSIS_NewBi (6/5/2010)

    Team,

    Can you please review the code and evaluate it in terms of Performance and optimization.

    thanks

    :ermm:

    Wow, terse evaluation :w00t:

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 8, 2010 at 10:53 am

    #1177573

    SSIS_NewBi (6/5/2010)

    I was able to solve your issue.

    As mentioned in this thread you can use the Pivot transformation to convert the file. But for Pivot you would need 3 columns else if will give you Pivot has duplicate rows error.

    To overcome this I have givena unique row number using script component in data flow task.

    Attached is the code for the same.

    Team,

    Can you please review the code and evaluate it in terms of Performance and optimization.

    thanks

    You are really amazing!!!

    That really solved the problem that I have. Thank you very much.

    One more question, is this possible if we replace the source text file with different name after every hour?

  • June 8, 2010 at 2:12 pm

    #1177665

    Hi

    Yes you can do that. There are two scenarios

    1. There is only 1 file in the folder at a specific time

    Please follow my posting

    http://qa.sqlservercentral.com/Forums/Topic933534-148-1.aspx

    2. If you have multiple files in the location and you would like to pcikup the latest file based on time stamp then you can use WMI Task .

    For scheduling you could use SQLServer Agent or scheduling tool like Autosys.

    I hope this helps

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply