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?Look up BCP and Format file in BOL.|||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.|||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?|||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
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?|||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|||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.|||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... :o
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.|||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|||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
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. :)|||I need the DDL as well
CREATE TABLE tablename(Col1 int, ect
Do you know how to script that in Enterprise Manager?|||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?|||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:
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|||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.|||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.|||EDIT: I attached the sample file to load from
After you build it you might need to ISDATE and ISNUMERIC to filter out garbage and or REPLACE to get rid of quotes...
Where in the world are you doing this, and take 5 advil
USE Northwind
GO
master..xp_cmdshell ' DIR D:\Tax\m*.*'
CREATE TABLE myTable99(Col1 varchar(8000))
GO
master..xp_cmdshell 'bcp Northwind.dbo.myTable99 in D:\Tax\melaticTestData.txt -SNJROS1D151\NJROS1D151DEV -Usa -P -c'
GO
SELECT LEN(Col1) FROM myTable99
INSERT INTO myTable99(Col1)
SELECT REPLICATE('1234567890',800) UNION ALL
SELECT REPLICATE( SPACE(9)+'1'+SPACE(9)+'2'+SPACE(9)+'3'+SPACE(9)+'4 '+SPACE(9)+'5'
+ SPACE(9)+'6'+SPACE(9)+'7'+SPACE(9)+'8'+SPACE(9)+'9 '+SPACE(9)+'0',80) UNION ALL
SELECT REPLICATE( SPACE(99)+'1'+SPACE(99)+'2'+SPACE(99)+'3'+SPACE(99 )+'4'+SPACE(99)+'5'
+ SPACE(99)+'6'+SPACE(99)+'7'+SPACE(99)+'8'+SPACE(99 )+'9'+SPACE(99)+'0',8) UNION ALL
SELECT SPACE(999)+'1'+SPACE(999)+'2'+SPACE(999)+'3'+SPACE (999)+'4'+SPACE(999)+'5'
+ SPACE(999)+'6'+SPACE(999)+'7'+SPACE(999)+'8'
SELECT * FROM myTable99
-- Then you could build a select statement
SELECT SUBSTRING(Col1,0001,001) AS [Product Type]
, SUBSTRING(Col1,0003,006) AS [Plan]
, SUBSTRING(Col1,0010,010) AS [Policay Number]
, SUBSTRING(Col1,0021,010) AS [ID]
-- ect
FROM myTable99
WHERE SUBSTRING(Col1,1,1) <> '1'
GO
DROP TABLE myTable99
GO
--Use The Column list (Hopefully the match the names of the table
-- Cut and paste the select for reference doen above after to help build the select|||Sorry, i was out sick. SICK I TELL YOU!!! :)
I'm happy now though, because I got that developer to start using vertical bars as column delimiters instead of commas. I persuaded him it was just better, and he happily obliged. Aaah, the power of communication.
So, thanks blindman and brett kaiser for you help. I adore both of you to pieces.|||Great! But if you want to appear professional while talking with your developer, refer to them as "pipes", not "vertical bars". The output format is known as "pipe-delimited".|||Great! But if you want to appear professional while talking with your developer, refer to them as "pipes", not "vertical bars". The output format is known as "pipe-delimited".
A pipe-delimited developer ... I know the answer to this one!!!
Kris Kringle;)|||I prefer tab delimited|||Funny, you struck me as someone who would prefer Diet Coke delimited over Tab delimited...|||Funny, you struck me as someone who would prefer Diet Coke delimited over Tab delimited...Heck, I'll take Tab every time! It is one of the few Coca-Cola products that I like!
-PatP|||If it's not ta kill ya, then it's Poland spring....
I actually pass it on the way to and from Sunday River|||Great! But if you want to appear professional while talking with your developer, refer to them as "pipes", not "vertical bars". The output format is known as "pipe-delimited".
I called it pipe delimited, and he calls it vertical bar delimited. He's a C++ developer, I don't know if that matters. Allz I know is, I'm a happy gal.
Again, thanks so much for your help. I would be a stressball if it weren't for the comfort of my expert friends on dbforums.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment