Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1751 > unrolled thread

load multiple txt files to sql server

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  load multiple txt files to sql server tad na <teddybubu@gmail.com> - 2014-05-23 19:42 -0700
    Re: load multiple txt files to sql server Erland Sommarskog <esquel@sommarskog.se> - 2014-05-24 09:45 +0200
      Re: load multiple txt files to sql server bradbury9 <ray.bradbury9@gmail.com> - 2014-05-24 04:20 -0700
        Re: load multiple txt files to sql server tad na <teddybubu@gmail.com> - 2014-05-24 09:32 -0700
          Re: load multiple txt files to sql server Erland Sommarskog <esquel@sommarskog.se> - 2014-05-24 21:57 +0200
            Re: load multiple txt files to sql server bradbury9 <ray.bradbury9@gmail.com> - 2014-05-25 03:21 -0700
        Re: load multiple txt files to sql server rja.carnegie@gmail.com - 2014-05-24 15:30 -0700
          Re: load multiple txt files to sql server tad na <teddybubu@gmail.com> - 2014-05-24 20:50 -0700
          Re: load multiple txt files to sql server Erland Sommarskog <esquel@sommarskog.se> - 2014-05-25 11:16 +0200

#1751 — load multiple txt files to sql server

I need to import hundreds of data txt files to sql server 2008.
All the txt files are in folders.  The folders names are country names.
Obviously, I don't want to do this manually.
This is what I want:

1. import all country/folder names into sql server and create a table 
     example: dbo.China
2. import all txt files in country/folder to specific dbo in sql server

Is this possible to do and is it done with stored procedures??
How do I do this?  I am a beginner so any help is appreciated.

[toc] | [next] | [standalone]

#1752

tad na (teddybubu@gmail.com) writes:
> I need to import hundreds of data txt files to sql server 2008.
> All the txt files are in folders.  The folders names are country names.
> Obviously, I don't want to do this manually.
> This is what I want:
> 
> 1. import all country/folder names into sql server and create a table 
>      example: dbo.China
> 2. import all txt files in country/folder to specific dbo in sql server
> 
> Is this possible to do and is it done with stored procedures??
> How do I do this?  I am a beginner so any help is appreciated.
 
Is the structure of the text files the one and the same? In such case you 
should not create a table per file or folder, but a single table. The 
folder/country name should probably be a column in that table.

As for organising the import, you would have to write a program or use
SQL Server Integration Services (which I am not familiar with myself).


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1753

El sábado, 24 de mayo de 2014 09:45:59 UTC+2, Erland Sommarskog  escribió:
> tad na (teddybubu@gmail.com) writes:
> 
> > I need to import hundreds of data txt files to sql server 2008.
> 
> > All the txt files are in folders.  The folders names are country names.
> 
> > Obviously, I don't want to do this manually.
> 
> > This is what I want:
> 
> > 
> 
> > 1. import all country/folder names into sql server and create a table 
> 
> >      example: dbo.China
> 
> > 2. import all txt files in country/folder to specific dbo in sql server
> 
> > 
> 
> > Is this possible to do and is it done with stored procedures??
> 
> > How do I do this?  I am a beginner so any help is appreciated.
> 
>  
> 
> Is the structure of the text files the one and the same? In such case you 
> should not create a table per file or folder, but a single table. The 
> 
> folder/country name should probably be a column in that table.
> 
> As for organising the import, you would have to write a program or use
> 
> SQL Server Integration Services (which I am not familiar with myself).
> -- 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se

SSIS is the way to go, but wont be a easy task, you have several tasks that can help you achieve it. You will have to play around with script task, foreach loops, variables and source / destination tasks.

Did, long time ago, the other way. from a single excel store in database and loop database views to generate excels that one app would send via email.

Some task worth mention:

foreach loop task
script task

http://msdn.microsoft.com/en-us/library/ms139892.aspx

[toc] | [prev] | [next] | [standalone]

#1754

All txt files have the same column structure so that is a big relief.
I like the idea of creating one table but the problem is each txt file does not have a column with the country name.  How can I insert a new column with the country name when importing to sql server?

[toc] | [prev] | [next] | [standalone]

#1755

tad na (teddybubu@gmail.com) writes:
> All txt files have the same column structure so that is a big relief. 
> I like the idea of creating one table but the problem is each txt file
> does not have a column with the country name.  How can I insert a new
> column with the country name when importing to sql server? 
> 

The program that controls the load, would real the file and add the country 
name when inserting the rows. If you use BCP/BULK INSERT, you would have to 
change the default before each file. 

There are plenty of options available, and which one you chose is largely
up to your skills. That is, if you know C#, you write a C# program. If 
you VBscript you do it VBscript etc.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1759

El sábado, 24 de mayo de 2014 21:57:24 UTC+2, Erland Sommarskog  escribió:
> tad na (teddybubu@gmail.com) writes:
> 
> > All txt files have the same column structure so that is a big relief. 
> 
> > I like the idea of creating one table but the problem is each txt file
> 
> > does not have a column with the country name.  How can I insert a new
> 
> > column with the country name when importing to sql server? 
> 
> > 
> 
> 
> 
> The program that controls the load, would real the file and add the country 
> 
> name when inserting the rows. If you use BCP/BULK INSERT, you would have to 
> 
> change the default before each file. 
> 
> 
> 
> There are plenty of options available, and which one you chose is largely
> 
> up to your skills. That is, if you know C#, you write a C# program. If 
> 
> you VBscript you do it VBscript etc.
> 
> 
> 
> -- 
> 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se

If you want to do it just using sql server tools, go SSIS. If you dont mind programming a bit, write a program. Depends on what you are more comfortable with.

If you go the SSIS way, you will have to use the scripting task, loops, and ssis variables. If you use a programming language you will work with same tools plus it may be easier to debug/test (if you use decent programming tools xD). 

I am very familiar with programming cause I work as a developer and I like C# a lot.

[toc] | [prev] | [next] | [standalone]

#1756

On Saturday, 24 May 2014 12:20:52 UTC+1, bradbury9  wrote:
> El sábado, 24 de mayo de 2014 09:45:59 UTC+2, Erland Sommarskog  escribió:
> > tad na (teddybubu@gmail.com) writes:
> > > I need to import hundreds of data txt files to sql server 2008.
> > > All the txt files are in folders.  The folders names are country names.
> > > Obviously, I don't want to do this manually.
> > > This is what I want:
> > > 
> > > 1. import all country/folder names into sql server and create a table 
> > >      example: dbo.China
> > > 2. import all txt files in country/folder to specific dbo in sql server
> > > 
> > > Is this possible to do and is it done with stored procedures??
> > > How do I do this?  I am a beginner so any help is appreciated.
> > 
> > Is the structure of the text files the one and the same? In such case you 
> > should not create a table per file or folder, but a single table. The 
> > folder/country name should probably be a column in that table.
> > As for organising the import, you would have to write a program or use
> > SQL Server Integration Services (which I am not familiar with myself).
> 
> SSIS is the way to go, but wont be a easy task, you have several 
> tasks that can help you achieve it. You will have to play around 
> with script task, foreach loops, variables and source / destination
> tasks.
> 
> Did, long time ago, the other way. from a single excel store 
> in database and loop database views to generate excels that one 
> app would send via email.
> 
> Some task worth mention:
> 
> foreach loop task
> script task
> 
> http://msdn.microsoft.com/en-us/library/ms139892.aspx

Are those SSIS features?  In my very limited experience of it
(our infrastructura administrator apparently believed it was 
evil, so carefully removed it from all of our servers) 
SSIS itself could read data from a single text file, but
repeating the operation for another file would involve 
either addressing the SSIS process package with some 
scripting language to change the file name stored in it,
or copying each file in turn to be "SsisFile.txt" and
then importing that.

Otherwise, ... if I had this to do, one part of my 
approach would be to collect all of the file names to
be imported without making that a SQL Server program.
Or if you can use master.dbo.xp_cmdshell ...  At the 
operating system shell prompt, you can probably do this:

DIR /S /B "E:\Text\*.txt" >"E:\Text\ListOfFiles.txt"

This will store every file name ending in .txt 
underneath folder E:\Text in a file named ListOfFiles.txt 
- including ListOfFiles.txt itself, probably.
You can choose how to get around that, e.g. name it
NotThisOne.txt ...

So, you can import just the file names into a table,
and then use a cursor (I like a cursor variable)
to pick each one in turn.  Or, with either command-line 
BCP, or BULK INSERT - I've forgotten which I actually 
used in a particular process - it was useful to import 
into a view that only has one column to receive the 
imported data, but has more columns to use for 
processing data.  In particular, a row ID 
identity column.

As for the nation value that isn't in the data file itself,
I think you can set a constant in a format file that
controls a BCP data import process, but in my experience
the format file is difficult to construct - although
you can use the tool to create one from an existing
table, and then use it with other tables.

But this doesn't exist if I am reading this correctly
<http://msdn.microsoft.com/en-us/library/ms179250.aspx>
(SQL Server 2014) which says "A data file can contain 
fewer fields than the number of columns in the table 
only if the skipped columns are nullable and/or have 
default value."  i.e. you can't write a default value
into the format file to use instead.

But if it is an option, you could set up a format file for 
each nation, by hand, containing the correct country name 
as a column, otherwise identical; import each data file using
the appropriate format file.  This depends on whether 
you have to support 10 countries or 100 countries.

Or, import one file, leaving the country column NULL,
then UPDATE on the table setting the correct country
name "WHERE CountryName IS NULL".  Then, the next file.
The main concern there is that the UPDATE is a separate
logged operation, causing the transaction log file to
bloat up, and may be not particularly efficient.
The technique could be varied to improve efficiency.

Or create a different DEFAULT constraint on the 
[CountryName] column before importing each file.
That won't have the drawbacks that come with 
performing a separate UPDATE to set the missing
country name.

[toc] | [prev] | [next] | [standalone]

#1757

you guys are good.  
you have given me a lot to think about.
Time to go to work.

[toc] | [prev] | [next] | [standalone]

#1758

 (rja.carnegie@gmail.com) writes:
> As for the nation value that isn't in the data file itself,
> I think you can set a constant in a format file that
> controls a BCP data import process, 

No, you can't.

> but in my experience the format file is difficult to construct 

Yes, as long as you have not learnt the format.

> But this doesn't exist if I am reading this correctly
><http://msdn.microsoft.com/en-us/library/ms179250.aspx>
> (SQL Server 2014) which says "A data file can contain 
> fewer fields than the number of columns in the table 
> only if the skipped columns are nullable and/or have 
> default value."  i.e. you can't write a default value
> into the format file to use instead.
> 

Correct. But you can change the default value for each column before
you load.

Which still it is a bit of a kludge. I would rather solve it with 
client-side language. Just because you can do it in T-SQL does not
mean that you have to.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web