SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Bulk Insert with missing columns in row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JAdauto
Posting Yak Master

USA
154 Posts

Posted - 08/22/2012 :  13:52:17  Show Profile  Reply with Quote
I have the following bulk insert

BULK INSERT #Data
FROM ''' + @FilePathName + '''
WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
FIRSTROW = 2
)

but, I have a file that Im importing that sometimes has a couple of bad rows of data, for example, it might be missing the price column on row 5. (price and comma both).
Is there a way to check each row of data to see if it has a valid number of columns?
Thanks!

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 08/22/2012 :  14:15:40  Show Profile  Reply with Quote
Do you have to use the bulk insert?
Import/Export Wizard can do the job.
it will allow you to specify the TextQualifier.

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/22/2012 :  14:29:30  Show Profile  Reply with Quote
quote:
Originally posted by JAdauto

I have the following bulk insert

BULK INSERT #Data
FROM ''' + @FilePathName + '''
WITH
(
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
FIRSTROW = 2
)

but, I have a file that Im importing that sometimes has a couple of bad rows of data, for example, it might be missing the price column on row 5. (price and comma both).
Is there a way to check each row of data to see if it has a valid number of columns?
Thanks!


if its missing delimiter also then it would be difficult. If you've a blank place holder it will work fine. how is this file generated?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

JAdauto
Posting Yak Master

USA
154 Posts

Posted - 08/23/2012 :  09:39:31  Show Profile  Reply with Quote
Im not sure how file is generated. We recieve it from 3rd party.
It is using Bulk insert now, but that is how I inhereted the code. Im not completely locked into that, although Im trying to make the least amount of invasive changes as possible.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/23/2012 :  10:05:52  Show Profile  Reply with Quote
quote:
Originally posted by JAdauto

Im not sure how file is generated. We recieve it from 3rd party.
It is using Bulk insert now, but that is how I inhereted the code. Im not completely locked into that, although Im trying to make the least amount of invasive changes as possible.



if intermediate columns are missing without place holders then its really difficult to process it.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

JAdauto
Posting Yak Master

USA
154 Posts

Posted - 10/09/2012 :  16:10:22  Show Profile  Reply with Quote
I know this issue is old, but Im back to it. I have the following example in my CSV

LastName,FirstName,Number,Size
Doe,John,101,Med
Doe,Jane, 102,Med
Williams,Frank,201,Large
Williams,202,Large
Howard,Henry,301,Small


Notice that record #4 is missing the FirstName column completely (comma is missing as well).

I run BulkCopy to import that into a temp table and then evaluate data and insert into a permanent table. the Columns of my permanent table include the following fields:

lastname, firstname, number, size, active

The real probably I am having is not that the record #4 is not inserting as it should (bad data). The issue I am having is that record #5 is jacked up because of record #4 and then inputs a 0 into Active for record #5.

Has anyone seen anything like this before where the record following a bad row gets also messed up?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/09/2012 :  22:58:42  Show Profile  Reply with Quote
quote:
Originally posted by JAdauto

I know this issue is old, but Im back to it. I have the following example in my CSV

LastName,FirstName,Number,Size
Doe,John,101,Med
Doe,Jane, 102,Med
Williams,Frank,201,Large
Williams,202,Large
Howard,Henry,301,Small


Notice that record #4 is missing the FirstName column completely (comma is missing as well).

I run BulkCopy to import that into a temp table and then evaluate data and insert into a permanent table. the Columns of my permanent table include the following fields:

lastname, firstname, number, size, active

The real probably I am having is not that the record #4 is not inserting as it should (bad data). The issue I am having is that record #5 is jacked up because of record #4 and then inputs a 0 into Active for record #5.

Has anyone seen anything like this before where the record following a bad row gets also messed up?




I think the only way in such cases is to pull entire row of data into a single column table and then use a parsing logic based on , delimiter to get individual values out.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000