How to import a text database file into SQL Server Management Studio Express

I have a text file with a bunch of zip codes:

99546,"AK","Adak",162.70,55.20,.00
99551,"AK","Akiachak",161.39,60.89,.00
99552,"AK","Akiak",161.19,60.89,.00
99553,"AK","Akutan",165.78,54.14,.00

...............

I want to import this in Sql Server Express. Is it possible to do this using Management Studio Express? If not, does anyone know a script that would import the data?

Hi Banks,

the easiest way to do it is to convert your text files to .csv file. and in the first row of the csv file insert relevent column names. You can yous DTS service to populate the data by specifying the csv file as a datasource. the DTS service identifies 1st row of the csv file as column names and you can do the mapping.

Cheers,

Raj

|||

HI

A single query will import the text file content into the Table.

BULK INSERT [TableName]
FROM 'c:\ZipDetails.txt'
WITH (FIELDTERMINATOR = ',')

You can run this SQL Query directly from the Query Analyzer

|||

Both seem like good answers but didn't work for my situation. I tried the Bulk upload but my web host provider disabled that command and SQL Server Express doesn't come with DTS.

What I did instead was I ran find and replace on the text file and inserted "insert into zips values " at the beginning of each line. I then copied it into query analyzer and ran about 40,000 of the insert commands on my database and that did the trick.

How to import a bunch of files?

I have a large number of text files that I want to import into a database. I
have created a DTS package that knows how to import and transform the data
so that it can be imported but that seems to be hard coded to a particular
file. Doing this by hand 18 thousand times is not something I want to do. Is
there a command line interface that I can call from either DOS or WSH to
import all of this data? I'm thinking that something like
for %%f in (*.txt) do <import %%f>
would be great. Anyone have a suggestion on how this could be done?
Richard Lewis Haggard
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
For SQL2005 you can use the ForEach Loop file enumerator
Looping over files with the Foreach Loop
http://www.sqlis.com/default.aspx?55
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>I have a large number of text files that I want to import into a database.
>I have created a DTS package that knows how to import and transform the
>data so that it can be imported but that seems to be hard coded to a
>particular file. Doing this by hand 18 thousand times is not something I
>want to do. Is there a command line interface that I can call from either
>DOS or WSH to import all of this data? I'm thinking that something like
> for %%f in (*.txt) do <import %%f>
> would be great. Anyone have a suggestion on how this could be done?
> --
> Richard Lewis Haggard
>
|||I ended up doing it with a dtsrun package called from a pair of batch files.
In this case, c:\StockWizPro98\OUTPUT is the destination directory that
receives periodic updates and data drops. I decided to make a temp directory
that will have a single file in it and to create a DTS package that knows
how to import data from it. The
first batch file iterates through all of the input files, copies and renames
them to what the DTS package is looking for and calls the package to import
the data. Simple.
1.bat
pushd c:\StockWizPro98\OUTPUT
for %%f in (*.txt) do call 2 %%f
popd
2.bat
pushd c:\StockWizPro98\OUTPUT\temp
copy /y ..\%1 temp.txt
dtsrun /s (local) /n StockImport /e
popd
Richard Lewis Haggard
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23aBh2ff%23FHA.740@.TK2MSFTNGP12.phx.gbl...
> Looping, Importing and Archiving
> http://www.sqldts.com/default.aspx?246
> For SQL2005 you can use the ForEach Loop file enumerator
> Looping over files with the Foreach Loop
> http://www.sqlis.com/default.aspx?55
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
> news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>