Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Very Tough CSV Data Import to SQL 2005 DB Table Ch

Author  Topic 

dsabjp
Starting Member

1 Post
Posted - 2010-07-15 : 00:18:34
I'm not sure this is the right forum, please forgive if not in the cprrect one. I have a tough CSV file format that I want to import into SQL 2005 DB Table.

I would prefer to use Scheduled Job running stored procedure to do this rather than SSIS.

Sorry for the size of the examples, they are a little too difficult to cut back much.

Here is csv file format: (note the first columns are standard the remain can range from 12 - 160 columns - more like array format for those X columns)
Date - Time,Batch Number,Batch Product # (10 products per batch),Number of Tracks = N (measurement tracks),Number Of Measurement Locations Per Track = L,Track 1 Measurement 1,Track 1 Measurement 2,Track 1 Measurement 3,Track 1 Measurement 4,Track 2 Measurement 1,Track 2 Measurement 2,Track 2 Measurement 3,Track 2 Measurement 4,Track 3 Measurement 1,Track 3 Measurement 2,Track 3 Measurement 3,Track 3 Measurement 4,Track N…. Measurement… L
"07/14/2010 08:10:00",1,1,3,4,0.24,0.29,0.25,0.27,0.23,0.25,0.23,0.25,0.29,0.23,0.25,0.25,…
"07/14/2010 08:10:00",1,2,3,4,0.26,0.27,0.24,0.26,0.28,0.26,0.28,0.24,0.27,0.28,0.24,0.26,…
"07/14/2010 08:10:00",1,3,3,4,0.23,0.24,0.25,0.29,0.26,0.28,0.26,0.25,0.24,0.26,0.25,0.28,…
"07/14/2010 08:10:00",1,4,3,4,0.24,0.23,0.26,0.27,0.24,0.26,0.24,0.26,0.23,0.24,0.26,0.26,…
"07/14/2010 08:10:00",1,5,3,4,0.28,0.28,0.28,0.24,0.25,0.23,0.25,0.28,0.28,0.25,0.28,0.23,…
"07/14/2010 08:10:00",1,6,3,4,0.22,0.26,0.26,0.23,0.29,0.25,0.29,0.26,0.26,0.29,0.26,0.25,…
"07/14/2010 08:10:00",1,7,3,4,0.26,0.24,0.23,0.28,0.27,0.24,0.27,0.23,0.24,0.27,0.23,0.24,…
"07/14/2010 08:10:00",1,8,3,4,0.27,0.25,0.25,0.26,0.24,0.25,0.24,0.25,0.25,0.24,0.25,0.25,…
"07/14/2010 08:10:00",1,9,3,4,0.25,0.26,0.24,0.24,0.23,0.26,0.23,0.24,0.26,0.23,0.24,0.26,…
"07/14/2010 08:10:00",1,10,3,4,0.26,0.26,0.25,0.29,0.28,0.28,0.28,0.25,0.26,0.28,0.25,0.28,…
"07/14/2010 08:20:00",2,1,3,4,0.24,0.24,0.24,0.26,0.24,0.23,0.24,0.24,0.24,0.24,0.24,0.23,…
"07/14/2010 08:20:00",2,2,3,4,0.28,0.23,0.24,0.23,0.27,0.25,0.27,0.24,0.23,0.27,0.24,0.25,…
"07/14/2010 08:20:00",2,3,3,4,0.27,0.28,0.25,0.25,0.24,0.24,0.24,0.25,0.28,0.24,0.25,0.24,…
"07/14/2010 08:20:00",2,4,3,4,0.26,0.27,0.29,0.24,0.23,0.25,0.23,0.29,0.27,0.23,0.29,0.25,…
"07/14/2010 08:20:00",2,5,3,4,0.23,0.26,0.27,0.25,0.28,0.26,0.28,0.27,0.26,0.28,0.27,0.26,…
"07/14/2010 08:20:00",2,6,3,4,0.25,0.29,0.24,0.26,0.26,0.28,0.26,0.24,0.29,0.26,0.24,0.28,…
"07/14/2010 08:20:00",2,7,3,4,0.24,0.27,0.23,0.28,0.24,0.26,0.24,0.23,0.27,0.24,0.23,0.26,…
"07/14/2010 08:20:00",2,8,3,4,0.25,0.24,0.28,0.26,0.25,0.23,0.25,0.28,0.24,0.25,0.28,0.23,…
"07/14/2010 08:20:00",2,9,3,4,0.26,0.23,0.26,0.23,0.29,0.25,0.29,0.26,0.23,0.29,0.26,0.25,…
"07/14/2010 08:20:00",2,10,3,4,0.28,0.28,0.24,0.25,0.27,0.24,0.27,0.24,0.28,0.27,0.24,0.24,…

With number of columns variable, does not work well to design SQL DB like that. Here is the data once formatted into SQL DB Columns: (note first 5 columns are the same as csv file).
Date - Time,Batch Number,Batch Product # (10 products per batch),Number of Tracks = N (measurement tracks),Number Of Measurement Locations Per Track = L,Track Number,Measurement Location,Thickness
"07/14/2010 08:10:00",1,1,3,4,1,1,0.24
"07/14/2010 08:10:00",1,1,3,4,1,2,0.29
"07/14/2010 08:10:00",1,1,3,4,1,3,0.25
"07/14/2010 08:10:00",1,1,3,4,1,4,0.27
"07/14/2010 08:10:00",1,1,3,4,2,1,0.23
"07/14/2010 08:10:00",1,1,3,4,2,2,0.25
"07/14/2010 08:10:00",1,1,3,4,2,3,0.23
"07/14/2010 08:10:00",1,1,3,4,2,4,0.25
"07/14/2010 08:10:00",1,1,3,4,3,1,0.29
"07/14/2010 08:10:00",1,1,3,4,3,2,0.23
"07/14/2010 08:10:00",1,1,3,4,3,3,0.25
"07/14/2010 08:10:00",1,1,3,4,3,4,0.25
"07/14/2010 08:10:00",1,2,3,4,1,1,0.26
"07/14/2010 08:10:00",1,2,3,4,1,2,0.27
"07/14/2010 08:10:00",1,2,3,4,1,3,0.24
"07/14/2010 08:10:00",1,2,3,4,1,4,0.26
"07/14/2010 08:10:00",1,2,3,4,2,1,0.28
"07/14/2010 08:10:00",1,2,3,4,2,2,0.26
"07/14/2010 08:10:00",1,2,3,4,2,3,0.28
"07/14/2010 08:10:00",1,2,3,4,2,4,0.24
"07/14/2010 08:10:00",1,2,3,4,3,1,0.27
"07/14/2010 08:10:00",1,2,3,4,3,2,0.28
"07/14/2010 08:10:00",1,2,3,4,3,3,0.24
"07/14/2010 08:10:00",1,2,3,4,3,4,0.26
"07/14/2010 08:10:00",1,3,3,4,1,1,0.23
"07/14/2010 08:10:00",1,3,3,4,1,2,0.24
"07/14/2010 08:10:00",1,3,3,4,1,3,0.25
"07/14/2010 08:10:00",1,3,3,4,1,4,0.29
"07/14/2010 08:10:00",1,3,3,4,2,1,0.26
"07/14/2010 08:10:00",1,3,3,4,2,2,0.28
"07/14/2010 08:10:00",1,3,3,4,2,3,0.26
"07/14/2010 08:10:00",1,3,3,4,2,4,0.25
"07/14/2010 08:10:00",1,3,3,4,3,1,0.24
"07/14/2010 08:10:00",1,3,3,4,3,2,0.26
"07/14/2010 08:10:00",1,3,3,4,3,3,0.25
"07/14/2010 08:10:00",1,3,3,4,3,4,0.28
"07/14/2010 08:10:00",1,4,3,4,1,1,0.24
"07/14/2010 08:10:00",1,4,3,4,1,2,0.23
"07/14/2010 08:10:00",1,4,3,4,1,3,0.26
"07/14/2010 08:10:00",1,4,3,4,1,4,0.27
"07/14/2010 08:10:00",1,4,3,4,2,1,0.24
"07/14/2010 08:10:00",1,4,3,4,2,2,0.26
"07/14/2010 08:10:00",1,4,3,4,2,3,0.24
"07/14/2010 08:10:00",1,4,3,4,2,4,0.26
"07/14/2010 08:10:00",1,4,3,4,3,1,0.23
"07/14/2010 08:10:00",1,4,3,4,3,2,0.24
"07/14/2010 08:10:00",1,4,3,4,3,3,0.26
"07/14/2010 08:10:00",1,4,3,4,3,4,0.26
"07/14/2010 08:10:00",1,5,3,4,1,1,0.28
"07/14/2010 08:10:00",1,5,3,4,1,2,0.28
"07/14/2010 08:10:00",1,5,3,4,1,3,0.28
"07/14/2010 08:10:00",1,5,3,4,1,4,0.24
"07/14/2010 08:10:00",1,5,3,4,2,1,0.25
"07/14/2010 08:10:00",1,5,3,4,2,2,0.23
"07/14/2010 08:10:00",1,5,3,4,2,3,0.25
"07/14/2010 08:10:00",1,5,3,4,2,4,0.28
"07/14/2010 08:10:00",1,5,3,4,3,1,0.28
"07/14/2010 08:10:00",1,5,3,4,3,2,0.25
"07/14/2010 08:10:00",1,5,3,4,3,3,0.28
"07/14/2010 08:10:00",1,5,3,4,3,4,0.23
"07/14/2010 08:10:00",1,6,3,4,1,1,0.22
"07/14/2010 08:10:00",1,6,3,4,1,2,0.26
"07/14/2010 08:10:00",1,6,3,4,1,3,0.26
"07/14/2010 08:10:00",1,6,3,4,1,4,0.23
"07/14/2010 08:10:00",1,6,3,4,2,1,0.29
"07/14/2010 08:10:00",1,6,3,4,2,2,0.25
"07/14/2010 08:10:00",1,6,3,4,2,3,0.29
"07/14/2010 08:10:00",1,6,3,4,2,4,0.26
"07/14/2010 08:10:00",1,6,3,4,3,1,0.26
"07/14/2010 08:10:00",1,6,3,4,3,2,0.29
"07/14/2010 08:10:00",1,6,3,4,3,3,0.26
"07/14/2010 08:10:00",1,6,3,4,3,4,0.25
"07/14/2010 08:10:00",1,7,3,4,1,1,0.26
"07/14/2010 08:10:00",1,7,3,4,1,2,0.24
"07/14/2010 08:10:00",1,7,3,4,1,3,0.23
"07/14/2010 08:10:00",1,7,3,4,1,4,0.28
"07/14/2010 08:10:00",1,7,3,4,2,1,0.27
"07/14/2010 08:10:00",1,7,3,4,2,2,0.24
"07/14/2010 08:10:00",1,7,3,4,2,3,0.27
"07/14/2010 08:10:00",1,7,3,4,2,4,0.23
"07/14/2010 08:10:00",1,7,3,4,3,1,0.24
"07/14/2010 08:10:00",1,7,3,4,3,2,0.27
"07/14/2010 08:10:00",1,7,3,4,3,3,0.23
"07/14/2010 08:10:00",1,7,3,4,3,4,0.24
"07/14/2010 08:10:00",1,8,3,4,1,1,0.27
"07/14/2010 08:10:00",1,8,3,4,1,2,0.25
"07/14/2010 08:10:00",1,8,3,4,1,3,0.25
"07/14/2010 08:10:00",1,8,3,4,1,4,0.26
"07/14/2010 08:10:00",1,8,3,4,2,1,0.24
"07/14/2010 08:10:00",1,8,3,4,2,2,0.25
"07/14/2010 08:10:00",1,8,3,4,2,3,0.24
"07/14/2010 08:10:00",1,8,3,4,2,4,0.25
"07/14/2010 08:10:00",1,8,3,4,3,1,0.25
"07/14/2010 08:10:00",1,8,3,4,3,2,0.24
"07/14/2010 08:10:00",1,8,3,4,3,3,0.25
"07/14/2010 08:10:00",1,8,3,4,3,4,0.25
"07/14/2010 08:10:00",1,9,3,4,1,1,0.25
"07/14/2010 08:10:00",1,9,3,4,1,2,0.26
"07/14/2010 08:10:00",1,9,3,4,1,3,0.24
"07/14/2010 08:10:00",1,9,3,4,1,4,0.24
"07/14/2010 08:10:00",1,9,3,4,2,1,0.23
"07/14/2010 08:10:00",1,9,3,4,2,2,0.26
"07/14/2010 08:10:00",1,9,3,4,2,3,0.23
"07/14/2010 08:10:00",1,9,3,4,2,4,0.24
"07/14/2010 08:10:00",1,9,3,4,3,1,0.26
"07/14/2010 08:10:00",1,9,3,4,3,2,0.23
"07/14/2010 08:10:00",1,9,3,4,3,3,0.24
"07/14/2010 08:10:00",1,9,3,4,3,4,0.26
"07/14/2010 08:10:00",1,10,3,4,1,1,0.26
"07/14/2010 08:10:00",1,10,3,4,1,2,0.26
"07/14/2010 08:10:00",1,10,3,4,1,3,0.25
"07/14/2010 08:10:00",1,10,3,4,1,4,0.29
"07/14/2010 08:10:00",1,10,3,4,2,1,0.28
"07/14/2010 08:10:00",1,10,3,4,2,2,0.28
"07/14/2010 08:10:00",1,10,3,4,2,3,0.28
"07/14/2010 08:10:00",1,10,3,4,2,4,0.25
"07/14/2010 08:10:00",1,10,3,4,3,1,0.26
"07/14/2010 08:10:00",1,10,3,4,3,2,0.28
"07/14/2010 08:10:00",1,10,3,4,3,3,0.25
"07/14/2010 08:10:00",1,10,3,4,3,4,0.28

Again, sorry for good bit of data.

Thanks

Dean

Dean Pugh

sql-programmers
Posting Yak Master

190 Posts
Posted - 2010-07-15 : 05:34:31
You may be use Bulk Insert for import csv file other than SSIS,
Go through the article,

http://www.sql-programmers.com/Blog/tabid/153/EntryId/29/SQL-Bulk-Insert.aspx



SQL Server Programmers and Consultants
http://www.sql-programmers.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site