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

Groups > comp.databases.ms-sqlserver > #1756

Re: load multiple txt files to sql server

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web