Solved

Importing CSV file into SQL Server

Posted on 2004-08-23
19
294 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
[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
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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 1

Author Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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…
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.

630 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