Importing CSV file - 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 2008 Forums

Transact-SQL (2008)

Importing CSV file

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-08-19 : 11:33:27

hi
I am importing a csv file into SQL database and when i import it the the empty cells in the CSV file are read in with a value 0 for the type Double precision float and numeric and is empty for fields which are declared as string when i import from flat file source.
id o this in the management console

Any help

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-08-19 : 11:44:34

My question is i want them to be NULL in the the database table if the cell is empty in the CSV file???

parody
Posting Yak Master

111 Posts

Posted - 2010-08-19 : 11:56:36

There should be an option "retain nul values from source..." in the source properties. (In SSIS there is anyway).

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-19 : 12:16:47

can you please post a sample of your csv file , just first few rows..

If you don't have the passion to help people, you have no passion

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-08-19 : 13:05:14

APC_HCPCS APC_EFF_START APC_EFF_END APC_IMP_START APC_IMP_END APC_LINK APC_CHANGES APC_SI APC_GROUP APC_RW APC_PR APC_NUC APC_MUC
100 1/1/2010 12/31/2010 7/1/2010 12/31/2045 N
102 1/1/2010 12/31/2010 7/1/2010 12/31/2045 N
103 1/1/2010 12/31/2010 7/1/2010 12/31/2045 N
104 1/1/2010 12/31/2010 7/1/2010 12/31/2045 N
120 1/1/2010 12/31/2010 7/1/2010 12/31/2045 N
124 1/1/2010 12/31/2010 7/1/2010 12/31/2045 N
126 1/1/2010 12/31/2010 7/1/2010 12/31/2045 N
140 1/1/2010 12/31/2010 7/1/2010 12/31/2045 N

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-08-19 : 13:07:08

the last 4 columns are read in from CSV as double precision float and wherever there is a value, it is read in as float int he database table but if it is empty like the ones above it reads it in a 0

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-19 : 13:25:45

can you please provide the schema of the destination table?

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-19 : 13:28:08

also what is the default value of those field that you want to have NULL for?

If you don't have the passion to help people, you have no passion

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-08-19 : 13:39:39

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_APC](
[APC_HCPCS] [char](5) NOT NULL,
[APC_EFF_START] [smalldatetime] NOT NULL,
[APC_EFF_END] [smalldatetime] NOT NULL,
[APC_IMP_START] [smalldatetime] NOT NULL,
[APC_IMP_END] [smalldatetime] NOT NULL,
[APC_LINK] [varchar](10) NULL,
[APC_CHANGES] [int] NULL,
[APC_SI] [char](1) NULL,
[APC_GROUP] [varchar](4) NULL,
[APC_RW] [float] NULL,
[APC_PR] [float] NULL,
[APC_NUC] [float] NULL,
[APC_MUC] [float] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

There is no default value..

Thanks
JKR

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-19 : 14:11:30

can't figure out why but the following works. save this as format file with extension fmt. save it somewhere where whomever you are running as has rights to the folder where you save it to


10.0
13
1       SQLCHAR             0       5       ","   1     APC_HCPCS                      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       24      ","   2     APC_EFF_START                  ""
3       SQLCHAR             0       24      ","   3     APC_EFF_END                    ""
4       SQLCHAR             0       24      ","   4     APC_IMP_START                  ""
5       SQLCHAR             0       24      ","   5     APC_IMP_END                    ""
6       SQLCHAR             0       10      ","   6     APC_LINK                       SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR             0       12      ","   7     APC_CHANGES                    ""
8       SQLCHAR             0       1       ","   8     APC_SI                         SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR             0       4       ","   9     APC_GROUP                      SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR             0       30      ","   10    APC_RW                         ""
11      SQLCHAR             0       30      ","   11    APC_PR                         ""
12      SQLCHAR             0       30      ","   12    APC_NUC                        ""
13      SQLCHAR             0       30      "\r\n"     13    APC_MUC                        ""


BULK INSERT dbo._APC FROM "\\YOURPATH\jayram11.csv" WITH (FORMATFILE = '\\YOURPATH\jayram11.Fmt', FIRSTROW = 2  );

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources