help needed with the import - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help needed with the import

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-12-18 : 02:01:39

þBegDocþþProdBegDocþþProdEndDocþþFILE_PATHþþFULL_TEXTþ

the above are the headings (columns) delimited and þ as text qualifier
and the last column full_text is very very lengthy the no of records are 86 only
can any help me import this file in sqlserver 2005, the error I am getting is

error: the column data for column FULL_TEXT overflowed the disk I/O buffer

Even if I ignore this column in DTS and SSIS while importing then also the above err is seen

one record data for the column full_text is as below, there are more lengthy
records for this column this is just an example.

kindly help.

þ®These colors denote the rate status detailed in the Contract Summaries and DRG Case Rates tabs. ®New contract and/or new rates since previous release of grid Contract was terminated ®Active Non-active ®INPATIENT SERVICES 1199 NATIONAL BENEFIT FUND 1199 NATIONAL BENEFIT FUND 1199 NATIONAL BENEFIT FUND 1199 NATIONAL BENEFIT FUND AETNA COMMERCIAL PRODUCTS AETNA HMO & PPO AETNA HMO & PPO AETNA HMO & PPO AETNA HMO & PPO AETNA GOVERNMENT PROGRAMS (MEDICARE) AETNA MEDICARE AETNA MEDICARE AETNA MEDICARE AETNA MEDICARE AFFINITY MEDICAID, CHP, FHP (fka GENESIS HEALTHPLAN) AFFINITY MEDICAID, CHP, FHP (fka GENESIS HEALTHPLAN) HEALTH PLAN (SEE AMERIGROUP COMMUNITY) CENTECARE ST: 92506-92508, 97001-97546.®Applicable CPT codes for Radiology: 0144T, 0145T, 0146T, 0147T, 0148T, 0149T, 0150T, 0151T, 61793, and 70010-79999.®Applicable CPT codes for Radiology: 0144T, 0145T, 0146T, 0147T, 0148T, 0149T, 0150T, 0151T, 61793, and 70010-79999.®Applicable CPT codes for Radiology: 0144T, 0145T, 0146T, 0147T, 0148T, 0149T, 0150T, 0151T, 61793, and 70010-79999.®Applicable CPT codes for Radiology: 0144T, 0145T, 0146T, 0147T, 0148T, 0149T, 0150T, 0151T, 61793, and 70010-79999.®Applicable CPT codes for Radiology: 0144T, 0145T, 0146T, 0147T, 0148T, 0149T, 0150T, 0151T, 61793, and 70010-79999.®The following codes apply for Chemotherapy Administration reimbursement: CPT 51720, 96401-96542. HCPC Q0083-Q0084. Revenue 331, 332, 335.®Applicable drugs are chemotherapy drugs and other high cost drugs billed with the revenue codes 250 and 636.®ECT is paid $505 once per unique code per date. Use CPT 90870 with rev 901.®®Outpatient mental health services will be reimbursed 100% Medicare fee schedule. CPT 90801-9802, 90804-90815, 90845-90857, 90862-90899, 90901-90911, 96101-96116, 99058, 99212, 99221-99223, 99231-99239, 99241-99245, 99281-99285, 99307-99310.®There is an exception when the DRGs are 650-652. Please see the DRG Case Rates tab for details.®®The total case rate payment may be allocated at 100% to the mother's claim and 0% to the newborn's claim OR 80% to the mother's claim and 20% to the newborn's claim.®All MRI/MRA codes in CPT code range 70010-79999 and HCPC code range C8900-C9434.®All CAT scan codes in code range 70010-79999.®There is an exception when the DRGs are 650-652. Please see the DRG Case Rates tab for details.®®The total case rate payment may be allocated at 100% to the mother's claim and 0% to the newborn's claim OR 80% to the mother's claim and 20% to the newborn's claim.®There is an exception when the DRGs are 650-652. Please see the DRG Case Rates tab for details.®®The total case rate payment may be allocated at 100% to the mother's claim and 0% to the newborn's claim OR 80% to the mother's claim and 20% to the newborn's claim.®®Applicable CPT codes for Radiology: 0144T, 0145T, 0146T, 0147T, 0148T, 0149T, 0150T, 0151T, 61793, and 70010-79999.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®100% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program with an applicable technical/professional split in accordance with CMS guidelines.®85% of the maximum allowable Medicare fee schedule established by CMS for payments under the Medicare program.®Inpatient Per Diem exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274) and blood processing and administration (when billed with rev codes 390, 391, or 399).®Inpatient Per Diem exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274) and blood processing and administration (when billed with rev codes 390, 391, or 399).®Inpatient Per Diem exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274) and blood processing and administration (when billed with rev codes 390, 391, or 399).®Inpatient Per Diem exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274) and blood processing and administration (when billed with rev codes 390, 391, or 399).®Inpatient Per Diem exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274) and blood processing and administration (when billed with rev codes 390, 391, or 399).®Inpatient Per Diem exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274) and blood processing and administration (when billed with rev codes 390, 391, or 399).®Ambsurg Groups outpatient rate exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274), pacemakers (when billed with rev code 275), and blood processing and administration (when billed with rev codes 390, 391, or 399).®Ambsurg Groups outpatient rate exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274), pacemakers (when billed with rev code 275), and blood processing and administration (when billed with rev codes 390, 391, or 399).®Ambsurg Groups outpatient rate exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274), pacemakers (when billed with rev code 275), and blood processing and administration (when billed with rev codes 390, 391, or 399).®Other Outpatient rate exceptions: chemotherapy drugs when billed with rev code 636 in addition to rev codes 331, 332, or 335.®Other Outpatient rate exceptions: chemotherapy drugs when billed with rev code 636 in addition to rev codes 331, 332, or 335.®Ambsurg Groups outpatient rate exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274), pacemakers (when billed with rev code 275), and blood processing and administration (when billed with rev codes 390, 391, or 399).®Ambsurg Groups outpatient rate exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274), pacemakers (when billed with rev code 275), and blood processing and administration (when billed with rev codes 390, 391, or 399).®Ambsurg Groups outpatient rate exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274), pacemakers (when billed with rev code 275), and blood processing and administration (when billed with rev codes 390, 391, or 399).®Inpatient Per Diem exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274) and blood processing and administration (when billed with : other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274), pacemakers code 274), pacemakers (when billed with rev code 275), and blood processing and administration (when billed with rev codes 390, 391, or 399).®Inpatient Per Diem exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274) and blood processing and administration (when billed with rev codes 390, 391, or 399).®Inpatient Per Diem exceptions include: other implants (when billed with rev code 278), prosthetic and orthotic devices (when billed with rev code 274) and blood processing and administration (®260 ®270 ®280 $13,364 (19 LOS TRIM) ®281 $9,355 (16 LOS TRIM) ®285 $28,000.00 57 $29,473 $30,499 $29,611 $28,748 $27,911 $27,098 $26,599 $25,332 $24,126 $22,977 $46,780 (37 LOS TRIM) $44,578 (37 LOS TRIM) ®881 $80,258.00 57 ®884 $30,402.00 17 .þ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 02:13:20

how are you trying to import this? i think best way will be using BULK INSERT or OPENROWSET BULK options

http://msdn.microsoft.com/en-us/library/ms187042.aspx

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-12-18 : 02:24:41

quote:
Originally posted by visakh16

how are you trying to import this? i think best way will be using BULK INSERT or OPENROWSET BULK options

http://msdn.microsoft.com/en-us/library/ms187042.aspx

Thanks Visakh,

I have used the below code

BULK INSERT [BH-JH_Production_AEO_export]
FROM 'C:\BH-JH_Production_AEO_export.dat'
WITH (
DATAFILETYPE = 'CHAR',
FIELDTERMINATOR = '',
ROWTERMINATOR = '\n',
MAXERRORS = 0,
TABLOCK
)
but I am getting all column values as below
the '¦' ( field terminator is coming along with the data
¦Doss_Mounir000001188¦

Thanks,
aakcse

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 02:40:25

[code]
BULK INSERT [BH-JH_Production_AEO_export]
FROM 'C:\Documents and Settings\asifkhan\Desktop\Projects\Brown Stone\BH-JH_Production_AEO_export.dat'
WITH (
DATAFILETYPE = 'CHAR',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
MAXERRORS = 0,
TABLOCK
)
[/code]

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-12-18 : 02:46:58

Vishaka,

this is not working its giving the below error.

Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 02:50:15

whats your field terminator?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-12-18 : 02:53:56

quote:
Originally posted by visakh16

whats your field terminator?

field terminator is
and text qualifier is þ

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-12-18 : 02:56:16

quote:
Originally posted by visakh16

whats your field terminator?

BULK INSERT [BH-JH_Production_AEO_export]
FROM 'C:\BH-JH_Production_AEO_export.dat'
WITH (
DATAFILETYPE = 'CHAR',
FIELDTERMINATOR = 'þþ',
ROWTERMINATOR = '\n',
MAXERRORS = 0,
TABLOCK
)

this one is working but first column data I am getting as below
Doss_Mounir000001188
rest of the column data is fine only for all the records in the
first column is appended by

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 02:58:56

appended by what?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 03:00:52

consider specifying format file if you want to customize the separators

http://msdn.microsoft.com/en-us/library/ms178129.aspx

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-12-18 : 04:18:48