Again Trying to Bulk Insert | delimited txt file

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 2008 Forums

Transact-SQL (2008)

Again Trying to Bulk Insert | delimited txt file

Author

Topic

bconner
Starting Member

48 Posts

Posted - 2010-05-07 : 11:55:46

I am trying to do a Bulk Insert on file with "|" Delimiter but I am running
into errors. Below is the code I am using as well as a sample of the text
file I am trying to insert.

   
BULK INSERT TEST.dbo.Transactions_2010
  FROM 'C:\Transaction Files\GRP 8 TRANS MAR 10.txt'
  WITH 
     (
       FIRSTROW = 2,
       FIELDTERMINATOR = '|', 
       ROWTERMINATOR = '\n'
        )

Sample Data:

DIVISION|PATIENT ID|INVOICE|PATIENT NAME|FSC|TXN CODE|LINE ITEM|TXN
DESCRIPTION|CPT|QTY|POST
DATE|CHG|PAY|ADJ|CREDIT|DEBIT|DEDAMT|COINS|APPRAMT|REJ|PRIFSC|SECFSC|LOC|SVC
DATE|REF PHY|FSC#|MOD1|MOD2|MOD3|REF LOC|DX
CODE|ACCESSION|MRN#|GROUP#|PROVIDER|CHARGE TYPE
CHAN|1268521|13479186|YOUNGER,CYNTHIA|MEDICAID NORTH
CAROLINA|99||CHARGES|87491|1|03/04/2010| 84.00 | 0.00 | 0.00 | 0.00 | 0.00 |
0.00 | 0.00 | 0.00 ||1109|1|CHANIL|12/17/2009|LIVINGSTON MD,
ELIZABETH|1109||||NORTHERN OUTREACH|V73.88|77007956|2535149|8|SISCO MD,
KENNETH L|AP
CHAN|1268521|13479186|YOUNGER,CYNTHIA|MEDICAID NORTH
CAROLINA|99||CHARGES|87591|1|03/04/2010| 84.00 | 0.00 | 0.00 | 0.00 | 0.00 |
0.00 | 0.00 | 0.00 ||1109|1|CHANIL|12/17/2009|LIVINGSTON MD,
ELIZABETH|1109||||NORTHERN OUTREACH|V73.88|77007956|2535149|8|SISCO MD,
KENNETH L|AP
CHAN|1268523|13440268|BULLOCK,EARLINE|MEDICAID NORTH
CAROLINA|99||CHARGES|87491|1|03/02/2010| 84.00 | 0.00 | 0.00 | 0.00 | 0.00 |
0.00 | 0.00 | 0.00 ||1109|1|CHANIL|12/17/2009|LIVINGSTON MD,
ELIZABETH|1109||||NORTHERN OUTREACH|V73.88|77007952|2535153|8|SISCO MD,
KENNETH L|AP
CHAN|1268523|13440268|BULLOCK,EARLINE|MEDICAID NORTH
CAROLINA|99||CHARGES|87591|1|03/02/2010| 84.00 | 0.00 | 0.00 | 0.00 | 0.00 |
0.00 | 0.00 | 0.00 ||1109|1|CHANIL|12/17/2009|LIVINGSTON MD,
ELIZABETH|1109||||NORTHERN OUTREACH|V73.88|77007952|2535153|8|SISCO MD,
KENNETH L|AP
CHAN|1392665|13113940|CONAWAY,PAULINE K|MEDICARE INDIANA|1028|101.
0003|MEDICARE INDIANA PAYMENT|88368||03/04/2010| 0.00 | 391.86 | 251.54 | 0.
00 | 0.00 | 0.00 | 97.96 | 489.82 |MA18|1028|2616|CHANIL|12/04/2009|MOSSLER
MD,JEFFREY A|1028||||NAAB RD SURG CTR|174.9|IS0985835|2783182|8|SISCO MD,
KENNETH L|AP
CHAN|1439380|13113949|PATTERSON,JAN L|UNITED HEALTHCARE|1268|101.0003|UNITED
HEALTHCARE PAYMENT|88368||03/17/2010| 0.00 | 218.62 | 522.74 | 0.00 | 0.00 |
0.00 | 0.00 | 218.62 ||1268|1|CHANIL|01/06/2010|RICHERT MD,CHARLES
L|1268||||CENTER FOR WOMENS HLTH INDIANA|174.9|IS10846|2876565|8|SISCO MD,
KENNETH L|AP
CHAN|1740458|10451062|JONES,MARLENE L|MEDICAID INDIANA|1167|101.0014|MEDICAID
INDIANA PAYMENT|88368||03/16/2010| 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
0.00 | 0.00 |29|1167|1|CHANIL|02/05/2009|AMIN MD,CHIRAG JITENDRA|1167||||ST
VINCENT HOSPITAL 86TH|198.5|IC09791|3475593|8|SISCO MD,KENNETH L|AP
CHAN|1740458|10451062|JONES,MARLENE L|MEDICAID INDIANA|1167|101.0017|MEDICAID
INDIANA PAYMENT|88368||03/30/2010| 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
0.00 | 0.00 |16|1167|1|CHANIL|02/05/2009|AMIN MD,CHIRAG JITENDRA|1167||||ST
VINCENT HOSPITAL 86TH|198.5|IC09791|3475593|8|SISCO MD,KENNETH L|AP
CHAN|1953948|13113956|DONAHUE WYKOFF,ANGELA K|MEDICAID INDIANA
SECONDARY|3167|101.0007|MEDICAID INDIANA SECONDARY PAYMENT|88368||03/16/2010|
0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00

|23|1028|3167|CHANIL|12/07/2009|CUDAHY MD,TERENCE J|3167||||MIDWEST BREAST
CENTER|174.8|IS0986213|3901742|8|SISCO MD,KENNETH L|AP
CHAN|1953948|13113956|DONAHUE WYKOFF,ANGELA K|MEDICAID INDIANA
SECONDARY|3167|102.0007|MEDICAID INDIANA SECONDARY PAYMENT|88368||03/16/2010|
0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00

|23|1028|3167|CHANIL|12/07/2009|CUDAHY MD,TERENCE J|3167||||MIDWEST BREAST
CENTER|174.8|IS0986213|3901742|8|SISCO MD,KENNETH L|AP
CHAN|1953948|13113956|DONAHUE WYKOFF,ANGELA K|MEDICARE INDIANA|1028|101.
0004|MEDICARE INDIANA PAYMENT|88368||03/04/2010| 0.00 | 391.86 | 251.54 | 0.
00 | 0.00 | 0.00 | 97.96 | 489.82 ||1028|3167|CHANIL|12/07/2009|CUDAHY MD,
TERENCE J|1028||||MIDWEST BREAST CENTER|174.8|IS0986213|3901742|8|SISCO MD,
KENNETH L|AP
CHAN|1953948|13113956|DONAHUE WYKOFF,ANGELA K|MEDICARE INDIANA|1028|102.
0004|MEDICARE INDIANA PAYMENT|88368||03/04/2010| 0.00 | 391.86 | 251.54 | 0.
00 | 0.00 | 0.00 | 97.96 | 489.82 ||1028|3167|CHANIL|12/07/2009|CUDAHY MD,
TERENCE J|1028||||MIDWEST BREAST CENTER|174.8|IS0986213|3901742|8|SISCO MD,
KENNETH L|AP
CHAN|1976249|13113959|MARTIN,JANE A|MEDICARE INDIANA|1028|101.0003|MEDICARE
INDIANA PAYMENT|88368||03/04/2010| 0.00 | 391.86 | 251.54 | 0.00 | 0.00 | 0.
00 | 97.96 | 489.82 |MA18|1028|3099|CHANIL|12/03/2009|MOSSLER MD,JEFFREY
A|1028||||MIDWEST BREAST CENTER|174.9|IS0985348|3946271|8|SISCO MD,KENNETH
L|AP
CHAN|2011174|11332263|SCHULTZ,WILMA|MEDICAID INDIANA APPEAL|5248||MEDICAID
CONTRACTUAL|83891||03/08/2010| 0.00 | 0.00 | 0.00 | 192.12 | 0.00 | 0.00 | 0.
00 | 0.00 ||1167|1|CHANIL||MILTON MD,DANIEL T|5167||||HEMATOLOGY ONCOLOGY OF
INDIANA|205.10|IH093886|4016045|8|SISCO MD,KENNETH L|AP
CHAN|2044499|13136298|HAMMOND,CHERYL R|UNITED HEALTHCARE|1268|101.0003|UNITED
HEALTHCARE PAYMENT|88368||03/24/2010| 0.00 | 325.68 | 300.62 | 0.00 | 0.00 |
0.00 | 0.00 | 325.68 ||1268|1|CHANIL|12/01/2009|MOSSLER MD,JEFFREY
A|1268||||ST VINCENT WOMEN'S HOSPITAL|174.9|IS0984652|4082667|8|SISCO MD,
KENNETH L|AP
CHAN|2456972|13113973|KING,DAVID|UNITED HEALTHCARE|1268|401.0007|UNITED
HEALTHCARE PAYMENT|83891||03/24/2010| 0.00 | 5.31 | 2.19 | 0.00 | 0.00 | 0.00

| 0.00 | 5.31 ||1268|1|CHANIL|12/18/2009|LIU MD,LANTING|1268||||AHN-NAAB ROAD
HEM/ONC|289.9|IH095209|4906563|8|SISCO MD,KENNETH L|AP
CHAN|2456972|13113973|KING,DAVID|UNITED HEALTHCARE|1268|402.0007|UNITED
HEALTHCARE PAYMENT|83900||03/24/2010| 0.00 | 24.20 | 69.80 | 0.00 | 0.00 | 0.
00 | 0.00 | 24.20 ||1268|1|CHANIL|12/18/2009|LIU MD,LANTING|1268||||AHN-NAAB
ROAD HEM/ONC|289.9|IH095209|4906563|8|SISCO MD,KENNETH L|AP
CHAN|2456972|13113973|KING,DAVID|UNITED HEALTHCARE|1268|403.0007|UNITED
HEALTHCARE PAYMENT|83912||03/24/2010| 0.00 | 7.12 | 0.00 | 0.00 | 0.00 | 0.00

| 0.00 | 7.12 ||1268|1|CHANIL|12/18/2009|LIU MD,LANTING|1268||||AHN-NAAB ROAD
HEM/ONC|289.9|IH095209|4906563|8|SISCO MD,KENNETH L|AP
CHAN|2456972|13113973|KING,DAVID|UNITED HEALTHCARE|1268|404.0007|UNITED
HEALTHCARE PAYMENT|83902||03/24/2010| 0.00 | 61.00 | 0.00 | 0.00 | 0.00 | 0.
00 | 0.00 | 61.00 ||1268|1|CHANIL|12/18/2009|LIU MD,LANTING|1268||||AHN-NAAB
ROAD HEM/ONC|289.9|IH095209|4906563|8|SISCO MD,KENNETH L|AP
SJCQ|7625438|13631099|MARX,JAMES|MEDICARE
TEXAS|99||CHARGES|88291|1|03/16/2010| 53.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.
00 | 0.00 | 0.00 ||1008|1|SJCQIL|02/05/2010|JUAREZ MD,MARIA I|1008||||THE
CANCER INSTITUTE OF DALLAS|203.00|NT10000351TX|12887866|8|REITZ MD,RICHARD
E|AP
SJCQ|7625440|13631102|SINCLER,MARGARET|MEDICARE
TEXAS|99||CHARGES|88291|1|03/16/2010| 53.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.
00 | 0.00 | 0.00 ||1008|3902|SJCQIL|02/08/2010|KAPADIA MD,ZEHRA|1008||||TEXAS
ONCOLOGY-WILLOWBROOK|238.75|NT10000377TX|12887868|8|REITZ MD,RICHARD E|AP
SJCQ|7647365|13631271|COOLEY,JAMES|MEDICARE
TEXAS|99||CHARGES|88291|1|03/16/2010| 53.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.
00 | 0.00 | 0.00 ||1008|3108|SJCQIL|02/05/2010|KAPADIA MD,ZEHRA|1008||||TEXAS
ONCOLOGY-WILLOWBROOK|288.02|NT10000362TX|12909794|8|REITZ MD,RICHARD E|AP
SJCQ|7647366|13631275|DENNY,NINA F|MEDICARE
TEXAS|99||CHARGES|88291|1|03/16/2010| 53.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.
00 | 0.00 | 0.00 ||1008|2616|SJCQIL|02/02/2010|REDDY MD,PRAVEEN
J|1008||||TEXOMA CANCER CENTER|238.72|NT10000364TX|12909795|8|REITZ MD,
RICHARD E|AP

Included in the Sample data above is the last record in the file, I can't
find anything wrong with the End of the data that would cause the error below.

The Error Message I am getting:

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".

Brian

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-07 : 16:21:47

I think you get the error because of blanks inbetween the data. At least it has been so for me in the past.