Solved

multiple text file import into MSSQL

Posted on 2007-03-22
2
1,315 Views
Last Modified: 2008-06-08
I have multiple TAB delimited text files (first row has field names) that I need to import to MSSQL... I don't want to do them one by one.
How can I do multiple file import?
0
Comment
Question by:hbojorquezgeek
2 Comments
 
LVL 16

Expert Comment

by:AdamRobinson
ID: 18786169
Perhaps use Perl or PHP to automatically script them?   Read the directory, save the file names in an array, loop through the array performing the mysql connections and the inline import.

0
 

Accepted Solution

by:
magicmike earned 500 total points
ID: 20023095
I do a shell command to view all files in a directory and insert into a temp table.
something like this
  DECLARE  @query VARCHAR(1000)
  CREATE TABLE #x (
    Name VARCHAR(200))

  SET @query = 'master.dbo.xp_cmdshell "dir c:\files\*.csv /b"'
 
  INSERT #x
  EXEC( @query)

I run through and insert all into a temp table using OPENROWSET
 SET @Query = 'INSERT into #temptable select * from OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=' + @Filepath + ';'', ''select * from ' + @Filename + ''') Rowset_1'

after i do that, I take the data, change whats needed then insert into the necessary table.

Sorry I do not have a cleaned up script for you, but i just stumbled on your question online and seen it had no answer.  I hope it works out for you.

0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now