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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 CSV data impot to SQL table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Menorel
Starting Member

USA
15 Posts

Posted - 06/28/2013 :  07:54:26  Show Profile  Reply with Quote
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

Edited by - Menorel on 06/28/2013 09:10:16

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/28/2013 :  12:17:21  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/28/2013 :  12:20:32  Show Profile  Reply with Quote
by the way I don't think double qoutues can be used in Bulk Insert statment

Cheers
MIK
Go to Top of Page

Menorel
Starting Member

USA
15 Posts

Posted - 07/01/2013 :  09:23:16  Show Profile  Reply with Quote
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)
Go to Top of Page

Menorel
Starting Member

USA
15 Posts

Posted - 07/01/2013 :  09:41:35  Show Profile  Reply with Quote
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.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 07/01/2013 :  10:09:08  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 07/01/2013 10:10:37
Go to Top of Page

Menorel
Starting Member

USA
15 Posts

Posted - 07/01/2013 :  10:50:38  Show Profile  Reply with Quote
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)
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 07/01/2013 :  10:56:57  Show Profile  Reply with Quote
Use Single qoutes not double ...

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

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000