Charlotte Foust
cfoust at infostatsystems.com
Mon Apr 23 19:01:00 CDT 2007
Have you thought about leaving town with no forwarding address instead??
LOL
Charlotte Foust
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Monday, April 23, 2007 4:13 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] using a saved SSIS with VB.Net
Well, it took about three hours to build an Access VBA system to turn
the fixed width file into a CSV with the leading / trailing spaces
stripped off of every field. The code runs about 2.5 K records / second
for the conversion portion. I name the CSV file with a fixed name and
linked the file to Access, so once that is done I run a simple append
query that pulls the data back out of the CSV and appends it into the
SQL Server table.
Unfortunately the append to SQL process is running at a little better
than 500 records / second. I have to figure that out or this will take
weeks.
I have ~ 100 million records to convert so...
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Monday, April 23, 2007 8:09 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] using a saved SSIS with VB.Net
Yea, a code "preprocessor" just seems to be the answer. No one on these
lists have ever proposed a good (non code) solution for this particular
problem.
The fixed width flat file just sucks as an import specification. It
requires an external file to tell you the field names and field widths.
Even once you know that it leaves you with data in the fields with
spaces between the end of the data and the end of the field width, i.e.
'John' in a
first name field 25 spaces wide is actually 'John '.
Unless you strip off the spaces you end up with a HUGE database where
indexes are huge and matches with external data are difficult.
But it is what is used in the industry I am dealing with.
Suck it up and move on I guess.
John W. Colby
Colby Consulting
www.ColbyConsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, April 23, 2007 5:16 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] using a saved SSIS with VB.Net
Hi John
I think that wizards are mostly for one-time or simple repeated tasks.
However, do you remember my observation from last year:
<quote>
>> 2006-05-05 20:59
Subject: MySQL insert records
When inserting records by a pass-through query from Access, I've found
that reading the records off a text file using
LOAD DATA INFILE
'../data/test.txt'
INTO TABLE
tempload
into an empty non-indexed table runs at about 50,000 records per second
even using very modest hardware.
This is way faster than any normal "Insert Into ... " I've tried.
However, it's a bit clumsy first to have to write the data to a file.
Any comments or suggestions?
</quote>
You should be able to do so if not from T-SQL then from Access, and then
run a modifiable import and append query in SQL Server which reads the
data from the MySQL table and appends them to the internal SQL Server
table.
But - as you have already done the mechanics for converting the fixed
length records files to csv files - that route may be faster for you; at
least it is proven.
/gustav
>>> jwcolby at colbyconsulting.com 23-04-2007 05:56 >>>
I saved an import spec from the import wizard in SQL Server 2005. I now
need to use that import spec in VB.Net, but I need to modify it. I need
to change the source file, make it an append instead of a make table /
append, and I need to modify the datatype from NVarchar to varchar. Of
course I can simply go back in to SQL Server and run the import wizard
again, however this is a fixed width file which means recreating all of
the field name / field width info a second time.
Is it possible to just do these mods on the existing import spec or
should I just bite the bullet and rebuild. I have ~ sixty more files to
import into the table and I really don't want to do this 60 times. The
last time I did this I actually built an Access app to create .csv files
from the original fixed width files. It seems like the long way around
the barn but then again I do not see how this wizard is really very
useful in this case.
Anyone?
John W. Colby
Colby Consulting
www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com