Jim Dettman
jimdettman at verizon.net
Tue May 1 09:41:50 CDT 2007
John, Nice work. As an aside, I'm seeing the vertical pipe character used more and more as a delimiter. Never seems to occur normally in data and it still leaves the file fairly easy to read. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Tuesday, May 01, 2007 10:21 AM To: 'Access Developers discussion and problem solving'; dba-sqlserver at databaseadvisors.com Subject: [AccessD] Importing large data files I have been struggling to get large data files to import into SQL Server in a manner that won't take weeks to complete. I was trying to use CSV files to get around the trailing (and occasionally leading) spaces in the data fields caused by fixed width / space padded fields in the source text files. So I created a program in MS Access to read the source file, line by line, carve out each data element, strip the spaces, then when an entire line was assembled, write that back out to a new csv file. The next issue I ran into is that SQL Server doesn't know how to deal with text qualifiers, which are commonly used in CSV files to encapsulate commas inside of data, IOW if a comma is the delimiter, then the data is enclosed in quotes: "51 Some Street, Ste2001", "Some City". In THIS CASE, since I control the intermediary file, I am able to change from a space delimiter to a | delimiter and remove the quotes. Thus the previous example turns into 51 Some Street, Ste2001|Some City| This can be imported into SQL Server without an issue since I can specify the FIELDTERMINATOR parameter to the bulk insert in a query. Using bulk insert pulled my import up from 300-400 records per second to almost one thousand records per second. Not blazing but with 100 million records to import, it is a significant improvement in total time to import. 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