| Author |
Topic |
|
mitasid
Yak Posting Veteran
51 Posts |
 Posted - 2006-07-23 : 23:10:44
|
| Hi guysI 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 fileIndicatorid,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,Denominator19,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,.000019,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,.000019,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,.000019,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,.0000and 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
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-07-24 : 02:05:54
|
| How are you importing it?Have you tried importing the first few rows to try and narrow down the problem? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-24 : 02:33:13
|
| "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 |
|
|
|
mitasid
Yak Posting Veteran
51 Posts |
Posted - 2006-07-24 : 16:19:41
|
| 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??? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-24 : 16:30:39
|
| "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 BCPKristen |
|
|
|
|
|
|
import/export problem!