Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Importing CSV file into SQL Server

Posted on 2004-08-23
19
Medium Priority
?
295 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
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.

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

722 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