Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16

    Unanswered: Only bringing in select columns from a text file

    Hi all,


    I have some bad data that i'm trying to do a workaround for. I have a comma delimited text file with a column which contains a few records that has, gasp, a comma in it. Thus creating an extra column and pushing out NULL values in an empty column that isn't recognized by my DTS package.
    Can i fix this?

    And if I'm correct in my assumption that I can't, how do I import only the fields preceding the bad data field? When i attempt to do that, I get the following message

    'Too many columns found in the current row; non white-spaced characters found after the last defined column's data.'

    Blindman, you probably know exactly what to do, huh?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Look up BCP and Format file in BOL.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Exactly what to do?

    Shoot the jackass who created the datafile.

    If you are in luck (which you probably aren't) the string fields are enclosed by quotes, in which case commas won't cause a problem. But you are having a problem, so forget that...

    Check you datafile. Commas embedded in text are followed by spaces, while comma delimiters may not be, depending on the system that generated the file. If this is true with your data, then try this trick:

    1) Using a text editor, replace all occurrences of ", " with another string, such as "azbycx".
    2) Replace all occurrences of "," with the pipe character "|".
    3) Replace all occurrences of "azbycx" with your origianl ", ".

    Voila! If all goes well you now have a pipe delimited file.

    If your data is too messy for this, then you may have to load it into a staging table as a single column and write a procedure for parsing it yourself, based upon its characteristics.

    ...or you could just ask the jackass to give you a better file before you shoot him.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16
    I was hoping you would help!!! I know all about bcp and format file. Well, not all about it, but you know what I'm trying to say.

    No such luck for me today. Primarily because I can't shoot the jackass (laws, you see), and I can't send the data back. Fortunately, I don't NEED that particular troublesome column, so how can I bring in only the columns preceding the bad data filled one?

    I attempted a hideiously clumsy but successful workaround involving opening said text file in excel, copying and pasting, deleting bad columsn, creating array, and importing back into SQL. Obviously, this just won't work once the file gets to be over 65,536 rows.

    And I've been told, this is something we will be doing a lot of. I have a boss that doesn't accept the "bad data" excuse, electing rather to spit out, "Get creative, make it work."

    Am I just totally screwed??


    OH, and the comma delimiters are accompanied by different spacing , because it's really a fixed width comma delimited field. And columns without values are given a random fixed width (blank of course) ie. ,' ',' ',

    Nice, eh?
    Last edited by melatic; 12-03-04 at 12:49.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by melatic
    I was hoping you would help!!! I know all about bcp and format file. Well, not all about it, but you know what I'm trying to say.

    Relax..

    First, you should have books online open at all times...sometimes it's hard to navigate, but it's all in there.

    Second, There are MANY ways to do this...like load the whole thing into a single column varchar(8000) column, and use t-sql to audit the crap.

    Third, you could use a format card, like

    Code:
    7.0 
    18 
    1	SQLCHAR	0	14	""	1	EmployeeID 
    2	SQLCHAR	0	40	""	2	LastName 
    3	SQLCHAR	0	20	""	3	FirstName 
    4	SQLCHAR	0	60	""	4	Title 
    5	SQLCHAR	0	50	""	5	TitleOfCourtesy 
    6	SQLCHAR	0	26	""	6	BirthDate 
    7	SQLCHAR	0	26	""	7	HireDate 
    8	SQLCHAR	0	120	""	8	Address 
    9	SQLCHAR	0	30	""	9	City 
    10	SQLCHAR	0	30	""	10	Region 
    11	SQLCHAR	0	20	""	11	PostalCode 
    12	SQLCHAR	0	30	""	12	Country 
    13	SQLCHAR	0	48	""	13	HomePhone 
    14	SQLCHAR	0	8	""	14	Extension 
    15	SQLCHAR	0	0	""	0	Photo 
    16	SQLCHAR	0	0	""	0	Notes 
    17	SQLCHAR	0	14	""	17	ReportsTo 
    18	SQLCHAR	0	510	"\r\n"	18	PhotoPath
    This is from the Northwind Employees table...notice the zeroes?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    One other thing...you may be worrying about column (I don't know why if they're not image or text)...but I would get a sense that as soon as you figure this out, you're gonna have to start worrying about rows, more than columns...

    Did you ever think to create a staging table and make everything varchar?

    EDIT: Oh, and I like your bosses 'tude
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, if your data file was really "fixed width" you'd have no problems importing it, so I assume you just mean that empty values are deliniated by spaces.

    I do wonder, though, how you were able to load it into Excel and delete the bad columns. Not sure why you could do this in Excel and not in SQL Server.

    I have to ask: who is sending you this data on a regular basis that cannot send it in a standard format? (The boss's nephew?)

    Can you post one or two of the records that contain embedded commas, just so we can see what we are dealing with?

    Here is an option: load the record into a single large varchar. Write a User Defined Function that takes a string and parses off everything to the left of the first comma, truncating the rest of the string. Cycle through your staging table once for each column in your production table, using this function to populate the data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16
    First of all, thanks so much for the help. I wish I could relax, but I'm under major time constraints and I'm running a 102 fever.

    Okay, I uploaded the txt.file. A few new developments...
    When I open the file in Excel, I can see the record with the embedded comma. (Do a search on Policy_Number WRA1227). The comma is in one of the 'address lines' near the back of the file. When I so a search for that same Policy_Number in the txt file, I can't find that policy. ??

    Also, the other reason why I can't use bulk insert task to do this: Error 229. Don't have the rights.

    Other details, I work for a VERY small consulting company. My boss is an actuary, knows nothing about SQL. He programs in APL, and has no troubles do any of this stuff.

    I'm the only one here. So alone...

    This dump used to work before new columns were added. And I am importing everything into a staging table as varchar using Transform Data Properties using the text souce icon, and then playing with the data during later steps in my dts.

    Also should mention: I'm not a DBA. So I have a bit of trouble with the dynamic sql. Trying to learn it, but while under massive deadlines, I just do what I know how to do. Which isn't a whole lot.

    And to answer your question blindman, we are testing a new program for a developer for a fraction of the cost of purchasing a maintstream life insurance processing program. But we will have to be dealing with "bad data" all the time, so that's why he's being such a hardass.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Please cut and paste some sample rows of data that you have..

    Also post the DDL of the final destination table

    With that, I'm sure I can get you a solution quickly

    Make sure the sample data has good and bad rows.

    What does he want doen with the bad data
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16
    I know I should eta, but I noticed my insert didn't work. File too big... so I just replicated the problem on a similar record. Policy Number: T58I30

    Code:
    Product Type,Plan,Policy Number,ID Number,Agent,Group,Issue State,Issue Date,Mode,Specified Amount,Benefit,Base Premium,Rider Premium,Contract Value,Loan Balance,Surrender Charge,Surrender Value,PUA Dividends,OYT Dividends,Dividend Cash Balance,Dividend Cash Value,Status Date,Status Code,Paid To Date,Date Last Bill,APL Option,NFO Option,DIVD Option,District,Next Premium Due Date,Next Premium Due Amount,Scheduled Premiums,Premiums Paid,Insured IssueAge,Insured Sex,Insured Class,Insured Birthdate,2nd Insured IssueAge,2nd Insured Sex,2nd Insured Class,2nd Insured Birthdate,Riders Count,Rider1 Type,Rider1 Code,Rider1 IssDate,Rider1 ExpDate,Rider1 Benefit,Rider1 Premium,Rider1 Period,Rider1 Status,Rider1 TermDate,Rider2 Type,Rider2 Code,Rider2 IssDate,Rider2 ExpDate,Rider2 Benefit,Rider2 Premium,Rider2 Period,Rider2 Status,Rider2 TermDate,Rider3 Type,Rider3 Code,Rider3 IssDate,Rider3 ExpDate,Rider3 Benefit,Rider3 Premium,Rider3 Period,Rider3 Status,Rider3 TermDate,Rider4 Type,Rider4 Code,Rider4 IssDate,Rider4 ExpDate,Rider4 Benefit,Rider4 Premium,Rider4 Period,Rider4 Status,Rider4 TermDate,Rider5 Type,Rider5 Code,Rider5 IssDate,Rider5 ExpDate,Rider5 Benefit,Rider5 Premium,Rider5 Period,Rider5 Status,Rider5 TermDate,Rider6 Type,Rider6 Code,Rider6 IssDate,Rider6 ExpDate,Rider6 Benefit,Rider6 Premium,Rider6 Period,Rider6 Status,Rider6 TermDate,Rider7 Type,Rider7 Code,Rider7 IssDate,Rider7 ExpDate,Rider7 Benefit,Rider7 Premium,Rider7 Period,Rider7 Status,Rider7 TermDate,Rider8 Type,Rider8 Code,Rider8 IssDate,Rider8 ExpDate,Rider8 Benefit,Rider8 Premium,Rider8 Period,Rider8 Status,Rider8 TermDate,Rider9 Type,Rider9 Code,Rider9 IssDate,Rider9 ExpDate,Rider9 Benefit,Rider9 Premium,Rider9 Period,Rider9 Status,Rider9 TermDate,Rider10 Type,Rider10 Code,Rider10 IssDate,Rider10 ExpDate,Rider10 Benefit,Rider10 Premium,Rider10 Period,Rider10 Status,Rider10 TermDate,Rider11 Type,Rider11 Code,Rider11 IssDate,Rider11 ExpDate,Rider11 Benefit,Rider11 Premium,Rider11 Period,Rider11 Status,Rider11 TermDate,Rider12 Type,Rider12 Code,Rider12 IssDate,Rider12 ExpDate,Rider12 Benefit,Rider12 Premium,Rider12 Period,Rider12 Status,Rider12 TermDate,Rider13 Type,Rider13 Code,Rider13 IssDate,Rider13 ExpDate,Rider13 Benefit,Rider13 Premium,Rider13 Period,Rider13 Status,Rider13 TermDate,Rider14 Type,Rider14 Code,Rider14 IssDate,Rider14 ExpDate,Rider14 Benefit,Rider14 Premium,Rider14 Period,Rider14 Status,Rider14 TermDate,Rider15 Type,Rider15 Code,Rider15 IssDate,Rider15 ExpDate,Rider15 Benefit,Rider15 Premium,Rider15 Period,Rider15 Status,Rider15 TermDate,Rider16 Type,Rider16 Code,Rider16 IssDate,Rider16 ExpDate,Rider16 Benefit,Rider16 Premium,Rider16 Period,Rider16 Status,Rider16 TermDate,Next Rider Premium Date,Next Rider Premium Amount,Rider Premiums Scheduled,Rider Premiums Paid,Expiry Date,Insured Last Name,Insured First Name,Insured SSN,Billing Option,Bill Address1,Bill Address2,Bill City,Bill State,Bill Zip,Bill Phone
    S,DV583A,        56,         1,        HO,         0,AL,06241963,A,     5560.21,     5560.21,      396.20,        0.00,     3345.99,        0.00,        0.00,     3345.99,        0.00,        0.00,       97.37,       97.37,20041201,52,20470624,00000000,Y,E,A,    ,00000000,        0.00,    15055.60,    15055.60, 16,F,C,12051947,  0, ,C,00000000, 0, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000,00000000,        0.00,        0.00,        0.00,20460624,'HOEHN                    ','MARY           ','421-64-8104',D,'C/O FRED M HOEHN              ','525 22ND AVENUE S             ','BIRMINGHAM                    ','AL','35205-6429','            '
    S,T58I30,        72,         2,        HO,         0,FL,12271972,A,      265.99,      265.99,        0.05,        0.00,      186.22,        0.00,        0.00,      186.22,        0.00,        0.00,        0.00,        0.00,20041201,52,20381227,00000000,Y,E,C,    ,00000000,        0.00,        0.10,        0.08, 34,M,C,02201939,  0, ,C,00000000, 0, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000,00000000,        0.00,        0.00,        0.00,20371227,'POWELL                   ','RANDALL        ','000-00-0000',D,'%FAITH,INVESTMENT CO          ','NO CURRENT ADDRESS            ','PENSACOLA                     ','FL','32505     ','            '
    S,T58I30,        73,         3,        HO,         0,FL,12271972,A,      197.98,      197.98,        0.04,        0.00,      113.28,        0.00,        0.00,      113.28,        0.00,        0.00,        0.00,        0.00,20041201,52,20491227,00000000,Y,E,C,    ,00000000,        0.00,        0.08,        0.06, 23,M,C,07071949,  0, ,C,00000000, 0, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000,00000000,        0.00,        0.00,        0.00,20481227,'THOMAS                   ','HARVEY         ','000-00-0000',D,'%FAITH INVESTMENT CO          ','NO CURRENT ADDRESS            ','PENSACOLA                     ','FL','32505     ','            '
    S,121101,       055,         4,        HO,         0,WA,12121967,A,    10540.00,    10540.00,        0.00,        0.00,     9247.54,        0.00,        0.00,     9247.54,        0.00,        0.00,      300.80,      300.80,20041201,50,20181212,00000000,Y,E,C,    ,00000000,        0.00,     7727.00,     7727.00, 49,F,C,11211918,  0, ,C,00000000, 0, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000, ,      ,00000000,00000000,        0.00,        0.00,  0, ,00000000,00000000,        0.00,        0.00,        0.00,20171212,'HINZE                    ','PEARL          ','479-22-7580',D,'148 102ND SE APT 21           ','                              ','BELLEVUE                      ','WA','98004     ','            '

    Excuse my ignorance, but DDL? Is that just the create table logic?

    And thank you.
    Last edited by melatic; 12-03-04 at 16:00.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I need the DDL as well

    CREATE TABLE tablename(Col1 int, ect

    Do you know how to script that in Enterprise Manager?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Also I noticed that some of the character data has quotes (ie 'Brett Kaiser')

    And some of it doesn't...is this true?

    Does the data normally get loaded with quotes?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16
    Do you know how to script that in Enterprise Manager?
    I have a DTS package in Enterprise Manager that calls a stored procedure that creates the table:

    Code:
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    
    
    ALTER PROCEDURE sp_PolDump_113004_test
    
    AS
    
    IF OBJECT_ID('MEC.mcopper.Pol_Dump113004_test') IS NOT NULL 
    DROP TABLE MEC.mcopper.Pol_Dump113004_test
    
    CREATE TABLE [Pol_Dump113004_test]
    (	
    	[Product_Type] varchar (12) NULL, 
    	[Plan_Number] varchar (12) NULL,
    	[Policy_Number] varchar (12) NULL, 
    	[ID_Number] varchar (12) NULL, 
    	[Agent] varchar (12) NULL, 
    	[Group_Number] varchar (12) NULL, 
    	[Issue_State] varchar (12) NULL, 
    	[Issue_Date] varchar (12) NULL, 
    	[Mode] varchar (12) NULL, 
    	[Specified_Amount] varchar (12) NULL, 
    	[Benefit] varchar (12) NULL, 
    	[Base_Premium] varchar (12) NULL, 
    	[Rider_Premium] varchar (12) NULL, 
    	[Contract_Value] varchar (12) NULL, 
    	[Loan_Balance] varchar (12) NULL, 
    	[Surrender_Charge] varchar (12) NULL, 
    	[Surrender_Value] varchar (12) NULL, 
    	[PUA_Dividends] varchar (12) NULL, 
    	[OYT_Dividends] varchar (12) NULL, 
    	[Dividend_Cash_Balance] varchar (12) NULL, 
    	[Dividend_Cash_Value] varchar (12) NULL, 
    	[Status_Date] varchar (12) NULL, 
    	[Status_Code] varchar (12) NULL, 
    	[Paid_To_Date] varchar (12) NULL, 
    	[Date_Last_Bill] varchar (12) NULL, 
    	[APL_Option] varchar (12) NULL, 
    	[NFO_Option] varchar (12) NULL, 
    	[DIVD_Option] varchar (12) NULL, 
    	[District] varchar (12) NULL, 
    	[Next_Premium_Due_Date] varchar (12) NULL, 
    	[Next_Premium_Due_Amount] varchar (12) NULL, 
    	[Scheduled_Premiums] varchar (12) NULL, 
    	[Premiums_Paid] varchar (12) NULL, 
    	[Insured_IssueAge] varchar (12) NULL, 
    	[Insured_Sex] varchar (12) NULL, 
    	[Insured_Class] varchar (12) NULL, 
    	[Insured_Birthdate] varchar (12) NULL,
    	[2nd_Insured_IssueAge] varchar (12) NULL,
    	[2nd_Insured_Sex] varchar (12) NULL,
    	[2nd_Insured_Class] varchar (12) NULL,
    	[2nd_Insured_Birthdate] varchar (12) NULL,
    	[Riders_Count] varchar (12) NULL,
    	[Rider1_Type] varchar (12) NULL,
    	[Rider1_Code] varchar (12) NULL,
    	[Rider1_IssDate] varchar (12) NULL,
    	[Rider1_ExpDate] varchar (12) NULL,
    	[Rider1_Benefit] varchar (12) NULL,
    	[Rider1_Premium] varchar (12) NULL,
    	[Rider1_Period] varchar (12) NULL,
    	[Rider1_Status] varchar (12) NULL,
    	[Rider1_TermDate] varchar (12) NULL,
    	[Rider2_Type] varchar (12) NULL,
    	[Rider2_Code] varchar (12) NULL,
    	[Rider2_IssDate] varchar (12) NULL,
    	[Rider2_ExpDate] varchar (12) NULL,
    	[Rider2_Benefit] varchar (12) NULL,
    	[Rider2_Premium] varchar (12) NULL,
    	[Rider2_Period] varchar (12) NULL,
    	[Rider2_Status] varchar (12) NULL,
    	[Rider2_TermDate] varchar (12) NULL,
    	[Rider3_Type] varchar (12) NULL,
    	[Rider3_Code] varchar (12) NULL,
    	[Rider3_IssDate] varchar (12) NULL,
    	[Rider3_ExpDate] varchar (12) NULL,
    	[Rider3_Benefit] varchar (12) NULL,
    	[Rider3_Premium] varchar (12) NULL,
    	[Rider3_Period] varchar (12) NULL,
    	[Rider3_Status] varchar (12) NULL,
    	[Rider3_TermDate] varchar (12) NULL,
    	[Rider4_Type] varchar (12) NULL,
    	[Rider4_Code] varchar (12) NULL,
    	[Rider4_IssDate] varchar (12) NULL,
    	[Rider4_ExpDate] varchar (12) NULL,
    	[Rider4_Benefit] varchar (12) NULL,
    	[Rider4_Premium] varchar (12) NULL,
    	[Rider4_Period] varchar (12) NULL,
    	[Rider4_Status] varchar (12) NULL,
    	[Rider4_TermDate] varchar (12) NULL,
    	[Rider5_Type] varchar (12) NULL, 
    	[Rider5_Code] varchar (12) NULL, 
    	[Rider5_IssDate] varchar (12) NULL, 
    	[Rider5_ExpDate] varchar (12) NULL, 
    	[Rider5_Benefit] varchar (12) NULL, 
    	[Rider5_Premium] varchar (12) NULL, 
    	[Rider5_Period] varchar (12) NULL, 
    	[Rider5_Status] varchar (12) NULL, 
    	[Rider5_TermDate] varchar (12) NULL, 
    	[Rider6_Type] varchar (12) NULL, 
    	[Rider6_Code] varchar (12) NULL, 
    	[Rider6_IssDate] varchar (12) NULL, 
    	[Rider6_ExpDate] varchar (12) NULL, 
    	[Rider6_Benefit] varchar (12) NULL, 
    	[Rider6_Premium] varchar (12) NULL, 
    	[Rider6_Period] varchar (12) NULL, 
    	[Rider6_Status] varchar (12) NULL, 
    	[Rider6_TermDate] varchar (12) NULL, 
    	[Rider7_Type] varchar (12) NULL, 
    	[Rider7_Code] varchar (12) NULL, 
    	[Rider7_IssDate] varchar (12) NULL, 
    	[Rider7_ExpDate] varchar (12) NULL, 
    	[Rider7_Benefit] varchar (12) NULL, 
    	[Rider7_Premium] varchar (12) NULL, 
    	[Rider7_Period] varchar (12) NULL, 
    	[Rider7_Status] varchar (12) NULL, 
    	[Rider7_TermDate] varchar (12) NULL, 
    	[Rider8_Type] varchar (12) NULL, 
    	[Rider8_Code] varchar (12) NULL, 
    	[Rider8_IssDate] varchar (12) NULL, 
    	[Rider8_ExpDate] varchar (12) NULL, 
    	[Rider8_Benefit] varchar (12) NULL, 
    	[Rider8_Premium] varchar (12) NULL, 
    	[Rider8_Period] varchar (12) NULL, 
    	[Rider8_Status] varchar (12) NULL, 
    	[Rider8_TermDate] varchar (12) NULL, 
    	[Rider9_Type] varchar (12) NULL, 
    	[Rider9_Code] varchar (12) NULL, 
    	[Rider9_IssDate] varchar (12) NULL, 
    	[Rider9_ExpDate] varchar (12) NULL, 
    	[Rider9_Benefit] varchar (12) NULL, 
    	[Rider9_Premium] varchar (12) NULL, 
    	[Rider9_Period] varchar (12) NULL, 
    	[Rider9_Status] varchar (12) NULL, 
    	[Rider9_TermDate] varchar (12) NULL, 
    	[Rider10_Type] varchar (12) NULL, 
    	[Rider10_Code] varchar (12) NULL, 
    	[Rider10_IssDate] varchar (12) NULL, 
    	[Rider10_ExpDate] varchar (12) NULL, 
    	[Rider10_Benefit] varchar (12) NULL, 
    	[Rider10_Premium] varchar (12) NULL, 
    	[Rider10_Period] varchar (12) NULL, 
    	[Rider10_Status] varchar (12) NULL, 
    	[Rider10_TermDate] varchar (12) NULL, 
    	[Rider11_Type] varchar (12) NULL, 
    	[Rider11_Code] varchar (12) NULL, 
    	[Rider11_IssDate] varchar (12) NULL, 
    	[Rider11_ExpDate] varchar (12) NULL, 
    	[Rider11_Benefit] varchar (12) NULL, 
    	[Rider11_Premium] varchar (12) NULL, 
    	[Rider11_Period] varchar (12) NULL, 
    	[Rider11_Status] varchar (12) NULL, 
    	[Rider11_TermDate] varchar (12) NULL, 
    	[Rider12_Type] varchar (12) NULL, 
    	[Rider12_Code] varchar (12) NULL, 
    	[Rider12_IssDate] varchar (12) NULL, 
    	[Rider12_ExpDate] varchar (12) NULL, 
    	[Rider12_Benefit] varchar (12) NULL, 
    	[Rider12_Premium] varchar (12) NULL, 
    	[Rider12_Period] varchar (12) NULL, 
    	[Rider12_Status] varchar (12) NULL, 
    	[Rider12_TermDate] varchar (12) NULL, 
    	[Rider13_Type] varchar (12) NULL, 
    	[Rider13_Code] varchar (12) NULL, 
    	[Rider13_IssDate] varchar (12) NULL, 
    	[Rider13_ExpDate] varchar (12) NULL, 
    	[Rider13_Benefit] varchar (12) NULL, 
    	[Rider13_Premium] varchar (12) NULL, 
    	[Rider13_Period] varchar (12) NULL, 
    	[Rider13_Status] varchar (12) NULL, 
    	[Rider13_TermDate] varchar (12) NULL, 
    	[Rider14_Type] varchar (12) NULL, 
    	[Rider14_Code] varchar (12) NULL, 
    	[Rider14_IssDate] varchar (12) NULL, 
    	[Rider14_ExpDate] varchar (12) NULL, 
    	[Rider14_Benefit] varchar (12) NULL, 
    	[Rider14_Premium] varchar (12) NULL, 
    	[Rider14_Period] varchar (12) NULL, 
    	[Rider14_Status] varchar (12) NULL, 
    	[Rider14_TermDate] varchar (12) NULL, 
    	[Rider15_Type] varchar (12) NULL, 
    	[Rider15_Code] varchar (12) NULL, 
    	[Rider15_IssDate] varchar (12) NULL, 
    	[Rider15_ExpDate] varchar (12) NULL, 
    	[Rider15_Benefit] varchar (12) NULL, 
    	[Rider15_Premium] varchar (12) NULL, 
    	[Rider15_Period] varchar (12) NULL, 
    	[Rider15_Status] varchar (12) NULL, 
    	[Rider15_TermDate] varchar (12) NULL, 
    	[Rider16_Type] varchar (12) NULL, 
    	[Rider16_Code] varchar (12) NULL, 
    	[Rider16_IssDate] varchar (12) NULL, 
    	[Rider16_ExpDate] varchar (12) NULL, 
    	[Rider16_Benefit] varchar (12) NULL, 
    	[Rider16_Premium] varchar (12) NULL, 
    	[Rider16_Period] varchar (12) NULL, 
    	[Rider16_Status] varchar (12) NULL, 
    	[Rider16_TermDate] varchar (12) NULL, 
    	[Next_Rider_Premium_Date] varchar (12) NULL, 
    	[Next_Rider_Premium_Amount] varchar (12) NULL, 
    	[Rider_Premiums_Scheduled] varchar (12) NULL, 
    	[Rider_Premiums_Paid] varchar (12) NULL, 
    	[Expiry_Date] varchar (12) NULL,
    	[Insured_Last_Name] varchar (288) NULL, 
    	[Insured_First_Name] varchar (288) NULL, 
    	[Insured_SSN] varchar (288) NULL, 
    	[Billing_Option] varchar (288) NULL, 
    	[Bill_Address1] varchar (288) NULL, 
    	[Bill_Address2] varchar (288) NULL, 
    	[Bill_City] varchar (288) NULL, 
    	[Bill_State] varchar (288) NULL, 
    	[Bill_Zip] varchar (288) NULL, 
    	[Bill_Phone] varchar (288) NULL
    )
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO

  14. #14
    Join Date
    Oct 2004
    Location
    Phx, Arizona
    Posts
    16
    Also I noticed that some of the character data has quotes (ie 'Brett Kaiser')

    And some of it doesn't...is this true?

    Does the data normally get loaded with quotes?
    Yes, it's true. That was the 'developer's' version of a quick fix. To text delimit the add'l columns that possibly had bad data. He originally had double quotes, but I changed them to single quotes.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, at a certain point (now would be good) you and your boss have to ask yourselves this question:
    "How good is the software this guy is developing going to be if he can't perform such a simple task as exporting a standard text file?"

    I, (and probably anybody else on this forum with any experience) can tell you that this turd is just going to stink more and more as the months wear on. It's going to cost you a helluva lot in maintenance costs if it turns out to be crappy software.

    At least, that is what MY crystal ball is telling me...

    As for your data, if you scrap the first line (headers) the rest is pure fixed-width with some superfluous commas tossed in. BCP should be able to handle it, but load it into a staging table and then strip off the trailing commas when you move it to your production table.

    Want another quick and dirty solution? MS Access's wizard will import fixed width with ease, (you can even set it up to ignore the comma delimiters). For best results use an Access ADP project linked to your SQL Server, or just use an Access MDB database and link your server tables to it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •