Solved

Importing CSV file into SQL Server

Posted on 2004-08-23
19
290 Views
Last Modified: 2008-03-10
Hi all...

    I'm trying a write an ASP script that wil import my csv file into my SQL server using ADO connection. Can anyone help me? The name of my csv file is "myDate.csv", and my database name is call "Db", and my table name is call "NAME".

thanks in advance.
0
Comment
Question by:ronghao
  • 9
  • 8
  • 2
19 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 11878458
If your CSV file is accesible from SQL Server point of view you can use simple BULK INSERT NAME FROM '<path or UNC>/myDate.csv'

If that data is downloaded from user you have to save it anywhere on server side and then run BULK INSERT.

Or you have to open file for reading and parse it by ASP code.

Patrik
0
 
LVL 1

Author Comment

by:ronghao
ID: 11878510
When the CSV file is accessible from the SQL server view, does tat mean that the SQL server must permit the permission to accept CSV file.. ?

Coz what im planning to do is to allow users to upload the csv file, then my script will import the csv file into my sql server. Is there an ASP sample code for me to reference?
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11878727
dim mconn
dim mcsvfilePath
dim mconnectionStr

' if file uploaded succesfully

mcsvfilePath = "\\Servername\PathName\MyDate.csv"
mConnectionStr = "Driver=SQL Server;Server=Servername;database=db;SSPI=True"

 set mConn=server.create("Adodb.Connection")
 mconn.open mConnectionStr
 mconn.execute("bulk insert [NAME] from '"&mcsvfilePath &"'")
 mconn.close
set mconn = nothing


Melih SARICA

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11878731
sorry . try this


dim mconn
dim mcsvfilePath
dim mconnectionStr

' if file uploaded succesfully

mcsvfilePath = "\\Servername\PathName\MyDate.csv"
mConnectionStr = "Driver=SQL Server;Server=Servername;database=db;SSPI=True"

 set mConn=server.createObject("Adodb.Connection")
 mconn.open mConnectionStr
 mconn.execute("bulk insert [NAME] from '"&mcsvfilePath &"'")
 mconn.close
set mconn = nothing


Melih SARICA
0
 
LVL 12

Expert Comment

by:patrikt
ID: 11878933
>>When the CSV file is accessible from the SQL server view, does tat mean that the SQL server must permit the permission to accept CSV file.. ?

No. That means that you have to have some common storrage for uploaded file.
As in non_zeros script  "\\Servername\PathName\MyDate.csv" must be anywhere on network where web server can store file after update and SQL server can read that file from.


0
 
LVL 1

Author Comment

by:ronghao
ID: 11888282
I tried using Bulk Inserting... but however, i have this "myDate.csv file not found" error.. My server is not on my computer. Im connecting to the server using ODBC connection. Why is it so? My csv file is placed in my local C: and i've set the csv file to "C:\myDate.csv". And also, the serverName, what should i put, the IP address of the SQL server which im connected to?

Is there other way to import the csv file beside using the Bulk Insert method?
0
 
LVL 1

Author Comment

by:ronghao
ID: 11888843
I managed to insert my CSV file using Bulk Insert. However, there is another problem. This is how my CSV file looks like:

==================
Num,FirstName,LastName
1,Sarah, Jane, Lim
2,Kimberly,Teo
==================

This is the codes i used to insert:

==========================================================
<%
dim mconn
dim mcsvfilePath
dim mconnectionStr

' if file uploaded succesfully

mcsvfilePath = "C:\Inetpub\wwwroot\myData.csv"
mConnectionStr = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Database;Initial Catalog=Northwind;Network Library=dbmssocn;UID=sa;PWD=;"

set mConn=server.createObject("Adodb.Connection")

mconn.open mConnectionStr

mconn.execute("bulk insert NAME from '"&mcsvfilePath&"' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')")

 mconn.close
set mconn = nothing
%>

<html>
<title>Upload CSV data into SQL Server</title>

<body>
CSV record uploaded sucuessfully
</body>
</html>
========================================================
The result i get is:

================
| 1 |Sarah | Jane, Lim |
| 2 | Kimberly | Teo |
================

The output i wanted is actually:

===============
| 1 |Sarah Jane | Lim |
| 2 | Kimberly | Teo |
===============


My final question is: How should i write my script if i wan the system to know that ", " is actually one field by itself instead of separating it into 2 different fields.
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11889274
1.. U ave to Upload ur cvs file to SQL Server. Bulk inserted text files must be in the SQL Server or on a comouter  that SQL server ave access rights on it.
2.. U must speate ur Fields with one delimeter. i mean 1st field with ';' and second with '|' 3rd .... butu cant seperate ur one field with to delimeters..


Melih SARICA
0
 
LVL 1

Author Comment

by:ronghao
ID: 11889388
> 2.. U must speate ur Fields with one delimeter. i mean 1st field with ';' and second with '|' 3rd .... butu cant seperate ur one field with to delimeters..


I don't understand ur above explanination.

The result i get is (table format):

================
| 1 |Sarah | Jane, Lim |
| 2 | Kimberly | Teo |
================

The output i wanted is actually:

===============
| 1 |Sarah Jane | Lim |
| 2 | Kimberly | Teo |
===============
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11889413
ok ..

here is the text data

1 |Sarah | Jane,Jim
2 | Kimberly | Teo

and u want it as

1 |Sarah  Jane|Jim
2 | Kimberly | Teo

rite? if yes.

lets talk about delimeters..

Here in text data delimeter is | and it seperates fields ..

there is 3 fields in ur text data
1-Row no
2-First name
3-Last name

And these 3 fields seperated with | character.

and u want to seperate one field with (,) character. and this seperations will not occur in everyrow.

delimeters covers all rows of the text data
if u wanna import this text data with
1st delimeter = |
2nd delimeter = ,

the result set u ll get ll be like this


 1 |Sarah Jane | Lim
 2 | Kimberly  Teo



Melih SARICA

0
 
LVL 1

Author Comment

by:ronghao
ID: 11890014
err.. tink you've misunderstand my question:

The csv file i have is like the following:

==================
Num,FirstName,LastName
1,Sarah, Jane, Lim
2,Kimberly,Teo
==================

I need the Sarah, Jane to be in 1 field, but however, my script splits it up into 2 field like the following:

----------------------
Sarah | Jane, Lim |    <=== This is the result i get.
----------------------


--------------------
Sarah Jane | Lim |  <=== This is wad i need
--------------------
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11890139
Cant u import ur Csv file with ; as delimeter ?

In this scene , no ay to get data as u wish , cus name seperator and delimeter char is the same.



Melih SARICA

0
 
LVL 1

Author Comment

by:ronghao
ID: 11898654
My CSV file directly save from excel, so the delimeter is  , by default. Is there anyway i can change the default delimeter to ; ?

thanks a lot of your help..
0
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 125 total points
ID: 11899645
Yes,

Save as CSV(Ms dos) , not CSV(Comma Seperated)
0
 
LVL 1

Author Comment

by:ronghao
ID: 11910251
Is there anyway that i can validate if the csv file has been updated into the database sucuessfully? How should i do it?
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11910295
If execute method pops and error event u would handle it..

0
 
LVL 1

Author Comment

by:ronghao
ID: 11910565
im new to asp, is there any reference codes for me so that i can refer?
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11910704
0
 
LVL 1

Author Comment

by:ronghao
ID: 11928548
I dun realli understand the objects stated there..
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

773 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