From flat file to table
-
"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.
-
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"
-
-
Or... use a BCP Format file...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You're right...I didn't notice that with the city and state. 🙂
-
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
-
Heh... did for me... data is pretty well broke...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply