JWColby
jwcolby at colbyconsulting.com
Tue May 8 08:25:00 CDT 2007
Billy,
The issue is not "handling fixed width" but in stripping off the trailing
spaces in the process. The environment that I work in isn't "import a file
and use it", but rather "import 56 files containing a HUNDRED MILLION
records of 150 (or SEVEN HUNDRED) fields into a single table and use it".
The SPACES in this last file were more than 40% of the total volume of the
file.
I CANNOT import all the spaces and then go back and strip them off in situ.
The extra space in the database and the extra time to do the strip makes
that a non starter.
And this is where (so far) the bulk insert method has failed me. Nobody has
shown me how to strip the spaces on the way in, and still keep the time up.
I have written an external application, in Access / VBA but headed towards
VB.NET which opens these HUGE (often four GIGABYTE) files (remember there
were 56 of these files in the last batch), and then reads each line, pulls
each field out, trims off the spaces, assembles it into a string with a
field delimiter, and writes each line back to a new file. That new file
imports cleanly into SQL Server using a BULK INSERT sql statement embedded
in a sproc, which I am confident I will get working from my same external
application to allow me to have that one app strip and import into SQL
Server, logging all results.
SQL Server may very well be able to do all this stuff in one fell swoop, who
knows? Certainly not me.
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Billy Pang
Sent: Tuesday, May 08, 2007 1:11 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Bulk insert
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 _______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com