June 15, 2007 at 1:21 pm
"Abbott, Ann, President --Leaflet Missal Company --St. Paul MN "
"Abdallah, Karen, Media Designer --L-Com, Inc. --North Andover MA "
"Abdul Raheem Khadri, Mohammed, Deputy Manager --IRMAC Services India Limited --Hyderabad India "
"Abraham, Julie, CEO --DMJ Search --Killingworth CT "
"Acher, Debby, Mgr. Catalog Planning/Analysis --Talbots --Hingham MA "
"Acker, Ron, -- --Red Oak TX "
"Adams, Jon, Internet Marketing Manager --Hach Company --Loveland CO "
"Ades, Abraham, President --Heavenly Treasures --Allenhurst NJ "
"Ades, Michael, Vice President --Heavenly Treasures --Allenhurst NJ "
How do I insert into a table this file where the names, business, etc are in seperate columns.
The file is actually several thosand rows in size.
Thankyou.
June 15, 2007 at 2:05 pm
Use the Import/Export wizard (DTS) - Right click a database and choose "All Tasks - Import/Export Data..."
If you use -- as your delimiter your table will end up like this:
vcName vcCompany vcCityState
--------------------------------------------------------------------------------------------------------------------------
"Abbott, Ann, President Leaflet Missal Company St. Paul MN "
"Abdallah, Karen, Media Designer L-Com, Inc. North Andover MA "
"Abdul Raheem Khadri, Mohammed, Deputy Manager IRMAC Services India Limited Hyderabad India "
and so on...
Then you can add an execute SQL Task that strips off the double quotes. To parse with different delimiters per line (such as you want to seperate out Abbot, Ann, President), gets more involved. But this is a good start.
Something you may want to consider is changing your file format so that you can utilize Comma Delimited and Quoted Identifiers: Example Line:
vcName,vcTitle,vcCompany,vcCity,vcState
"Abbott, Ann","President","Leaflet Missal Company","St. Paul","MN"
"Abdallah, Karen","Media Designer","L-Com, Inc.","North Andover","MA"
"Abdul Raheem Khadri, Mohammed","Deputy Manager","IRMAC Services India Limited","Hyderabad","India"
-
June 20, 2007 at 11:58 pm
Or... use a BCP Format file...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2007 at 8:41 am
Jeff, will that account for every value being in a field by itself? That is, lastname, firstname, title, company, address, city, etc. If not, if this is a one-off process, then open the .txt file and use a find and replace to replace the "--" with "," and then just use the "," as the delimiter. If this is going to be a scheduled job of some sort, then I am sure that the person can then use ActiveX to do the find and replace, right? Thanks.
Chris
June 22, 2007 at 1:28 pm
Yes, and no... if you use a format file for repetative imports, the answer is yes... but the data in this example won't support your method or my method because the city and state have no delimiter. A secondary process (update query) would be required for that... if it's required.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2007 at 1:31 pm
You're right...I didn't notice that with the city and state. 🙂
July 5, 2007 at 2:08 pm
Just a note on format.
In the future, you may wish to put your question first and the sample data after it. The newletter includes your subject and the first part of your message. The sample data did not provide any useful information about your needs.
Steve
July 6, 2007 at 12:13 am
Heh... did for me... data is pretty well broke...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2007 at 7:05 am
Jeff,
You're right, the data is useful for resolving the problem. It just wasn't as useful to know whether the topic is one that you even wanted to look at. Although, I'll have to admit, one of the reasons I looked at it was wondering what the heck is this.
Steve
July 6, 2007 at 8:21 am
Thanks, Steve,
The thing that really kills me is the cruddy data that poor ol' Jerry (the OP) has received. The data can be imported and correctly parsed even making some distinction between a 2 digit State and a Country. Jason posted a correct answer for the first step of this process but I'm just amazed at the crud data that people are provided from supposed knowledgeable sources of data. I try not to take things like that out on the OP 'cause it's just not their fault.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply