Importing .dat files into SQL - SQL Server Forums

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

SSIS and Import/Export (2005)

Importing .dat files into SQL

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-23 : 10:47:08

Hi All,

What is the best way to import *.Dat file into SQL table without losing any information?

I have tried the following code but I do loose on top few rows


-- Creating a format file
exec master..xp_cmdShell 'bcp TemplateForFileUploadOrganiser..hoko_Intake_stage format nul -c -t, -f D:\EvaluationsUnit\Langhill\Archive\FeedIntake\FeedIntakeFormatFiles.Fmt -T'


-- BULK Inserting
DELETE hoko_Intake_stage;
GO
BULK INSERT hoko_Intake_stage 
   FROM 'D:\EvaluationsUnit\Langhill\Archive\FeedIntake\VR090414.DAT' 
   WITH (FORMATFILE = 'D:\EvaluationsUnit\Langhill\Archive\FeedIntake\FeedIntakeFormatFiles.Fmt');
GO
SELECT * FROM hoko_Intake_stage;
GO

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-24 : 04:40:19

OK I have found a work around which is not to use format file:


BULK INSERT hoko_Intake_stage
   FROM 'D:\EvaluationsUnit\Langhill\Archive\FeedIntake\VR090414.DAT'
   WITH 
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR ='\n'
      )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 04:43:31

Or check your automatically created format file to see why it is wrong with the data?

E 12°55'05.63"
N 56°04'39.26"

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-24 : 04:50:56

I did check the format file and all the column names are there as expected, don't know where it is losing them...not sure what does 9.0 and 16 represents in the top two rows of the file:


9.0
16
1       SQLCHAR       0       50      ","      1     Dummy1                       SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       50      ","      2     Freeze_Brand                 SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       50      ","      3     Gate_no                      SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       50      ","      4     Start_Time                   SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       50      ","      5     Stop_Time                    SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       50      ","      6     Dummy2                       SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       50      ","      7     Start_Wgt                    SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR       0       50      ","      8     Stop_Wgt                     SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR       0       50      ","      9     Feed_Type                    SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR       0       50      ","      10    Dummy3                       SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR       0       50      ","      11    Dummy4                       SQL_Latin1_General_CP1_CI_AS
12      SQLCHAR       0       50      ","      12    Dummy5                       SQL_Latin1_General_CP1_CI_AS
13      SQLCHAR       0       50      ","      13    Dummy6                       SQL_Latin1_General_CP1_CI_AS
14      SQLCHAR       0       50      ","      14    Dummy7                       SQL_Latin1_General_CP1_CI_AS
15      SQLCHAR       0       50      ","      15    Dummy8                       SQL_Latin1_General_CP1_CI_AS
16      SQLCHAR       0       50      "\r\n"   16    Dummy9                       SQL_Latin1_General_CP1_CI_AS

quote:
Originally posted by Peso

Or check your automatically created format file to see why it is wrong with the data?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources