[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

CSV to SQL from Batch

Posted on 2014-02-06
15
Medium Priority
?
3,342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
15 Comments
 
LVL 12

Accepted Solution

by:
Tony303 earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

650 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