Database Discussion - Importing Data (SQL Server)

Print Page | Close Window

Importing Data (SQL Server)

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=2462
Printed Date: 29 March 2026 at 11:03pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Importing Data (SQL Server)

Posted By: Gary
Subject: Importing Data (SQL Server)
Date Posted: 06 May 2003 at 8:37am

Ok, I've been working with SQL Server for a few years, but am compltetely stumped by this one....

Basically I have a csv file which comprises of a header, column names followed by column data as follows

"Created at ","6-May-2003 13:54:10","GMT" (FILE HEADER)
"Mgr1 Name","Mgr2 Name","Mgr3 Name","SE Name","SE_USER_ID","OPP_NO","SE_COUNTRY","SITE_ID","SITE_NAME","FRCST_CLOSE_DT","FRCST_PROD_REV","FRCST_SERV_REV","FRCST_ED_REV","CURRENCY_CD","PROB_CLOSE_PCT","STATUS_CD","TOP_DEAL_IND","TAS_PLAN_IND","PROB_WIN_PCT","MF_OPP_IND","OPP_SUMMARY_TXT","BLANK","TOPS_NAME","ADDRESS_1","ADDRESS_2","CITY","STATE","ZIP","COUNTRY","PERIL_IND","INSIDE_QTR_IND","E_BUSINESS","ENTERPRISE_MGMT","TEMP","OS390","PORTAL_MGMT","PREDICITIVE_ANAL","SECURITY","STORAGE","TIME","TIMECAP","VSE_VM","Mgr1ID","Mgr2ID","Mgr3ID","Row#","Mgr4 Name","Mgr4ID" (COLUMN NAMES)
"mgr1 data","mgr2 data","Mgr3 data","SE data", and so on (DATA)
"mgr1 data","mgr2 data","Mgr3 data","SE data", and so on (DATA)
"mgr1 data","mgr2 data","Mgr3 data","SE data", and so on (DATA)

Now, I just cannot get the damn data to import (using DTS) - it complains of too many columns being found in the current row. I've tried setting up the data transformation to skip x number of rows, first row has column names, etc, but no luck.

If I manually remove the first row (Created at......) and then import it works fine.

So, what do I need to do? I really could do without any manual intervention (ie modifying the source file).

Thx

Replies:

Posted By: MorningZ
Date Posted: 06 May 2003 at 12:55pm

over the years i have noticed that Access is better at importing data like this

perhaps try to import that list into Access (or Excel) and then into SQL ?

i dunno, just a thought to try to help ya out

-------------
Contribute to the working anarchy we fondly call the Internet

Posted By: michael
Date Posted: 06 May 2003 at 1:53pm

Well when importing the data you should not have the header in there because it bombs the format. For importing csv files though i like to use the bcp utility or BULK INSERT, which gives you some nicer options...

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Gary
Date Posted: 07 May 2003 at 2:38am

Thanks for the responses....

Morningz,
I wish I could get the file without the header information, but it comes from our head office and they're not exactly helpful on the matter!!!!

Michael,
I was thinking of looking at BCP, but have never used it before.

One workaround I've been playing with is to use fso to read the data, line by line and then import only the lines/rows required into SQL (not all of the data below the header is required).