SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 import/export problem!
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitasid
Yak Posting Veteran

New Zealand
51 Posts

Posted - 07/23/2006 :  23:10:44  Show Profile
Hi guys
I am trying to import a text file into sql server but sql doesnt let me do that.
My flat file is in the txt format.
Here is a part of my txt file

Indicatorid,IndicatorName,Reportingquarter,SourceDate,DHB,DHBName,PHO,PHOName,Practice,PracticeName,Practitioner,PractitionerName,NHI,DOB,Ethnicity_Group_1,Ethnicity_Group_2,Ethnicity_Group_3,Gender,Ageband,Quintile,Practice_Type,LabsPharms_rate,Population,Consults,DrZerorates,TotalProjPopn,ProjectedPopn,Adjustment,NumeratorSource,DenominatorSource,NumeratorCode,DenominatorCode,ReferralIDCRP,ReferralIDESR,Units,LOCUM,Percntge,Numerator,Denominator
19,GP Referred Pharmaceutical Expenditure Total Population,2005-10-01 00:00:00,2005-12-17 00:00:00,141,Southland DHB,600649,Wakatipu PHO 600649,600649_0001011,Queenstown Medical Centre,0037379,Simon Bernard Davies,,,,,,,,,,,,,1.00,,,3.10,310.27000,.00000,,,,,,NO ,100.00,313.3727,.0000
19,GP Referred Pharmaceutical Expenditure Total Population,2005-10-01 00:00:00,2005-12-09 00:00:00,131,Otago DHB,597005,Central Otago PHO Ltd t/a Rural Otago PHO 597005,597005_0000006,Aspiring Medical Centre,0037384,Jayne Louise Davies,,,,,,,,,,,,,.80,,,1.52,189.47000,.00000,,,,,,NO ,100.00,190.9858,.0000
19,GP Referred Pharmaceutical Expenditure Total Population,2005-10-01 00:00:00,2005-10-05 00:00:00,120,Canterbury DHB,593879,Hurunui Kaikoura Primary Health Organisation 59387,593879_0000002,Amuri,0031747,Jean Gerges Dehn,,,,,,,,,,,,,1.10,,,2.61,474.56000,.00000,,,,,,NO ,50.00,239.8901,.0000
19,GP Referred Pharmaceutical Expenditure Total Population,2005-10-01 00:00:00,2005-11-16 00:00:00,120,Canterbury DHB,596721,Partnership Health Canterbury 596721,596721_0003489,Darfield Medical Centre Limited,0030500,Andries Hendrik Grobler,,,,,,,,,,,,,1.10,,,2.04,185.33000,.00000,,,,,,NO ,100.00,187.3686,.0000








and this file is almost 1 gb in size.
when i try to export this file to sql 2005, it gives me an error which says "invalid format"...
please help

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 07/24/2006 :  02:05:54  Show Profile  Visit timmy's Homepage
How are you importing it?

Have you tried importing the first few rows to try and narrow down the problem?

Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/24/2006 :  02:33:13  Show Profile
"it gives me an error which says "invalid format"..."

What happens, in the data, when there is an embedded comma in one of the string fields?

That's the most common cause of that type of problem. (The fields should be enclosed in double-quotes, if it isn't you will have a problem!)

Kristen
Go to Top of Page

mitasid
Yak Posting Veteran

New Zealand
51 Posts

Posted - 07/24/2006 :  16:19:41  Show Profile
Hi
The problem is some fields are separated with one comma and some are by more than one..
could you tell me how to use a bcp command or bulk insert???
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/24/2006 :  16:30:39  Show Profile
"The problem is some fields are separated with one comma and some are by more than one"

The file will have to be consistent (i.e. the same number of columns, and thus unquoted-commas, in each row) for you to be able to import it. The sample four rows you have posted are fine in this regard - although there are multiple adjacent commas there are the same number in each row.

If you are not familiar with BCP you might prefer to try DTS - you can use the visual interface to preview the columns and see what translations are being performed etc., rather than doing it "blind" with BCP

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000