Importing Text Files

hi friends ,

Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .

Thanks $ Regards

Nevin

You can use BULK INSERT

Code Snippet

BULK INSERT [DatabaseName].dbo.[Table Name]
FROM '<Your CSV File>'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)

|||

You have to use Like this

CREATE TABLE F(A VARCHAR(MAX))

BULK INSERT [learner].dbo.[F]

FROM 'E:\SHARE\TE.TXT'

WITH

(

FIELDTERMINATOR = '|',

ROWTERMINATOR = '|\n'

)

SELECT * FROM F

|||

thank you very much Krupa Sankar . very useful to me it works fine

|||

Thank you very much .It works fine

importing excel file into sql server 2005 in asp .net using c#

Dear friends i want to import excel file into sql server 2005 in asp .net using c# can anyone help me?

rgds,
RK

try the link below [SqlBulkCopy]

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

 
// Connection String to Excel Workbookstring excelConnectionString = @."Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";// Create Connection to Excel Workbookusing (OleDbConnection connection =new OleDbConnection(excelConnectionString)){ OleDbCommand command =new OleDbCommand ("Select ID,Data FROM [Data$]", connection); connection.Open();// Create DbDataReader to Data Worksheetusing (DbDataReader dr = command.ExecuteReader()) {// SQL Server Connection Stringstring sqlConnectionString ="Data Source=.; Initial Catalog=Test;Integrated Security=True";// Bulk Copy to SQL Serverusing (SqlBulkCopy bulkCopy =new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName ="ExcelData"; bulkCopy.WriteToServer(dr); } }}
 

Good Luck./.

|||

Hi

you have to first read the XL content then you have to insert into Sql server.

you can transfer your XL to dataset by Oledb provider by this code

string conection_string ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=YOUR XL PATH; Extended Properties='Excel 8.0;IMEX=1;HDR=YES;'";

OleDbDataAdapter adp =newOleDbDataAdapter("SELECT * FROM [Sheet1$] ", conection_string);

DataSet ds =newDataSet();

adp.Fill(ds);

now your xl content would be in dataset ds , then you can access this dataset as well and read dataset line by line and insert into Sql

add namespace :using System.Data.OleDb;

|||

Dear what is Sheet1$ can u tell me detail?

|||

ramu778:

Dear what is Sheet1$ can u tell me detail?

sorry to interrupt

in ConnectionString; for DataSource we give the XLS file path...

and in below code line... we have to give the Sheet name in the Select Query from which we have to read the date from, as there would be more than one Sheet in an excel file...

OleDbDataAdapter adp =newOleDbDataAdapter("SELECT * FROM [Sheet1$] ", conection_string);

hope it clears./.

|||

Hi

sheet1 is nothing but the name of the XL sheet , there are some name at bottom of XL like sheet1,sheet2 and so, we have to give that name

importing DB from another SQL server

Hello,
I need to set up a development platform at home with a copy of a DB from a
SQL server at a friend's company.
So far, all my attempts at backup/restore or export/import have failed
despite my reading all the documentation I could find (I am a newbie).
Using DTS/Export wizard, I tried to export the DB first to a plain text
file, then to an excells file. After having gone through all the screens,
the export script runs. It exported all the data for close to one hour, but
at the end I only get a 0kb empty text file, or 5kb empty excells file.
Is there a feature on SQL server preventing export (such as for security
reasons)? Then why would the export wizard run for so long, just to output
nothing?
Since it's only for developement, I don't need the latest data yet, so I
burned on CD the .bak backup files previously made, but I couldn't import
nor restore them on the other machine. Depending on what I tried, I had
various error messages, like
* an improper login and password (which should this be: that of the owner
of the DB on the source SQL server, or that of the SQL admin or windows
admin on the target server?),
* an error saying that the backup doesn't belong to the empty DB I try to
restore to...
* I even managed to import a mydbname.bak.dns file with the DTS wizard
(apparently it was a backup of an Access DB, not the SQL Server DB I need),
but the import script runs apparently smoothly for a while, but then the
target DB didn't contain any of the imported tables, only the sys tables.
For the last several weeks, I have tried everything I could think of, read
the docs, but still I am at a loss as to how to import or make a copy on my
system of the BD from the office.
What would be the proper procedure to import/restore the copies of the
backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
2000?
Failing that, I can go back to my friend's office and try again to export
the data, but how do I make sure I don't end up with an empty file?
thank you,
Anguo.

http://www.gnosis-usa.com/
Revolutionary Psychology, White Tantrism, Dream Yoga...
http://www.reuniting.info/
Intimate Relationships, peace and harmony in the couple.
Message posted via http://www.sqlmonster.com
If you do not need the data, why not just script the objects out and run it
against your new server. You can use Enterprise Manager/QueryAnalyser to
script the DDL. You can also use QALite from rac4sql.net for this.
-oj
"Anguo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:86f3fb22900e440bbd05d784d291fc06@.SQLMonster.c om...
> Hello,
> I need to set up a development platform at home with a copy of a DB from a
> SQL server at a friend's company.
> So far, all my attempts at backup/restore or export/import have failed
> despite my reading all the documentation I could find (I am a newbie).
> Using DTS/Export wizard, I tried to export the DB first to a plain text
> file, then to an excells file. After having gone through all the screens,
> the export script runs. It exported all the data for close to one hour,
> but
> at the end I only get a 0kb empty text file, or 5kb empty excells file.
> Is there a feature on SQL server preventing export (such as for security
> reasons)? Then why would the export wizard run for so long, just to output
> nothing?
> Since it's only for developement, I don't need the latest data yet, so I
> burned on CD the .bak backup files previously made, but I couldn't import
> nor restore them on the other machine. Depending on what I tried, I had
> various error messages, like
> * an improper login and password (which should this be: that of the owner
> of the DB on the source SQL server, or that of the SQL admin or windows
> admin on the target server?),
> * an error saying that the backup doesn't belong to the empty DB I try to
> restore to...
> * I even managed to import a mydbname.bak.dns file with the DTS wizard
> (apparently it was a backup of an Access DB, not the SQL Server DB I
> need),
> but the import script runs apparently smoothly for a while, but then the
> target DB didn't contain any of the imported tables, only the sys tables.
> For the last several weeks, I have tried everything I could think of, read
> the docs, but still I am at a loss as to how to import or make a copy on
> my
> system of the BD from the office.
> What would be the proper procedure to import/restore the copies of the
> backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
> 2000?
> Failing that, I can go back to my friend's office and try again to export
> the data, but how do I make sure I don't end up with an empty file?
> thank you,
> Anguo.
>
>
> --
> http://www.gnosis-usa.com/
> Revolutionary Psychology, White Tantrism, Dream Yoga...
> http://www.reuniting.info/
> Intimate Relationships, peace and harmony in the couple.
> --
> Message posted via http://www.sqlmonster.com
|||Thanks, but I do need some data even if it is not up to date, in order to
work with as a sample.
I have been advised to read this article:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...en-us;314546#9
I am looking into it.
I still would like to know how come the export procedure didn't work and I
only got an empty file.
Thank you.
Message posted via http://www.sqlmonster.com
|||> I still would like to know how come the export procedure didn't work and I
> only got an empty file.
I suggest you post that question to the DTS group. There are many many possibilities within DTS, and
you would need to give us information on exactly how you specified the DTS execution.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anguo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:3298983102dd4e1db62952dfbea8e130@.SQLMonster.c om...
> Thanks, but I do need some data even if it is not up to date, in order to
> work with as a sample.
>
> I have been advised to read this article:
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default...en-us;314546#9
> I am looking into it.
> I still would like to know how come the export procedure didn't work and I
> only got an empty file.
> Thank you.
> --
> Message posted via http://www.sqlmonster.com
|||The best (most reliable, fastest and fewest steps involved) method to
restore a DB on other server is to detach the production MDFs, make a copy
of the MDF files, re-attach the production MDFs, then reattach the MDFs on
your development PC. You don't have to worry about data type conversion,
scripting SPs, re-importing RI data in proper order, etc.
If you want to use DTS, then keep in ming the following. When exporting
to text, you may encounter data type conversion issues. Also, the problem
with Excel is that it supports only 60k rows, and perhaps that is the
problem you are currently having. The best externals file format for DTSing
into is MS Access. It preserves the data types and can contain all the
tables in a single Access database file. Regardless of the export format you
choose, the SPs, Views, etc. need to be scripted sperately.
"Anguo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:86f3fb22900e440bbd05d784d291fc06@.SQLMonster.c om...
> Hello,
> I need to set up a development platform at home with a copy of a DB from a
> SQL server at a friend's company.
> So far, all my attempts at backup/restore or export/import have failed
> despite my reading all the documentation I could find (I am a newbie).
> Using DTS/Export wizard, I tried to export the DB first to a plain text
> file, then to an excells file. After having gone through all the screens,
> the export script runs. It exported all the data for close to one hour,
but
> at the end I only get a 0kb empty text file, or 5kb empty excells file.
> Is there a feature on SQL server preventing export (such as for security
> reasons)? Then why would the export wizard run for so long, just to output
> nothing?
> Since it's only for developement, I don't need the latest data yet, so I
> burned on CD the .bak backup files previously made, but I couldn't import
> nor restore them on the other machine. Depending on what I tried, I had
> various error messages, like
> * an improper login and password (which should this be: that of the owner
> of the DB on the source SQL server, or that of the SQL admin or windows
> admin on the target server?),
> * an error saying that the backup doesn't belong to the empty DB I try to
> restore to...
> * I even managed to import a mydbname.bak.dns file with the DTS wizard
> (apparently it was a backup of an Access DB, not the SQL Server DB I
need),
> but the import script runs apparently smoothly for a while, but then the
> target DB didn't contain any of the imported tables, only the sys tables.
> For the last several weeks, I have tried everything I could think of, read
> the docs, but still I am at a loss as to how to import or make a copy on
my
> system of the BD from the office.
> What would be the proper procedure to import/restore the copies of the
> backup I have burnt on CD into my fresh install of W2000/SP1 + SQL Server
> 2000?
> Failing that, I can go back to my friend's office and try again to export
> the data, but how do I make sure I don't end up with an empty file?
> thank you,
> Anguo.
>
>
> --
> http://www.gnosis-usa.com/
> Revolutionary Psychology, White Tantrism, Dream Yoga...
> http://www.reuniting.info/
> Intimate Relationships, peace and harmony in the couple.
> --
> Message posted via http://www.sqlmonster.com