Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

BULK INSERT & Text Qualifier Expand / Collapse
Author
Message
Posted Monday, January 14, 2008 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 4, 2008 2:45 PM
Points: 1, Visits: 3
And what if my data looks like this:

datahere,"data with a comma, more",xxx
datahere,data without a comma,xxx
Post #442487
Posted Monday, January 14, 2008 2:22 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 4:39 PM
Points: 4,576, Visits: 8,342
All of you are wrong.
Using "," as delimites is an error waiting to happen.

mdjtlj, ask you supplier to build a test file containing '19" Monitor'.
Then try to process that file.
Post #442693
Posted Monday, January 14, 2008 3:58 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 4:39 PM
Points: 4,576, Visits: 8,342
For this kind of files it's better to use ODBC drivers.
They read those strings with identifiers properly.

I created text file TestInsert.txt in the folder C:\TextFiles (local to the SQL Server instance):
"Name","Price"
"Test","1,1"
"19""Monitor, the nice one","250"

Then I added linked server:
EXEC sp_addlinkedserver txtsrv1, 'Jet 4.0', 
'Microsoft.Jet.OLEDB.4.0',
'C:\TextFiles',
NULL,
'Text'

To see the list of files available:
EXEC sp_tables_ex txtsrv1

And now - just simple SELECT:
SELECT * 
FROM txtsrv1...[TestInsert#txt]

I remember this code was done based on BOL examples, but I don't remember the topic.
Post #442746
Posted Friday, January 18, 2008 7:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Sergiy (1/14/2008)
All of you are wrong.
Using "," as delimites is an error waiting to happen.

mdjtlj, ask you supplier to build a test file containing '19" Monitor'.
Then try to process that file.


Works fine if BCP format file is properly constructed. Delimiter of "," (including the quotes) is perfect for "real" CSV instead of "comedy separated values".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #445182
Posted Friday, January 18, 2008 7:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
remember this code was done based on BOL examples, but I don't remember the topic.


First, nice idea... hadn't thought about doing it that way. Great way to import from a staging area. Thanks for the tip.

And the topic you're looking for is "Linked Servers".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #445183
Posted Wednesday, January 23, 2008 2:15 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 4:39 PM
Points: 4,576, Visits: 8,342
Jeff Moden (1/18/2008)
[quote]Delimiter of "," (including the quotes) is perfect for "real" CSV instead of "comedy separated values".

Delimiter of "," is as good as '[' + @TableName + ']'.
Works almost every time.
But it's better to use QUOTENAME. Because it deals properly with quotes inside of the variable.
Post #446630
Posted Monday, November 3, 2008 2:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 3, 2008 2:14 AM
Points: 1, Visits: 2
Dear All,

I am also faced the same problem mentioned above, using CSV file bulk upload with [ " ] as a Text Qualifier. Any one have a solution.

I am waiting......

Thanks
Prabu
Post #595677
Posted Monday, November 3, 2008 5:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Ummm... the answer is actually near the beginning of this thread. Instead of waiting, read the rest of the thread.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #595727
Posted Tuesday, March 10, 2009 4:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 12, 2012 5:50 AM
Points: 4, Visits: 33
Alternatively you could just do a find and replace on the original file and get rid of them that way.

I just did because the whole thing was driving me towards an even deeper insanity

Post #672269
Posted Tuesday, March 10, 2009 1:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
Richard Hurst (3/10/2009)
Alternatively you could just do a find and replace on the original file and get rid of them that way.

I just did because the whole thing was driving me towards an even deeper insanity



Using which tool, Richard?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #672757
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse