Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Multiple Delimiters in an Extract

Answered Multiple Delimiters in an Extract

  • Wednesday, January 09, 2013 1:58 PM
     
     
    Sign In to Vote
    0

    Hi,

    I am attempting to pull a extract from our server, SQL Server 2008 R2.  In a normal extract I would use SSIS to a delimited Text File, however this extract is looking for something a litte bit more complex.  It needs to have a delimiter at the end of a record(Line Feed), one after each field (Comma), and then one after each value in that field (Tab). 

    An example of how this would look would be:

              ID, Address Line 1     Address Line 2     Address Line 3, City, State, Zip, Address Type, Phone Num 1     Phone Number 2   ...., Phone Number Type 1     Phone Number Type 2     ...\LF

    For the multiple Address line that is a simple concatenation from the db, but the phone numbers are providing some issues.  Each ID could have multiple numbers, in our db as separate records.  For Example:

    ID     Phone Number     Phone Number Type

    12      555-555-1234      Home

    12      555-555-1235      Cell

    12 ....

    Any ideas on how i would work through the issue of trying to make this work?

    Any help would be much appreciated.

    Thanks,

    Matt

     

All Replies

  • Wednesday, January 09, 2013 2:07 PM
     
     
    Sign In to Vote
    0

    Hi,

    I can think of 2 alternatives :

    1. Either importing from comma-delimited text into a staging table, where address and phone fields will be strings with none, one or more Tabs in the middle.

    Then, you'll have to import from the staging table into the final table taking care of the Tabs.

    2. Or, write a VB/VC# script to import straight from the text file into the final table, and do the line parsing (splitting commas and tabs) yourself

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

     
  • Wednesday, January 09, 2013 2:26 PM
     
     
    Sign In to Vote
    0
    Thank you for the prompt reply.  Unfortunately, this isn't for me to import the data.  I need to pull this information from our server, to send it to someone else.  One the other end this is how they want to receive the data from us.  Do you have any ideas?
    • Edited by mjrobison Wednesday, January 09, 2013 2:44 PM
    •  
     
  • Wednesday, January 09, 2013 2:46 PM
     
     Answered
    Sign In to Vote
    0

    Hi,

    for getting one long row from many short, try to look for pivot transformation.

    For export to special formatted flat file, you have to create your own destination. It can be done by Script transformation in DFT used as destination. But it needs coding skills.

    I used similar solution for migration and it worked.

    Regards

    Zdenek

    Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu


    • Edited by Zdenek Nosek Wednesday, January 09, 2013 2:47 PM
    • Marked As Answer by mjrobison Wednesday, January 09, 2013 8:52 PM
    •  
     
  • Wednesday, January 09, 2013 4:51 PM
     
     
    Sign In to Vote
    0

    If you are writing a SSIS package then you can pull all the data into stage table with main conditions.

    Then have to massage the data to see if there is multiple values like for phone number - then re-write the data as TAB delimited.

    In the next step do this task  - i.e comma demiter and line feed ending you can done.

    But could you tell me what is the exact error you are getting?