Thursday, March 22, 2012

Converting .dbf to sql server table...batch file? command line?

My client will be receiving a .dbf file which needs to be uploaded
into a sql server database table (as an append) every week. They are
NOT computer savvy and I would like to automate this process rather
than go into enterprise manager and run data transformation.

Is there any way to write a batch file or a set of command lines which
will do this?

Thanks.

Monicamonica@.datashark.net (Monica J. Braverman) wrote in message news:<d70cd025.0310081033.375fbc9b@.posting.google.com>...
> My client will be receiving a .dbf file which needs to be uploaded
> into a sql server database table (as an append) every week. They are
> NOT computer savvy and I would like to automate this process rather
> than go into enterprise manager and run data transformation.
> Is there any way to write a batch file or a set of command lines which
> will do this?
> Thanks.
> Monica
DTS package would probably be the easiest solution. Create the package
as an import from the .dbf into the approriate table and then run it
with each new .dbf received. If running DTS packages is a bit
'complex' for your users you could wrap it up in an applicaion/wizard
(eg in VB) and execute it that way.|||monica@.datashark.net (Monica J. Braverman) wrote:
>My client will be receiving a .dbf file which needs to be uploaded
>into a sql server database table (as an append) every week. They are
>NOT computer savvy and I would like to automate this process rather
>than go into enterprise manager and run data transformation.
>Is there any way to write a batch file or a set of command lines which
>will do this?

You might create a DTS package on the server, then create a batch file
to access it that calls the DTSRUN.exe program. I have a batch file
with this as the command line:
dtsrun /e /n"dtspackagename" /sSQLServerName and it works very well.
hth,
Myron|||Hi,
you can automate your process using a command prompt utility named
"bcp"; if you try to execute this command you get the syntax:

C:\>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide
character type]
[-N keep non-text native] [-V file format version] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row
terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional
enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]

Another way is to use the T-SQL command "BULK INSERT".
You can find explanations on your MS-SQL documentation.

Bye.

monica@.datashark.net (Monica J. Braverman) wrote in message news:<d70cd025.0310081033.375fbc9b@.posting.google.com>...
> My client will be receiving a .dbf file which needs to be uploaded
> into a sql server database table (as an append) every week. They are
> NOT computer savvy and I would like to automate this process rather
> than go into enterprise manager and run data transformation.
> Is there any way to write a batch file or a set of command lines which
> will do this?
> Thanks.
> Monicasqlsql

No comments:

Post a Comment