?
Solved

CSV to SQL from Batch

Posted on 2014-02-06
15
Medium Priority
?
3,292 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

743 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