Import multiple txt files

I have 8GB of text files which are basically log files from the past few years.
There is 24 text files per directory which are labeled for every day (so they are not all in 1 folder).
It would make reading them much easier if I could import them to SQL but I only seem to be able to import 1 at a time? (with the wizards :eek: )

Surely there is a way to mass import without all the costly applications that google searches give me?
cheers :PUse a DTS package implementing looping constructs with ActiveX scripts to loop through each folder and import the data contained in each file. There are some basic examples of how to do this on SQLDTS.com

Regards
Lempster|||If you are using SQL Server 2005 then you can use Integration service.
SSIS provides number of features to perform variety of tasks. You can use for loop container to import multiple text files at a time.|||I would use bcp and a cmd shell script for this.|||I would use bcp and a cmd shell script for this.

I would probably wrap a DTSRun in a cmd shell script (with a for/do) and pass in whatever needed input variables (date, basefilename, etc.), I just wish cmd had good "native" equivalents of grep and sed that exist in UNIX. I've used type | find, type | findstr but it gets a bit clumsy.|||Lost of ideas for me to look at here hehe, thanks a lot

Import from a file does not load data

I am using sql server 2000 on my Desktop (Windows 2000). I exported a
table to a .txt file, When I imported table using DTS wizard. I do
not get any errors. Yet, no data is loaded in the table. How can I
troubleshoot the problem. Which logs I can look into to find the
problem.
I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
2000.Hi
I assume you are using the import wizard in which case it should have shown
a preview of the data. If you chose the run immediately option it should have
shown the number if records inserted once the copy data step has competed.
How many rows are you expecting from the file?
Are you loading this into a new table?
If the import summary showed no rows but you know the file has data then you
may want to change the deliminators you have specified!
John
"zigzagdna@.yahoo.com" wrote:
> I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> table to a .txt file, When I imported table using DTS wizard. I do
> not get any errors. Yet, no data is loaded in the table. How can I
> troubleshoot the problem. Which logs I can look into to find the
> problem.
> I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> 2000.
>|||Import summary showed 2 rows were loaded, yet nothing was loaded.
When I did export from the wizard, it put 2 rows in the file, no create
table staement. My table employee alerady exists in the table, it has no
rows before import. I was hpoing it will have 2 rows after import, but it did
not. Is there anyway to turn on some tracing to see where the import had
problems.
"John Bell" wrote:
> Hi
> I assume you are using the import wizard in which case it should have shown
> a preview of the data. If you chose the run immediately option it should have
> shown the number if records inserted once the copy data step has competed.
> How many rows are you expecting from the file?
> Are you loading this into a new table?
> If the import summary showed no rows but you know the file has data then you
> may want to change the deliminators you have specified!
> John
>
> "zigzagdna@.yahoo.com" wrote:
> > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > table to a .txt file, When I imported table using DTS wizard. I do
> > not get any errors. Yet, no data is loaded in the table. How can I
> > troubleshoot the problem. Which logs I can look into to find the
> > problem.
> >
> > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > 2000.
> >
> >|||Hi
When you export using the Export wizard it will only create a data file, you
can script the table creation script through Enterprise Manager or Query
Analyser.
Have you tables under a different schema with the same name?
If you create a DTS package from the Import/Export Wizard you can then
modify the package and use the DTS to log information and handle errors.
John
"Prem Mehrotra" wrote:
> Import summary showed 2 rows were loaded, yet nothing was loaded.
> When I did export from the wizard, it put 2 rows in the file, no create
> table staement. My table employee alerady exists in the table, it has no
> rows before import. I was hpoing it will have 2 rows after import, but it did
> not. Is there anyway to turn on some tracing to see where the import had
> problems.
> "John Bell" wrote:
> > Hi
> >
> > I assume you are using the import wizard in which case it should have shown
> > a preview of the data. If you chose the run immediately option it should have
> > shown the number if records inserted once the copy data step has competed.
> > How many rows are you expecting from the file?
> > Are you loading this into a new table?
> > If the import summary showed no rows but you know the file has data then you
> > may want to change the deliminators you have specified!
> >
> > John
> >
> >
> >
> > "zigzagdna@.yahoo.com" wrote:
> >
> > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > > table to a .txt file, When I imported table using DTS wizard. I do
> > > not get any errors. Yet, no data is loaded in the table. How can I
> > > troubleshoot the problem. Which logs I can look into to find the
> > > problem.
> > >
> > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > > 2000.
> > >
> > >|||I finally figured the problem. Name of the export file has to match with
table being exported/imported. So when importing, it created a new table and
was putting rows
there. Also while in Enterpirse Manager, I did not see these tables
until today.
It seems to see new tables screated by import, one must disconnect from
database and reconnect to it, that's why I had no idea what was going on.
Thanks a lot for all your help.
"John Bell" wrote:
> Hi
> When you export using the Export wizard it will only create a data file, you
> can script the table creation script through Enterprise Manager or Query
> Analyser.
> Have you tables under a different schema with the same name?
> If you create a DTS package from the Import/Export Wizard you can then
> modify the package and use the DTS to log information and handle errors.
> John
> "Prem Mehrotra" wrote:
> > Import summary showed 2 rows were loaded, yet nothing was loaded.
> > When I did export from the wizard, it put 2 rows in the file, no create
> > table staement. My table employee alerady exists in the table, it has no
> > rows before import. I was hpoing it will have 2 rows after import, but it did
> > not. Is there anyway to turn on some tracing to see where the import had
> > problems.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I assume you are using the import wizard in which case it should have shown
> > > a preview of the data. If you chose the run immediately option it should have
> > > shown the number if records inserted once the copy data step has competed.
> > > How many rows are you expecting from the file?
> > > Are you loading this into a new table?
> > > If the import summary showed no rows but you know the file has data then you
> > > may want to change the deliminators you have specified!
> > >
> > > John
> > >
> > >
> > >
> > > "zigzagdna@.yahoo.com" wrote:
> > >
> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > > > table to a .txt file, When I imported table using DTS wizard. I do
> > > > not get any errors. Yet, no data is loaded in the table. How can I
> > > > troubleshoot the problem. Which logs I can look into to find the
> > > > problem.
> > > >
> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > > > 2000.
> > > >
> > > >|||Hi
The wizard will default the name of the table, but you do have the option to
change it.
John
"Prem Mehrotra" wrote:
> I finally figured the problem. Name of the export file has to match with
> table being exported/imported. So when importing, it created a new table and
> was putting rows
> there. Also while in Enterpirse Manager, I did not see these tables
> until today.
> It seems to see new tables screated by import, one must disconnect from
> database and reconnect to it, that's why I had no idea what was going on.
> Thanks a lot for all your help.
> "John Bell" wrote:
> > Hi
> >
> > When you export using the Export wizard it will only create a data file, you
> > can script the table creation script through Enterprise Manager or Query
> > Analyser.
> >
> > Have you tables under a different schema with the same name?
> >
> > If you create a DTS package from the Import/Export Wizard you can then
> > modify the package and use the DTS to log information and handle errors.
> >
> > John
> > "Prem Mehrotra" wrote:
> >
> > > Import summary showed 2 rows were loaded, yet nothing was loaded.
> > > When I did export from the wizard, it put 2 rows in the file, no create
> > > table staement. My table employee alerady exists in the table, it has no
> > > rows before import. I was hpoing it will have 2 rows after import, but it did
> > > not. Is there anyway to turn on some tracing to see where the import had
> > > problems.
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I assume you are using the import wizard in which case it should have shown
> > > > a preview of the data. If you chose the run immediately option it should have
> > > > shown the number if records inserted once the copy data step has competed.
> > > > How many rows are you expecting from the file?
> > > > Are you loading this into a new table?
> > > > If the import summary showed no rows but you know the file has data then you
> > > > may want to change the deliminators you have specified!
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > > "zigzagdna@.yahoo.com" wrote:
> > > >
> > > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > > > > table to a .txt file, When I imported table using DTS wizard. I do
> > > > > not get any errors. Yet, no data is loaded in the table. How can I
> > > > > troubleshoot the problem. Which logs I can look into to find the
> > > > > problem.
> > > > >
> > > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > > > > 2000.
> > > > >
> > > > >|||THe import wizard allows you to specify the name of the table being
created, or to specify an existing table. For an existing table you
can specify if the table is to be truncated first, or the new data
appended to the old. When you get to the window in the wizard with
three columns - Source, Destination, Transform - you can click on
Desination to make changes to the name or select an existing table
from the list. Click on Transform to specify append or replace, and
other options such as controlling the names and data types of the
columns in the table being created.
There is more to the import wizard, and I suggest it is worth your
time playing with it for a while and exploring the options so you are
familiar with what is there. The most important feature not mentioned
so far is that you can save it as a DTS package and then edit it in
the DTS interface - good for minor tweaking.
Roy Harvey
Beacon Falls, CT
On Mon, 20 Aug 2007 18:48:02 -0700, Prem Mehrotra
<PremMehrotra@.discussions.microsoft.com> wrote:
>I finally figured the problem. Name of the export file has to match with
>table being exported/imported. So when importing, it created a new table and
>was putting rows
>there. Also while in Enterpirse Manager, I did not see these tables
>until today.
>It seems to see new tables screated by import, one must disconnect from
>database and reconnect to it, that's why I had no idea what was going on.
>Thanks a lot for all your help.
>"John Bell" wrote:
>> Hi
>> When you export using the Export wizard it will only create a data file, you
>> can script the table creation script through Enterprise Manager or Query
>> Analyser.
>> Have you tables under a different schema with the same name?
>> If you create a DTS package from the Import/Export Wizard you can then
>> modify the package and use the DTS to log information and handle errors.
>> John
>> "Prem Mehrotra" wrote:
>> > Import summary showed 2 rows were loaded, yet nothing was loaded.
>> > When I did export from the wizard, it put 2 rows in the file, no create
>> > table staement. My table employee alerady exists in the table, it has no
>> > rows before import. I was hpoing it will have 2 rows after import, but it did
>> > not. Is there anyway to turn on some tracing to see where the import had
>> > problems.
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi
>> > >
>> > > I assume you are using the import wizard in which case it should have shown
>> > > a preview of the data. If you chose the run immediately option it should have
>> > > shown the number if records inserted once the copy data step has competed.
>> > > How many rows are you expecting from the file?
>> > > Are you loading this into a new table?
>> > > If the import summary showed no rows but you know the file has data then you
>> > > may want to change the deliminators you have specified!
>> > >
>> > > John
>> > >
>> > >
>> > >
>> > > "zigzagdna@.yahoo.com" wrote:
>> > >
>> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
>> > > > table to a .txt file, When I imported table using DTS wizard. I do
>> > > > not get any errors. Yet, no data is loaded in the table. How can I
>> > > > troubleshoot the problem. Which logs I can look into to find the
>> > > > problem.
>> > > >
>> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
>> > > > 2000.
>> > > >
>> > > >|||I looked at DTS Wizard. Is there any way to export all the
tables/views/packages etc using one "command". I find DTS is table based, so
it only exports schema of a table and its data. How ablout views? I want to
export all the tables at the same time and then selectively import. Oracle
lets you do that. I am sure sql server also allows that, but how?
"Roy Harvey" wrote:
> THe import wizard allows you to specify the name of the table being
> created, or to specify an existing table. For an existing table you
> can specify if the table is to be truncated first, or the new data
> appended to the old. When you get to the window in the wizard with
> three columns - Source, Destination, Transform - you can click on
> Desination to make changes to the name or select an existing table
> from the list. Click on Transform to specify append or replace, and
> other options such as controlling the names and data types of the
> columns in the table being created.
> There is more to the import wizard, and I suggest it is worth your
> time playing with it for a while and exploring the options so you are
> familiar with what is there. The most important feature not mentioned
> so far is that you can save it as a DTS package and then edit it in
> the DTS interface - good for minor tweaking.
> Roy Harvey
> Beacon Falls, CT
> On Mon, 20 Aug 2007 18:48:02 -0700, Prem Mehrotra
> <PremMehrotra@.discussions.microsoft.com> wrote:
> >I finally figured the problem. Name of the export file has to match with
> >table being exported/imported. So when importing, it created a new table and
> >was putting rows
> >there. Also while in Enterpirse Manager, I did not see these tables
> >until today.
> >
> >It seems to see new tables screated by import, one must disconnect from
> >database and reconnect to it, that's why I had no idea what was going on.
> >
> >Thanks a lot for all your help.
> >
> >"John Bell" wrote:
> >
> >> Hi
> >>
> >> When you export using the Export wizard it will only create a data file, you
> >> can script the table creation script through Enterprise Manager or Query
> >> Analyser.
> >>
> >> Have you tables under a different schema with the same name?
> >>
> >> If you create a DTS package from the Import/Export Wizard you can then
> >> modify the package and use the DTS to log information and handle errors.
> >>
> >> John
> >> "Prem Mehrotra" wrote:
> >>
> >> > Import summary showed 2 rows were loaded, yet nothing was loaded.
> >> > When I did export from the wizard, it put 2 rows in the file, no create
> >> > table staement. My table employee alerady exists in the table, it has no
> >> > rows before import. I was hpoing it will have 2 rows after import, but it did
> >> > not. Is there anyway to turn on some tracing to see where the import had
> >> > problems.
> >> >
> >> > "John Bell" wrote:
> >> >
> >> > > Hi
> >> > >
> >> > > I assume you are using the import wizard in which case it should have shown
> >> > > a preview of the data. If you chose the run immediately option it should have
> >> > > shown the number if records inserted once the copy data step has competed.
> >> > > How many rows are you expecting from the file?
> >> > > Are you loading this into a new table?
> >> > > If the import summary showed no rows but you know the file has data then you
> >> > > may want to change the deliminators you have specified!
> >> > >
> >> > > John
> >> > >
> >> > >
> >> > >
> >> > > "zigzagdna@.yahoo.com" wrote:
> >> > >
> >> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> >> > > > table to a .txt file, When I imported table using DTS wizard. I do
> >> > > > not get any errors. Yet, no data is loaded in the table. How can I
> >> > > > troubleshoot the problem. Which logs I can look into to find the
> >> > > > problem.
> >> > > >
> >> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> >> > > > 2000.
> >> > > >
> >> > > >
>|||You need to use the "Transfer Objects and Data between SQL Server Databases" DTS task. This exports
the schemas information to a set of text files, exports the data to files, created the objects at
the other end and then imports the data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Prem Mehrotra" <PremMehrotra@.discussions.microsoft.com> wrote in message
news:C1314416-D22B-4C62-8AEE-49935AED8A01@.microsoft.com...
>I looked at DTS Wizard. Is there any way to export all the
> tables/views/packages etc using one "command". I find DTS is table based, so
> it only exports schema of a table and its data. How ablout views? I want to
> export all the tables at the same time and then selectively import. Oracle
> lets you do that. I am sure sql server also allows that, but how?
> "Roy Harvey" wrote:
>> THe import wizard allows you to specify the name of the table being
>> created, or to specify an existing table. For an existing table you
>> can specify if the table is to be truncated first, or the new data
>> appended to the old. When you get to the window in the wizard with
>> three columns - Source, Destination, Transform - you can click on
>> Desination to make changes to the name or select an existing table
>> from the list. Click on Transform to specify append or replace, and
>> other options such as controlling the names and data types of the
>> columns in the table being created.
>> There is more to the import wizard, and I suggest it is worth your
>> time playing with it for a while and exploring the options so you are
>> familiar with what is there. The most important feature not mentioned
>> so far is that you can save it as a DTS package and then edit it in
>> the DTS interface - good for minor tweaking.
>> Roy Harvey
>> Beacon Falls, CT
>> On Mon, 20 Aug 2007 18:48:02 -0700, Prem Mehrotra
>> <PremMehrotra@.discussions.microsoft.com> wrote:
>> >I finally figured the problem. Name of the export file has to match with
>> >table being exported/imported. So when importing, it created a new table and
>> >was putting rows
>> >there. Also while in Enterpirse Manager, I did not see these tables
>> >until today.
>> >
>> >It seems to see new tables screated by import, one must disconnect from
>> >database and reconnect to it, that's why I had no idea what was going on.
>> >
>> >Thanks a lot for all your help.
>> >
>> >"John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> When you export using the Export wizard it will only create a data file, you
>> >> can script the table creation script through Enterprise Manager or Query
>> >> Analyser.
>> >>
>> >> Have you tables under a different schema with the same name?
>> >>
>> >> If you create a DTS package from the Import/Export Wizard you can then
>> >> modify the package and use the DTS to log information and handle errors.
>> >>
>> >> John
>> >> "Prem Mehrotra" wrote:
>> >>
>> >> > Import summary showed 2 rows were loaded, yet nothing was loaded.
>> >> > When I did export from the wizard, it put 2 rows in the file, no create
>> >> > table staement. My table employee alerady exists in the table, it has no
>> >> > rows before import. I was hpoing it will have 2 rows after import, but it did
>> >> > not. Is there anyway to turn on some tracing to see where the import had
>> >> > problems.
>> >> >
>> >> > "John Bell" wrote:
>> >> >
>> >> > > Hi
>> >> > >
>> >> > > I assume you are using the import wizard in which case it should have shown
>> >> > > a preview of the data. If you chose the run immediately option it should have
>> >> > > shown the number if records inserted once the copy data step has competed.
>> >> > > How many rows are you expecting from the file?
>> >> > > Are you loading this into a new table?
>> >> > > If the import summary showed no rows but you know the file has data then you
>> >> > > may want to change the deliminators you have specified!
>> >> > >
>> >> > > John
>> >> > >
>> >> > >
>> >> > >
>> >> > > "zigzagdna@.yahoo.com" wrote:
>> >> > >
>> >> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
>> >> > > > table to a .txt file, When I imported table using DTS wizard. I do
>> >> > > > not get any errors. Yet, no data is loaded in the table. How can I
>> >> > > > troubleshoot the problem. Which logs I can look into to find the
>> >> > > > problem.
>> >> > > >
>> >> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
>> >> > > > 2000.
>> >> > > >
>> >> > > >

Import data from text to MS SQL Server. ! Need Help!

Hi
I need to know how to import data from .txt to the MS SQL Server. IT is really important and i have 1 hour only, any help ...... appreciated.
\ThanksDTS import data

import and text qualifier

Hi

How can I import txt files, in which data sometimes can contain
the letter that is used as text qualifier

Example txt file: 'abc',123, 'john's',12

Here the word -john's- contains the same letter as the text qualifier I have to use.

Can it be true, that this can't be done i MS SQL, when it can be done in MS Excel, Paradox etc..

Regards
Carsten H.When you need to import a delimter character like the apostrophe in Transact-SQL, all computer languages need some kind of escape character to prefix the delimiter. In C or C++, you often use the backslash (\} for an escape character.

This is done automagically for you when using the text file import utilities like BCP, and manually when using Transact-SQL syntax. What tool are you using?

-PatP|||Weel, I'm not an expert at sql, so I'm using DTS and I have tried different kind of stuff using BULK INSERT|||To make the process a lot more simple, just import the text file into an Excel spreadsheet, then use DTS to import it into SQL Server from the spreadsheet's XLS file.

There are certainly ways that it can be done using just the SQL tools, but it sounds like you are a lot more comfortable using Excel, so that is probably the best route for you.

-PatP|||HATE Excel

So the answer must be, that I still have to use sql in MySql, and then throw it all back to my main db (MS SQL)

IMPORT A .TXT FILE INTO TABLE

I am looking to import a text file into a already
exisiting table(no data) via syntax SQL. I am just not
sure of the code.
could it be something like:
INSERT MY_FILE.TXT INTO MY_TABLE
I really appriciate your helpThis is a multi-part message in MIME format.
--=_NextPart_000_00E3_01C34C6A.735A2DC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Check out BULK INSERT or bcp in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"jon" <jon@.mybossisken.com> wrote in message =news:014701c34c8b$573349d0$a401280a@.phx.gbl...
I am looking to import a text file into a already exisiting table(no data) via syntax SQL. I am just not sure of the code.
could it be something like:
INSERT MY_FILE.TXT INTO MY_TABLE
I really appriciate your help
--=_NextPart_000_00E3_01C34C6A.735A2DC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out BULK INSERT or bcp in the BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"jon" wrote in message news:014701c34c8b$57=3349d0$a401280a@.phx.gbl...I am looking to import a text file into a already exisiting table(no =data) via syntax SQL. I am just not sure of the code.could it be =something like:INSERT MY_FILE.TXT INTO MY_TABLEI really appriciate =your help

---

--=_NextPart_000_00E3_01C34C6A.735A2DC0--