Solved

CSV to SQL from Batch

Posted on 2014-02-06
15
3,166 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

896 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

18 Experts available now in Live!

Get 1:1 Help Now