Dear Dano,
In my actual data file there are many fields like address and all that is sometimes more than 8k char, so in that case rest chars are trimmed.
So which kind of application should i write to import this data properly.
Thanks in advance.
Regards,
Nabeel Ahmad
-----Original Message-----
From: Smith Dano [mailto:mssqldba-ezmlmshield-x99321917.[Email address protected]
Sent: Thursday, June 28, 2007 6:46 PM
To: LazyDBA Discussion
Subject: RE: BULK INSERT with firstrow=2
Your early example showed a very narow data-set to import, not 8000
characters.
If you really have data files that are thousands of characters long -
you need to write an actuall application to import this data properly.
Dano
-----Original Message-----
From: Nabeel Ahmad
[mailto:mssqldba-ezmlmshield-x64926462.[Email address protected]
Sent: Thursday, June 28, 2007 8:49 AM
To: LazyDBA Discussion
Subject: RE: BULK INSERT with firstrow=2
Importance: Low
Hi,
Can anybody look at the below problem and provide me the solution.
Regards,
Nabeel Ahmad
-----Original Message-----
From: Nabeel Ahmad [mailto:mssqldba-ezmlmshield-x71467106.[Email address
protected]
Sent: Wednesday, June 27, 2007 12:16 PM
To: LazyDBA Discussion
Subject: RE: BULK INSERT with firstrow=2
Also Dano,
If we will insert all the text in column then max col size is 8000, if
the size is more than 8000 then the rest text will not be inserted into
that column.
So data loss may happen in this case.
Any other solution please.
Regards,
Nabeel Ahmad
-----Original Message-----
From: Smith Dano [mailto:[Email address protected]
Sent: Tuesday, June 26, 2007 6:28 PM
To: Nabeel Ahmad
Subject: RE: BULK INSERT with firstrow=2
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Garbage In Garbage Out,
Take out the garbage.
The first row is still parsed, and it needs to match the file definition
on col/row terminators.
I get crappy files like this all the time, and in this case, I would
suggest.
Create a table "dbo.Line (col1 line varchar(2000) <-- whatever your max
len is)
Then bulk insert into the one single line, no column delimiter, just a
row delimiter.
Then after the data is in sql, in a single column, you can query to your
hearts content, leaving out the first, row.
Parsing as needed, etc.
Or conceiveably, you could remove the first row, bulk copy back out,
then back in a final time, with the formatted columns.
Dano Smith
-----Original Message-----
From: Nabeel Ahmad
[Email address protected]
Sent: Monday, June 25, 2007 11:38 PM
To: LazyDBA Discussion
Subject: BULK INSERT with firstrow=2
Importance: Low
Hi,
I have a text file and having the header of unwanted columns having less
than the no. of data column in the txt file. I am using the bulk insert
command to insert the text file data into the sql server table by using
the below query:
BULK INSERT Mytable FROM 'e:\Mytext.txt'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
Mytext.txt contains, lets assume like this ---
Name;FName;year;Sex
Robin Singh;Robin;2007;M;Graduate;Working;India
John Mathew;John;1999;M;Post Graduate;Working;USA
Rita Jhonson;Rita;2000;F;Graduate;Housewife;UK
When we are running above query it is inserting from the 3 row i.e. John
Mathew skipping the 2 row. What I come to know from the above result
that if the header has less columns in the txt file then it considers
2nd row as 1st row that's why it's skipping the 2nd row when we are
using above bulk insert.
If there is any solution to insert all the row except header kindly let
me know.
Regards,
Nabeel Ahmad
MASTEK
"Making a valuable difference"
Mastek in NASSCOM's 'India Top 20' Software Service Exporters List.
In the US, we're called MAJESCOMASTEK
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Opinions expressed in this e-mail are those of the individual and not
that of Mastek Limited, unless specifically indicated to that effect.
Mastek Limited does not accept any responsibility or liability for it.
This e-mail and attachments (if any) transmitted with it are
confidential and/or privileged and solely for the use of the intended
person or entity to which it is addressed. Any review, re-transmission,
dissemination or other use of or taking of any action in reliance upon
this information by persons or entities other than the intended
recipient is prohibited. This e-mail and its attachments have been
scanned for the presence of computer viruses. It is the responsibility
of the recipient to run the virus check on e-mails and attachments
before opening them. If you have received this e-mail in error, kindly
delete this e-mail from all computers.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-
MASTEK
"Making a valuable difference"
Mastek in NASSCOM's 'India Top 20' Software Service Exporters List.
In the US, we're called MAJESCOMASTEK
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Opinions expressed in this e-mail are those of the individual and not
that of Mastek Limited, unless specifically indicated to that effect.
Mastek Limited does not accept any responsibility or liability for it.
This e-mail and attachments (if any) transmitted with it are
confidential and/or privileged and solely for the use of the intended
person or entity to which it is addressed. Any review, re-transmission,
dissemination or other use of or taking of any action in reliance upon
this information by persons or entities other than the intended
recipient is prohibited. This e-mail and its attachments have been
scanned for the presence of computer viruses. It is the responsibility
of the recipient to run the virus check on e-mails and attachments
before opening them. If you have received this e-mail in error, kindly
delete this e-mail from all computers.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MASTEK
"Making a valuable difference"
Mastek in NASSCOM's 'India Top 20' Software Service Exporters List.
In the US, we're called MAJESCOMASTEK
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Opinions expressed in this e-mail are those of the individual and not
that of Mastek Limited, unless specifically indicated to that effect.
Mastek Limited does not accept any responsibility or liability for it.
This e-mail and attachments (if any) transmitted with it are
confidential and/or privileged and solely for the use of the intended
person or entity to which it is addressed. Any review, re-transmission,
dissemination or other use of or taking of any action in reliance upon
this information by persons or entities other than the intended
recipient is prohibited. This e-mail and its attachments have been
scanned for the presence of computer viruses. It is the responsibility
of the recipient to run the virus check on e-mails and attachments
before opening them. If you have received this e-mail in error, kindly
delete this e-mail from all computers.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MASTEK
"Making a valuable difference"
Mastek in NASSCOM's 'India Top 20' Software Service Exporters List.
In the US, we're called MAJESCOMASTEK
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Opinions expressed in this e-mail are those of the individual and not that of Mastek Limited, unless specifically indicated to that effect. Mastek Limited does not accept any responsibility or liability for it. This e-mail and attachments (if any) transmitted with it are confidential and/or privileged and solely for the use of the intended person or entity to which it is addressed. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. This e-mail and its attachments have been scanned for the presence of computer viruses. It is the responsibility of the recipient to run the virus check on e-mails and attachments before opening them. If you have received this e-mail in error, kindly delete this e-mail from all computers.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MS Sql Server LazyDBA home page