Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Importing multiple .txt files into one table - no SSIS Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 8:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
I wish to import multiple text files into one table in SQL Server 2008 but I don't have SSIS capability. I wish for the format of the file to stay the same (no formatting or modification of data) and I wish for each file to be dumped into its own row under one column heading (e.g. 'Data')

The link below is along the right lines of what I wish to do but I cannot work out how to take out the part that delimits the data as I'm fairly new to this.

http://www.nigelrivett.net/SQLTsql/ImportTextFiles.html

Any help greatly appreciated.
Post #1351008
Posted Tuesday, August 28, 2012 9:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 12,952, Visits: 32,476
it's fairly easy; you would want to use BULK INSERT to bring the text files into a SQL table...but you have to know the layout of the data, and create teh destination table (whether it's a "staging" table or the real table) before you do anything else.

you also need to know how the file is delimtied...comma delimited , tab delimited, or fixed width, or some custom delimiter.

the example you pointed out is pretty good; can you be more specific about where you are having trouble?
can you give more specifics about your text files, how they are delimited, etc?
what table have you created for it so far?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1351025
Posted Tuesday, August 28, 2012 9:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 12,952, Visits: 32,476
here's an example i've posted lots of times.
this inserts allt eh data into a table named "BULKACT, that has a single column "RAWDATA"

it imports all the files in four different directories, if htey end in .txt.

the field delimiter is something I'm sure doesn't exist in my data: four pipe characters in a row.
BULK INSERT MULTIPLE FILE 
-a table to loop thru filenames drop table ALLFILENAMES
CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))
--the source table: yours already exists, but needed for this example.
CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))
--some variables
declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@cmd varchar(1000)
--get the list of files to process:
--#########################################
SET @path = 'C:\DB\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB2\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB3\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null

SET @path = 'C:\DB4\'
SET @cmd = 'dir ' + @path + '*.txt /b'
INSERT INTO ALLFILENAMES(WHICHFILE)
EXEC Master..xp_cmdShell @cmd
UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null
--#########################################
--cursor loop
declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.txt%'
open c1
fetch next from c1 into @path,@filename
While @@fetch_status <> -1
begin
--bulk insert won't take a variable name, so make a sql and execute it instead:
set @sql = 'BULK INSERT BULKACT FROM ''' + @path + @filename + ''' '
+ ' WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = ''||||'',
ROWTERMINATOR = ''\n'',
FIRSTROW = 1
) '
print @sql
exec (@sql)

fetch next from c1 into @path,@filename
end
close c1
deallocate c1



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1351028
Posted Tuesday, August 28, 2012 9:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
I've been trying to use BULK INSERT but I'm getting this error (which I'm in the process of trying to solve)

Cannot bulk load because the file "C:\users\xxx" could not be opened. Operating system error code 5(Access is denied.).

The text files I'm trying to import are of various lengths split by pipes (|) but I wish for the data to not be changed (still want pipes included, no delimiting at all) so I only have one column in my table called 'Data' (varchax(MAX)) that I am dumping it into.

The trouble I'm having with the code on the link is that it delimits the data which I do not wish to do but I can't work out which bit to take out to stop it from delimiting, as I don't fully understand the code. It would work perfectly for what I want though, so I'd like to stick with it if possible.
Post #1351035
Posted Tuesday, August 28, 2012 10:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 12,952, Visits: 32,476
for the delimiter issue, use something you KNOW will not exist in the data; a tilde ~ for example.
then everything will come in as asingle column of wide data.
For the permissions issue,
to access resources outside of a SQL database, (local files and folders, network shares, etc, , SQL uses a DIFFERENT account than the one you are logged in with/would typically expect.


SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

or if the above was blank, the account in services:


That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.
As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.

Once that is done, and you stop and start the SQL service to make it use that account instead of old running values, your linked server/xp_cmdshell would work.


you can prove this is the issue by simply putting in your credentials, with your domain account and password, and confirm the external object you were trying to access/use works when SQL is run your credentials, so you'd know you need a domain account to access the resource.
[/quote]


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1351108
Posted Wednesday, August 29, 2012 2:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
Thanks, that's really helpful. I'm giving it a go today.
Post #1351471
Posted Monday, May 26, 2014 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 19, 2014 2:29 PM
Points: 1, Visits: 9
Hi ..

I have tried the code. But the whichfile column doesnt populate the file name. Its showing access denied.
Can you please help ?
Post #1574605
Posted Monday, May 26, 2014 7:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 12,952, Visits: 32,476
koyyatrouser (5/26/2014)
Hi ..

I have tried the code. But the whichfile column doesnt populate the file name. Its showing access denied.
Can you please help ?


Access denied is typically because the account in the servicesndoesnt have access.... examples are when you put files in c:\users\specificperson\desktop or my documents and the account cannot access. Move the files to a non private directory would fix it.

If the account does have access then itnmight be because thenfile is open in excel at the same time this process isntrying to open it as well.
If you give the exact error and path to files we could help further.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1574630
Posted Tuesday, May 27, 2014 7:58 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 362, Visits: 1,086
Make sure where SQL thinks c:\users\xxx is as it could be looking at the SQL server not the system where you are running the code.
Post #1574791
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse