Billy Pang
tuxedoman888 at gmail.com
Tue May 8 00:10:34 CDT 2007
John:
This is a bit late so not sure if you resolved your issues with BCP yet.
However, I would like to point out that bcp does indeed handle fixed width
datatypes. See following illustration:
1) create a database called test
2) run the following script to create a table called table1
CREATE TABLE [Table1] (
[asdf] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[asdf2] [int] NULL ,
[asdf3] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[asdf4] [int] NULL ,
[asf] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[asf]
) ON [PRIMARY]
) ON [PRIMARY]
3) populate the table with 2 records
insert into table1 values('1',2,'3',4)
insert into table1 values('a',5,'b',6)
4) ok.. now use bcp to export the data using trusted connection
bcp "test..table1" out "c:\yo_exported_data.txt" -T -c
there are two records in the data file. note: fixed width preserved!
5) use bcp to create a format file
bcp "test..table1" format -T -c -f "c:\yo_format_file.txt"
6) now use bcp to import the records you just exported using the format file
you just created
bcp "test..table1" in "c:\yo_exported_data.txt" -T -c -f
"c:\yo_format_file.txt"
7) count the records in the table
select count(*) from table1
there are four records
8) view the records in the tables.
select * from table1
sometimes bcp is a bit of pita but it works like a dream once you get it
working. hardest part is creating the format file but once you get bcp to
do that for you, the rest pretty much writes itself.
HTH
Billy
On 5/1/07, David Lewis <DavidL at sierranevada.com> wrote:
>
>
>
> John:
>
> There are a few websites concerned with sql server, with active forums.
> I recommend you check them out. One is sqlcentral.com, the other is
> sswug.
>
> You are right, access is a 'toy' when compared to sql server. One
> problem you are having is that you don't have the luxury of a learning
> curve -- but that is not the fault of the tool. Hang in there. D
>
>
> And that is exactly what I am doing and it is soooooo KLUDGY!!!
>
> I am running a program I wrote to read the data out of the original
> fixed
> width file because SQL Server won't handle fixed width files and strip
> off
> the spaces. How incredibly stupid is THAT? Is there ANYONE out there
> who
> WANTS those spaces? So I am already using KLUDGE to get data into SQL
> Server. Now I export it out to a perfectly valid CSV file only to
> discover
> that SQL Server BCP and Bulk Insert don't even look at (understand)
> quotes
> around comma delimited fields.
>
> But ACCESS does. But Access is a TOY remember? What exactly does that
> make
> SQL Server that it needs a toy to feed it data?
>
> This has been an exercise in discovering just how brain dead the data
> import
> processes are (or can be anyway) for SQL Server. This is NOT rocket
> science. I am able to write a utility to open / import / mangle /
> export it
> back out to another file in VBA. How tough can it be to do this import
> inside of SQL Server natively?
>
> I have no idea how widespread this kind of file is but I can tell you
> that
> that is all I see EVER in the industry I am dealing with. HUGE files,
> fixed
> width, space padded right. And I can tell you they have been a royal
> PITA
> to get into SQL Server.
>
> At least now I have my own utility that can get I these input files into
> the
> format I need, even if it is in ACCESS/ VBA. My next step is to port
> this
> to VB.Net so that I can do it a little more "natively". Once I get a
> little
> more familiar with VB.Net I want to look at storing the data right into
> a
> recordset in ADO and then write that back to SQL Server. If that is too
> slow (I suspect that it will be) then I can still do what I do now and
> import / mangle / write to file and then run a stored procedure to do a
> Bulk
> Insert from the file I create.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
--
Billy Pang
http://dbnotes.blogspot.com/
"Once the game is over, the King and the pawn go back in the same box." -
Italian proverb