September 15, 2005 at 3:07 am
Hi,I want to import a txt file to a sql server table. The table has 50 columns. I have pasted some sample from the txt file for reference. I will be using DTS to import the file. Please tell me which format to use when creatting the DTS to import this file, I mean the Tab delimiter etc. Thanks.
--Kishore
9/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN UNKNOWN 01022005190052 NHSGIS0054 - NHS Security WPG Closure Green John Housley Security Services EMEA NHS Client Requested 2/13/2005 7:00:00 PM 12/30/2005 7:00:00 PM 23363 2146647 464366 8/30/2005 6:11:46 AM Not Applicable NHS GIS P1R1b United Kingdom L 0 0 0 0 -1449938 -1449938 -442613 -442613 Yes Yes 12/30/2005 7:00:00 PM 23363 8048 1449938 0 696709 1449938 0 696709 442613 0 217539/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN UNKNOWN 01032005091553 H&SC Information Centre Planned Green Glyn P Anderson Service Delivery Mgmt - - Nth Region EMEA Dept of Health Client Requested 2/28/2005 7:00:00 PM 3/30/2006 6:00:00 PM 3000 1000100 0 8/18/2005 5:03:59 AM Not Applicable Not Applicable United Kingdom L 1000000 1000000 1000000 250000 999900 999900 999900 100 100 100 Yes No 3/30/2006 6:00:00 PM 3000 0 100 0 1000000 100 0 1000000 0 0 09/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN Northern 01042005105224 RMG Lotus Notes Archiving Open Active Green Brian Gott Computing Services (EMEA) CSC Internal Transformation 12/12/2004 7:00:00 PM 10/30/2005 7:00:00 PM 2900 835798 403664 9/2/2005 10:49:20 AM Not Applicable Not Applicable United Kingdom L 0 0 0 0 -290064 -290064 -290064 -46473 39 39 39 Yes Yes 7/28/2005 7:00:00 PM 2900 677 290064 0 545734 290064 0 545734 38446 8027 3571919/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN UNKNOWN 01062004091130 CoBRa Migration - Technology Refresh Open Active Green Martin Pressinger Service Delivery Mgmt Old Mutual Old Mutual Client Requested 2/14/2004 5:00:00 PM 10/14/2005 6:00:00 PM 4500 0 0 8/2/2005 6:41:51 AM Not Applicable OM243 Wintel Programme South Africa L 0 0 0 0 -164280 -164280 -128904 -108172 Yes Yes 7/30/2005 6:00:00 PM 4500 3000 164280 0 4103 164280 0 4103 108172 0 41039/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN UNKNOWN 01072005153617 RMG - CCN002 - Order To Cash - OTC Closure Green Peter Koefoed-Meyer Web Services EMEA RMG - Royal Mail Group Client Requested 6/5/2005 6:00:00 PM 9/22/2005 6:00:00 PM 312 181692 163898 9/9/2005 11:45:00 AM Not Applicable Not Applicable Denmark L 0 0 0 0 -60754 -63184 -63184 -57108 Yes Yes 9/22/2005 6:00:00 PM 300 282 63184 0 118508 60754 0 116551 57108 0 1067909/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN UNKNOWN 01092005151051 SAN Review (Deployment, TtT & EUT env) Open Active Green Kiran P Haria Service Delivery Mgmt - - Nth Region EMEA NHS GIS Internal 7/17/2005 7:00:00 PM 2/27/2006 7:00:00 PM 1613 2104798 1097764 9/8/2005 8:09:35 AM Not Applicable NHS GIS Dep & Misc United Kingdom L 0 0 0 0 -757938 -757938 -757938 -22740 Yes Yes 2/27/2006 7:00:00 PM 1613 223 174640 583298 1346860 174640 583298 1346860 22740 0 10750249/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN Northern 01102004112413 Machine room 5 build (Copenhagen) Closure Green Phillip Saxton Facilities CSC Internal GIS Internal 9/30/2004 7:00:00 PM 8/9/2005 7:00:00 PM 0 6100000 6100000 8/12/2005 6:58:26 AM Not Applicable Not Applicable Denmark L 0 0 0 0 0 0 0 0 Yes Yes 8/9/2005 7:00:00 PM 0 0 0 0 6100000 0 0 6100000 0 0 61000009/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN Northern 01112004123437 GIS EMEA Finance - Volumetrics Reporting Database Open Active Green Jason Kershaw Computing Services (EMEA) CSC Internal GIS Internal 4/26/2005 7:00:00 PM 10/6/2005 7:00:00 PM 3417 905053 630307 9/8/2005 1:45:26 PM Not Applicable Not Applicable United Kingdom L 0 0 0 0 -131775 -435053 -435053 -252326 GSS Security Review Not Planned For Exceptional Other Managing Situation On Hourly Basis To Drive Proceess To Completion 8/8/2005 Yes Yes 5/30/2005 7:00:00 PM 1781 2233 225263 209790 470000 104950 26825 33144 145133 107193 3779819/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN UNKNOWN 01112004183144 NHSGIS0041 - NHS Exit Plan - 11000-001 On Hold Green Phil Chalmers Service Delivery Mgmt - - Nth Region EMEA NHS Client Requested 10/31/2004 7:00:00 PM 11/29/2005 7:00:00 PM 2260 206914 198468 9/9/2005 3:47:32 AM Not Applicable NHS GIS Dep & Misc United Kingdom L 0 0 0 0 -46726 -206914 -206914 -198468 Awaiting response from Authority to initial draft None required, regular contact with Authority contact being maintained 11/30/2005 Yes Yes 6/28/2005 7:00:00 PM 100 2240 189450 17464 0 45000 1726 0 186168 12300 09/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN UNKNOWN 01122003084120 Server Refresh - Infrastucture Refresh Programme Open Active Green Kieran J Russell Service Delivery Mgmt - - Nth Region EMEA Schroders GIS Internal 10/28/2004 7:00:00 PM 1/29/2006 7:00:00 PM 4000 12000000 8729593 9/8/2005 4:03:46 AM Regional Deployment Schroders Infrastructure Refresh Programme United Kingdom L 0 0 0 0 -2000007 -4000000 -2227631 -2092893 Yes Yes 6/29/2004 7:00:00 PM 2000 26631.26 4000000 0 8000000 2000007 0 8000000 2092893 0 66367009/15/2005 1:30:40 AM Sep 2005 UNKNOWN UNKNOWN UNKNOWN 01122004105616 POL-MI S80 Open Active Green Jamie L Rollings Computing Services (EMEA) RMG - Royal Mail Group Client Requested 11/21/2004 7:00:00 PM 9/29/2005 7:00:00 PM 4193 396952.56 134899 9/12/2005 7:27:44 AM Not Applicable Not Applicable United Kingdom L 393787 396953 190802 134899 0 0 0 0 -4 Yes Yes
September 15, 2005 at 4:07 am
Difficult to tell from the way that the stuff's been formatted for display. What is the field delimiter (ie which character appears between the fields and nowhere else)?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 15, 2005 at 4:17 am
I have just copied some sample data from the txt file. There is no way to upload the file in the forum. What can be done ?
--Kishore.
September 15, 2005 at 9:29 am
DTS is set up to import "Comma", "Tab", "Semicolon", or "Other" (such as "pipe" or any other character) Comma & Tab are quite common, so bringing in a .CSV or orther standard file is quite straightforward. The bigger problem you might have is with the data itself. Where is it coming from ? We've encoutered problems importing from Excel, because data such as dates can sometimes include invalid dates, so SQL will not allow invalid dates into datetime fields. If your data is "clean", then DTS is the way to go.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply