Bad experiences with BULK INSERT? 1

[pandatime]

Hi,

I have been trying to use BULK INSERT to load an Oracle spool file.

The file has in addition to just plain bad rows, rows that look like:

1, good data <tab> good data <carriage return>
2, bad data <carriage return>
3, <carriage return> -- i.e. blank row
4, good data <tab> good data <carriage return>
5, etc (mostly good data, interspersed with above scenario)

Problem is, BULK INSERT is rejecting good rows along with the bad rows, and I suspect it's because the data is not consistent, as shown above.

Is this something that BULK INSERT is equipped to deal with even?

Thanks

 

[gmmastros]

Based on your sample data, I guess you are trying to bulk insert 2 columns based on the tab character, but sometimes there is bad data without a tab, and sometimes there's no data at all.

At least the carriage return looks consistent. In a situation like this, I would encourage you to create a temp table with a single column (varchar/nvarchar) and bulk insert to that. Once the data is in the temp table, you can clean up your data and then finally insert in to the real table. This process will be slower than going directly to the table you want, but it's also likely to be faster than other methods of cleaning the data, too.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

Yes, thank you

 

[pandatime]

Yes, but wait... once the data is is one big column and cleaned up, how do I go about extracting the columns out of the one big column?

I guess I'm not sure if you can do a substring select on a tab? I'm assuming I would use substring function to get the individual values out of the column.

Thanks

 

[gmmastros]

How many columns do you have?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

If it's just 2 columns, it's pretty simple. The more columns you have, the uglier it gets.

Example:

Declare @Temp Table(Data VarChar(200))

Insert Into @Temp Values('column 1' + char(9) + 'column 2')
Insert Into @Temp Values('col A' + char(9) + 'col B')

Select Data, 
       Left(Data, CharIndex(Char(9), Data)) As FirstColumn, 
       SubString(Data, CharIndex(Char(9), Data) + 1, 8000) As SecondColumn
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

actually i have 4 to 5 per row

 

[pandatime]

You're right, this is pretty ugly!!

select top 10 Data,
left(Data, charindex(char(9), data)) As Date,
substring( (substring(Data, (charindex(Char(9), Data) + 1), 8000)), 1, (charindex(char(9), (substring(Data, (charindex(Char(9), Data) + 1), 8000)))) )
from myTable

and that's only 2 columns parsed :-(

 

[gmmastros]

Kind of a hack, but it might work...

Take a look at this example that loads data in to a temp table (hard coded) and then parses the data in to separate columns.

-- Hardcode data to test
Create Table #Temp(Data VarChar(200))

Insert Into #Temp Values('column 1' + char(9) + 'column 2' + char(9) + 'Column 3')
Insert Into #Temp Values('col A' + char(9) + 'col B' + char(9) + 'Col C')

-- Add 3 more columns
Alter Table #Temp Add Col1 VarChar(20), Col2 VarChar(20), Col3 VarChar(20)

-- look at the data before parsing
Select * From #Temp

-- grab column 1
Update #Temp
Set    Col1 = Left(Data, CharIndex(Char(9), Data)),
       Data = Right(Data, Len(Data) - CharIndex(Char(9), Data))        

-- grab column 2
Update #Temp
Set    Col2 = Left(Data, CharIndex(Char(9), Data)),
       Data = Right(Data, Len(Data) - CharIndex(Char(9), Data))        

-- what's left is column 3
Update #Temp
Set    Col3 = Data

-- look at the final output
Select Col1, Col2, Col3 From #Temp

-- clean up after ourselves.
Drop Table #Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

Yes, I figured that out too. The other way was just too nightmarish!

I'm just updating other columns with a subset of the data, and when I'm finished, chopping off the data in the "Data" column I just snagged. viola.

The code is GREATLY simplified.

thanks