How to Import Data from a Data file having multiple Delimeters ?

• chaithu559

  SSCommitted

  Points: 1574

  More actions

  March 17, 2011 at 9:54 am

  #233347

  Hi Everyone,

  Can anyone Please help me out in importing data from a data file which has multiple delimiters (like 1st column with ";" and 4th column with ",") to the Sqlserver table.Please reply to this

  post if you have came across this kind of scenario

  Thanks and Regards

  Chaithu

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  March 17, 2011 at 10:34 am

  #1300061

  What method are you utilizing to attempt to import the file SSIS or OPENROWSET or BCP?

  Not knowing that, but here are some ideas which might spark your thinking.

  1. Import the file into a single column staging tables.

  2. Decide on a single suitable delimiter for example a comma

  3. Use the REPLACE function to replace the unwanted (in your example semi colon ' ; ' with a comma.

  4. Then utilize a function to parse the resulting single string. For example the [DelimitedSplit8K] by Jeff Modden, at

  http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589

  and insert the data into the proper table.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]