Solved

Importing CSV file into SQL Server

Posted on 2004-08-23
19
288 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

757 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

22 Experts available now in Live!

Get 1:1 Help Now