bulk load: no error message, but not loading

hey guys, my xml bulk load creates no error message, but does not populate my tables.
I am very new to xml and sql server, so maybe this is a simple problem?
i have a root element i dont want to map, and then several tables of data in one document. I am unconcerned with any key constraints at this point.this board wont let me post all my data at once, so one at a time...
heres how i call the load
****************************
Function Main()
Set oXMLBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
oXMLBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=localhost;" & _
"DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
oXMLBulkLoad.Execute "C:\myschema", "C:\myfile"
oXMLBulkLoad.SchemaGen = True
Set oXMLBulkLoad = Nothing
Main = DTSTaskExecResult_success
End Function
***************************************|||ill try attaching my schemasql

Bulk insert skips rows

Hi Guys,

My little bulk insert is only bringing every second row of a CSV file. this is not good as i need every row.

My SQLcommand is thus.

InsertCommand="BULK INSERT TBL_Unitel_services FROM 'C:/webroot/servicedesk/csvs_Services/csv.csv' WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', MAXERRORS = 0) "

Every thing looks fine, just change this

ROWTERMINATOR = '\n'

as

ROWTERMINATOR = '\r\n'

Let me know if you still have issues or Mark the thread as answered if it answers your issue.

|||

no joy.

I am sure its becasue i have a computed column at the end of the table. If i remove it all the rows are entered, with it only half come in GAHHHH!!

sql

bulk insert question? Another way?

hi guys,
I have a text file contains about 1 million rows, I tried to import to a
table but I received data conversion errors for some rows, however, the row
number is like 504500 for example, I want to troubleshoot this row but the
only way I know is to open the text file (300MB) which will take forever to
open on notepad, I'm not sure if there is an easy way to troubleshoot such
a problem. Another thing is, I set Batchsize=10000, so I guess if I
received errors for 20 rows, it would mean that 200000 rows (20 *10000)
didn't get inserted into table, correct?
ThanksKevin,
Get a copy of TextPad or one of the many shareware or freeware text editors
that can handle large files with ease.
Another alternative is to import the file into a one-column staging table on
your SQL Server, and then scrub the data before inserting it into the target
table.
Steve Kass
Drew University
kevin wrote:

>hi guys,
> I have a text file contains about 1 million rows, I tried to import to
a
>table but I received data conversion errors for some rows, however, the ro
w
>number is like 504500 for example, I want to troubleshoot this row but the
>only way I know is to open the text file (300MB) which will take forever to
>open on notepad, I'm not sure if there is an easy way to troubleshoot such
>a problem. Another thing is, I set Batchsize=10000, so I guess if I
>received errors for 20 rows, it would mean that 200000 rows (20 *10000)
>didn't get inserted into table, correct?
>Thanks
>
>
>

bulk insert problem

hi guys, I have a data file that I like to import to a table.
the column delimiter is @., row delimiter is \n, line feed.
But here is the problem, we have a column called "comment",
basically user can enter any characters to it, so if "comment" column
contains
@. character, then sql server think it's an extra column. However, that's
why before @. character we put \ , it's \@. in comment column now. BUT How
come it doesn't do escape for @. character? Is it possible to create an
exception for delimiter in case if Sql server sees \@. , then it'll ignore it
and will not treat it as delimiter?
thanksWhy not do a global replace of @. with some other sequence of characters that
is not already found in the file? Import the file and then do another
replace in the column to put the characters back to @..
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:3D940ABE-6D54-48DA-9164-0D22787DB201@.microsoft.com...
> hi guys, I have a data file that I like to import to a table.
> the column delimiter is @., row delimiter is \n, line feed.
> But here is the problem, we have a column called "comment",
> basically user can enter any characters to it, so if "comment" column
> contains
> @. character, then sql server think it's an extra column. However,
> that's
> why before @. character we put \ , it's \@. in comment column now. BUT
> How
> come it doesn't do escape for @. character? Is it possible to create an
> exception for delimiter in case if Sql server sees \@. , then it'll ignore
> it
> and will not treat it as delimiter?
> thanks
>|||in this case, it's possible to write perl script to do global replacement in
windows?
This way I can make it automate process.
"Andrew J. Kelly" wrote:

> Why not do a global replace of @. with some other sequence of characters th
at
> is not already found in the file? Import the file and then do another
> replace in the column to put the characters back to @..
> --
> Andrew J. Kelly SQL MVP
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:3D940ABE-6D54-48DA-9164-0D22787DB201@.microsoft.com...
>
>|||I thought this might have been a one time import but I don't see why you
can't use perl (or some other utility) to automate this.
Andrew J. Kelly SQL MVP
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:6D17EF5E-0150-4F38-80BB-91D6F086CA70@.microsoft.com...
> in this case, it's possible to write perl script to do global replacement
> in
> windows?
> This way I can make it automate process.
>
> "Andrew J. Kelly" wrote:
>sql

Bulk insert problem

Hi guys
I am using the Bulk insert command to import a flat file to the SQL server
2005.
My flat file contains about 1 million rows.
I have already created an empty table to insert the rows from the flat file.
But I dont want to import the first row into my table in the database.
Is there a way I can give some some sort of condition in my bulk insert
command so that the first row from the flat file does not get imported'
Here is my bulk insert statement:
BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
(FIELDTERMINATOR = ',')
Cheers
Mita"mita" <mita@.discussions.microsoft.com> wrote in message
news:F742B532-8FA9-4AC1-89AC-7E50FC757619@.microsoft.com...
> Hi guys
> I am using the Bulk insert command to import a flat file to the SQL server
> 2005.
> My flat file contains about 1 million rows.
> I have already created an empty table to insert the rows from the flat
> file.
> But I dont want to import the first row into my table in the database.
> Is there a way I can give some some sort of condition in my bulk insert
> command so that the first row from the flat file does not get imported'
> Here is my bulk insert statement:
> BULK INSERT new1 FROM 'f:\My Documents\flatFileBaseline\one.txt' WITH
> (FIELDTERMINATOR = ',')
>
Read the entry in Books Online for BULK INSERT.
David

Bulk Insert from a table to Another

Hi guys,

Consider this Scenario.

I have two Tables.

Table1-Users

Fields are

id, name,joindate,designation, status

Table2-People

Fields are

id, name, status

The table Users have data in it say 100 records

I have to fill it toPeople Table where id=id and name=name and status=status

Any Way?

Regards,

Naveen

If it's an insert action, why do you need to match the row values?

For a simple insert, you can do:

INSERT INTO [people](id,name, status)SELECT id,name, status-- assumed id is PKFROM [users]WHERE idNOT IN (SELECT idFROM people)-- make sure you don't insert duplicate ID

Bulk Insert doubts

Hello Guys,

soon soon, ll have to devellop some procedures to read an ASCII file to supply MS SQL tables. As Ive read some old post, Ive understand that I have to use BULK INSERT , or else, BCP or DTS. Id like to know the diference between this commands and witch of them is more powerful, faster and efficient. If you can give me some implementation tips, I will be very grateful.

thanx allDid you look it up in BOL...

How is the data stored?

anyway...I almost exclusivley use bcp for Production code

BULK INSERT usually for quick data analysis..

and DTS for analysis, if the data is in Excel, Access, whatever...|||DTS is the most flexible, because it can go from more or less automated (using the wizard), to very task oriented (using the painter), to micro-managing the transfer (using VBA).

BCP and BULK INSERT are both tools that expose the current equivalents to the old Bulk Copy API. They are very efficient, but not extremely flexible or friendly.

-PatP|||But what's the most effecient?

EDIT: And do you not think releasing DTS to a production environment is more painful?|||BCP and BULK INSERT are simply differet front ends to the same code. Performancewise the difference is irrelevant.

I live and breathe in a replicated environment. One of our machines does virtually all of our DTS jobs against an "interface" server that has little or no user load.

We use DTS against our OLTP servers, our DW servers, and a number of "friendly" machines that need data. I've never had any real complaints about it.

-PatP|||Well thanx for all replies... Talking about what I have... Ill have 30 ASCII files to load as I told you, and approximatly 91292,42 KB for each... Itll run in production environment, to synchronize the ambient for a BI system... Which comand should I use?

Originally posted by Brett Kaiser
Did you look it up in BOL...

How is the data stored?

anyway...I almost exclusivley use bcp for Production code

BULK INSERT usually for quick data analysis..

and DTS for analysis, if the data is in Excel, Access, whatever...|||I suggest DTS is best and reliable tool to import those ASCII text files to the database. And even you can schedule the same package if its ongoing BI requirement.

As suggested you should follow books online for all the information.
For DTS specifically keep in touch with http://www.sqldts.com.