CSV data impot to SQL table - 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

Transact-SQL (2005)

CSV data impot to SQL table

Author

Topic

Menorel
Starting Member

15 Posts

Posted - 2013-06-28 : 07:54:26

I am importing multiple CSV files into a SQL Server 2005 table and I believe I am running into an issue with the data. Below is the way I have established the table, a sample of the data, and the stored procedure I am using to do the import.

The error message that I am getting when executing the SP is:

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ','.

Table Configuration
TIMERECORDID nvarchar(50) Checked
ExternalID nvarchar(50) Checked
FirstName nvarchar(50) Checked
LastName nvarchar(50) Checked
LastUpdatedDate datetime Checked
LastUpdatedTime nvarchar(50) Checked
LastUpdatedBy nvarchar(50) Checked
IsApproved nvarchar(50) Checked
EmployeeID nvarchar(50) Checked
OrganizationName nvarchar(50) Checked
TimeZone nvarchar(50) Checked
Activity nvarchar(50) Checked
Startdate datetime Checked
Starttime nvarchar(50) Checked
EndDate datetime Checked
EndTime nvarchar(50) Checked
Duration int Checked
IsPaid nvarchar(50) Checked
TimeSourceCode int Checked
EventType int Checked

Sample Data (First Row is Header Column First Row does not have "" around the hash this is done so that the hash does not affect formating the hash is part of the field name)

"#"TimeRecordID,ExternalID,FirstName,LastName,LastUpdatedDate,LastUpdateTime,LastUpdatedBy,IsApproved,Employee ID#,OrganizationName,TimeZone,Activity,StartDate,StartTime,EndDate,EndTime,Duration,IsPaid,TimeSourceCode,EventType

46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Records Merge Activity,2013/06/24,14:53:58,2013/06/24,14:59:00,6,false,4,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,AUX 0,2013/06/24,14:59:00,2013/06/24,15:00:15,1,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Phone,2013/06/24,15:00:15,2013/06/24,15:17:35,17,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,AUX Out,2013/06/24,15:17:50,2013/06/24,15:18:50,1,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Customer on Hold,2013/06/24,15:18:50,2013/06/24,15:19:05,1,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Phone,2013/06/24,15:19:05,2013/06/24,15:21:05,2,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Available,2013/06/24,15:21:05,2013/06/24,15:22:20,1,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Phone,2013/06/24,15:22:20,2013/06/24,15:32:35,10,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Available,2013/06/24,15:32:35,2013/06/24,15:34:06,2,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Phone,2013/06/24,15:34:06,2013/06/24,15:40:52,6,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Available,2013/06/24,15:40:52,2013/06/24,15:41:07,1,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Phone,2013/06/24,15:41:07,2013/06/24,15:43:52,2,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Available,2013/06/24,15:43:52,2013/06/24,15:44:08,1,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Phone,2013/06/24,15:44:08,2013/06/24,15:46:39,2,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Available,2013/06/24,15:46:39,2013/06/24,15:48:24,2,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Phone,2013/06/24,15:48:24,2013/06/24,15:57:54,9,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Available,2013/06/24,15:57:54,2013/06/24,15:59:09,2,true,0,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Records Merge Activity,2013/06/24,15:59:24,2013/06/24,16:00:09,1,false,4,0 46853,3457,Jane,Doe,2013/06/24,14:53:13,,false,545571,Phone Team,GMT,Meeting,2013/06/24,16:00:39,2013/06/24,16:10:10,10,true,0,0

SP Code

declare @query varchar(1000)
declare @max1 int
declare @count1 int
declare @filename varchar(100)
declare @filepath varchar(500)
declare @pattern varchar(100)
declare @TableName varchar(128)
set @count1 =0
set @filepath = '\\susagpwfm01\TRE_Files'
set @pattern = '*.csv'
set @TableName = 'tbl_Payroll'
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x 
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
Set @Filepath = '\\servername\Files_Folder'
Set @Pattern = '*.csv'
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'" 
WITH ( FIELDTERMINATOR = ",",
FIRSTROW=2,
ROWTERMINATOR = "\n")'
--print @query
exec (@query)
end

drop table #y

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-28 : 12:17:21

post the print's result of @query variable of the following portion?

set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",
FIRSTROW=2,
ROWTERMINATOR = "\n")'
print @query

Cheers
MIK

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-28 : 12:20:32

by the way I don't think double qoutues can be used in Bulk Insert statment

Cheers
MIK

Menorel
Starting Member

15 Posts

Posted - 2013-07-01 : 09:23:16

Here is the results from the print @query your asked for. It appears to be building the query correctly.

(2 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
BULK INSERT tbl_Payroll FROM "\\susagpwfm01\TRE_FilesThe network path was not found."
WITH ( FIELDTERMINATOR = ",",
FIRSTROW=2,
ROWTERMINATOR = "\n")
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

(1 row(s) affected)

Menorel
Starting Member

15 Posts

Posted - 2013-07-01 : 09:41:35

I just noticed "The network path was not found" statement in there. Not sure why it is having a problem the folder has 'Everyone' allowed on it with modify rights so it shouldn't be running into authentication issues.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-01 : 10:09:08

The query should look like following in order to read CSV file using Bulk Insert

BULK INSERT tbl_Payroll FROM '\\PathOfTheFile\FolderName\FileName.csv'
WITH ( FIELDTERMINATOR = ',',
FIRSTROW=2,
ROWTERMINATOR = '\n')

1) as I mentioned earlier there should be single qoute not double
2) you mentioned that its CSV file.. but query youprinted has no sign of information that you're pointing onto a CSV file located on network path .. logically the path should resembles somewhat to --> \\ServerName\FolderName\FileName.CSV

Cheers
MIK

Menorel
Starting Member

15 Posts

Posted - 2013-07-01 : 10:50:38

Ok got the file path thing corrected had left out a "\" in @Filepath. Here is the updated output from @query.

(7 row(s) affected)

(1 row(s) affected)

(6 row(s) affected)
6
0
tbl_Payroll
\\susagpwfm01\TRE_FilesBPTRExport__06_26_2013_04_00_09_EXPORTsusagpbdr01.csv
BULK INSERT tbl_Payroll FROM "\\susagpwfm01\TRE_Files\BPTRExport__06_26_2013_04_00_09_EXPORTsusagpbdr01.csv" WITH ( FIELDTERMINATOR = ",", ROWTERMINATOR = "\n")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
tbl_Payroll
\\susagpwfm01\TRE_FilesBPTRExport__06_27_2013_04_00_09_EXPORTsusagpbdr01.csv
BULK INSERT tbl_Payroll FROM "\\susagpwfm01\TRE_Files\BPTRExport__06_27_2013_04_00_09_EXPORTsusagpbdr01.csv" WITH ( FIELDTERMINATOR = ",", ROWTERMINATOR = "\n")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
tbl_Payroll
\\susagpwfm01\TRE_FilesBPTRExport__06_28_2013_04_00_08_EXPORTsusagpbdr01.csv
BULK INSERT tbl_Payroll FROM "\\susagpwfm01\TRE_Files\BPTRExport__06_28_2013_04_00_08_EXPORTsusagpbdr01.csv" WITH ( FIELDTERMINATOR = ",", ROWTERMINATOR = "\n")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
tbl_Payroll
\\susagpwfm01\TRE_FilesBPTRExport__06_29_2013_04_00_08_EXPORTsusagpbdr01.csv
BULK INSERT tbl_Payroll FROM "\\susagpwfm01\TRE_Files\BPTRExport__06_29_2013_04_00_08_EXPORTsusagpbdr01.csv" WITH ( FIELDTERMINATOR = ",", ROWTERMINATOR = "\n")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
tbl_Payroll
\\susagpwfm01\TRE_FilesBPTRExport__06_30_2013_04_00_07_EXPORTsusagpbdr01.csv
BULK INSERT tbl_Payroll FROM "\\susagpwfm01\TRE_Files\BPTRExport__06_30_2013_04_00_07_EXPORTsusagpbdr01.csv" WITH ( FIELDTERMINATOR = ",", ROWTERMINATOR = "\n")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
tbl_Payroll
\\susagpwfm01\TRE_FilesBPTRExport__07_01_2013_04_00_07_EXPORTsusagpbdr01.csv
BULK INSERT tbl_Payroll FROM "\\susagpwfm01\TRE_Files\BPTRExport__07_01_2013_04_00_07_EXPORTsusagpbdr01.csv" WITH ( FIELDTERMINATOR = ",", ROWTERMINATOR = "\n")
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.
tbl_Payroll
\\susagpwfm01\TRE_Files

(1 row(s) affected)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-01 : 10:56:57

Use Single qoutes not double ...

BULK INSERT tbl_Payroll FROM '\\PathOfTheFile\FolderName\FileName.csv'
WITH ( FIELDTERMINATOR = ',',
FIRSTROW=2,
ROWTERMINATOR = '\n')

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources