Solved

CSV to SQL from Batch

Posted on 2014-02-06
15
3,249 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 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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. …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 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