Solved

CSV to SQL from Batch

Posted on 2014-02-06
15
3,120 Views
Last Modified: 2016-02-10
I receive a daily csv download over sftp. I have a batch file to retrieve this daily.

I need to then call out a .sql file to insert the new records daily. This is where I need help.

I know the SQL syntax just fine and work with it daily but I have never tried to do something like this.

Any suggestions?
0
Comment
Question by:LCNW
  • 10
  • 5
15 Comments
 
LVL 12

Accepted Solution

by:
Tony303 earned 500 total points
ID: 39840205
Have you thought about an SSIS Package.

You'd have to have SQL Server Integration Services running.

I have processes which connect to sftp, transfers zip files, unzips the files, loads the records, deletes the files and archives the zips.

These can be scheduled to run using the normal SQL Server Agent.

There are fish-hooks along the way setting this up, user accounts and rights etc, but they work very well day after day after day.

T
0
 
LVL 1

Author Comment

by:LCNW
ID: 39840216
I don't have SSIS running. I'm trying to build the SQL. I have this so far, but with errors...



USE [Database]

BULK
INSERT dbo.Table
FROM 'C:\file.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM dbo.Table
GO
--Drop the table to clean up database.
DROP TABLE dbo.Table
GO
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39840277
What are the errors.
0
 
LVL 1

Author Comment

by:LCNW
ID: 39840296
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 1 (DateX).
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 1 (DateX).
Msg 4865, Level 16, State 1, Line 3
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Open in new window

0
 
LVL 12

Expert Comment

by:Tony303
ID: 39840390
OK,

So we need to configure the columns in the SQL table to reflect the type of data coming in from the .csv files.

2 ways,

Create a load table with the datatype for each field big enough to cope with any sort of data. Say Varchar(255).
Then after the import from the csv have another process to transform the data into their destination table/s using datatypes that are apprpriate. IE Date type data to a Date field, Numeric data to an integer data type etc.

Or create a mapping file that has all the datatypes for the field specified and add that to the SQL syntax when loading the csv...

Take a look here...it is not a bad example for the mapping file option...

http://stackoverflow.com/questions/13056929/bulk-load-data-conversion-error-type-mismatch-or-invalid-character-for-the-spec
0
 
LVL 1

Author Comment

by:LCNW
ID: 39840458
I'll check out the mapping file option and report back.
0
 
LVL 1

Author Comment

by:LCNW
ID: 39842636
I have an xlsx file that appears to have the mapping.

DateX	datetime	no	8	     	     	yes	(n/a)	(n/a)	NULL
DeathOn	datetime	no	8	     	     	yes	(n/a)	(n/a)	NULL
AddedToStatlineOn	datetime	no	8	     	     	yes	(n/a)	(n/a)	NULL
ReferredOn	datetime	no	8	     	     	no	(n/a)	(n/a)	NULL
SightLife Case ID	nvarchar	no	40	     	     	yes	(n/a)	(n/a)	SQL_Latin1_General_CP1_CI_AS
StatLine Referral ID	nvarchar	no	30	     	     	yes	(n/a)	(n/a)	SQL_Latin1_General_CP1_CI_AS
Region	nvarchar	no	100	     	     	yes	(n/a)	(n/a)	SQL_Latin1_General_CP1_CI_AS
TissueOutcome	nvarchar	no	100	     	     	yes	(n/a)	(n/a)	SQL_Latin1_General_CP1_CI_AS
TissueOutcomeDetail	nvarchar	no	100	     	     	yes	(n/a)	(n/a)	SQL_Latin1_General_CP1_CI_AS

Open in new window


Do I just paste into a text file and save as .fmt?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:LCNW
ID: 39842714
Ok. I made a fmt file. Now I'm trying to figure out how to implement this.
0
 
LVL 1

Author Comment

by:LCNW
ID: 39842769
I use the following code:
USE [LifeCenterReporting]
GO
BULK INSERT LifeCenterReporting.dbo.SightlifeExtract
    FROM 'C:\TransplantConnect\Replication\SightLife\lcnw.csv' 
    WITH (FORMATFILE = 'C:\TransplantConnect\Replication\SightLife\SightlifeFormatFile.fmt',
		  FIELDTERMINATOR = ',', 
		  ROWTERMINATOR = '\n' 
		  )

Open in new window


But it returns this error:
Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (DateX).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Open in new window

0
 
LVL 12

Expert Comment

by:Tony303
ID: 39842989
Hi,

Is the date field looking OK on your csv file? That first error is pointing to the first column of the first row?
Is there a header row in the csv?
Is it trying to put text data into a datetime field?

In these situations I generally get back to basics.
I'll cut down the csv file to 2 rows of perfectly formed data.

If that works as an import, then there is something else in the csv file that is incorrectly formatted.
If it doesn't work with 2 rows then there is perhaps there is a problem with the format file.

T
0
 
LVL 1

Author Comment

by:LCNW
ID: 39854732
I've been given a new file in the .mdb format. It imports fine. Now I need to automate the deletion of the original and the import of the .mdb to run daily.
0
 
LVL 1

Author Comment

by:LCNW
ID: 39854940
I created a SSIS package for the import and a 2-step job for the deletion and recreation of the table. All works fine. Is there a better way to handle this programmatically?
0
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 500 total points
ID: 39854976
Hi,

Good job on the SSIS package.
Deploying to an SSIS server now and scheduling in SQL Agent will be the next step.

As far as the 2-step job for the deletion and recreation of the table. You could do it all in one SQL Task I guess with 2 lots of code... a DROP TABLE and a CREATE TABLE?

Have you thought about truncating the table before you import the .mdb. No need then for a drop or create?

T
0
 
LVL 1

Author Comment

by:LCNW
ID: 39855008
I created a job and an operator and enabled database mail so I can get alerts when it fails. It all tested okay.
0
 
LVL 1

Author Closing Comment

by:LCNW
ID: 39855069
Thanks for the help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server Error Log - logging period 1 15
Sql query 34 17
MSSQL Frequency of Years From Days Field 2 11
Azure SQL DB? 3 14
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now