473,836 Members | 1,480 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

update tables from a csv file?

Hello and thank you for your time. I have never worked with sql
server and currently have a project updating the database.

I have a CSV file as follows:
id
keywords (this field contains commas within)
date

I have a sql server table as follows with only the id populated.
id
keywords
date

How do I update the sql server fields from the CSV file? I'm assuming
I would use an UPDATE statement. What SQL server utility would I use
(I'm very new at this, please be basic)?

Thank you once again,
Sunshine
Jul 20 '05 #1
3 18859
su************@ yahoo.com (Sunshine) wrote in message news:<7b******* *************** ****@posting.go ogle.com>...
Hello and thank you for your time. I have never worked with sql
server and currently have a project updating the database.

I have a CSV file as follows:
id
keywords (this field contains commas within)
date

I have a sql server table as follows with only the id populated.
id
keywords
date

How do I update the sql server fields from the CSV file? I'm assuming
I would use an UPDATE statement. What SQL server utility would I use
(I'm very new at this, please be basic)?

Thank you once again,
Sunshine


One way is to create a load table with the same structure as the
destination table, and use DTS (Import and Export Data) to load the
CSV file into it. When you've done that, then you can use a query like
this:

update dbo.MyTable
set keywords = l.keyords, date = l.date
from dbo.MyTable t
join dbo.MyTable_Loa d l
on t.id = l.id

You could also look at bcp.exe or BULK INSERT to load the CSV data.

Simon
Jul 20 '05 #2
Please post your table structure like this:

CREATE TABLE TableName
(
id int identity not null primary key,
keywords varchar(100),
[date] as datetime -- smalldatetime?
)

and a sample of your data like this:

1
KEYWORD,KEYWORD ,KEYWORD,...
05/28/04
2
KEYWORD,KEYWORD ,KEYWORD,...
05/28/04

Otherwise, we're forced to make assumptions that may not be correct.

Thanks.
"Sunshine" <su************ @yahoo.com> wrote in message
news:7b******** *************** ***@posting.goo gle.com...
Hello and thank you for your time. I have never worked with sql
server and currently have a project updating the database.

I have a CSV file as follows:
id
keywords (this field contains commas within)
date

I have a sql server table as follows with only the id populated.
id
keywords
date

How do I update the sql server fields from the CSV file? I'm assuming
I would use an UPDATE statement. What SQL server utility would I use
(I'm very new at this, please be basic)?

Thank you once again,
Sunshine

Jul 20 '05 #3
Solution 1:
~~~~~~~~~~~
Use DTS and transfer the contents from the CSV file to a table. Join
the original table in the SQL Server and the table created using DTS
and update the table in sql server

Solution 2:
~~~~~~~~~~~
Create linked server in SQL Server for the CSV file. Join and update
the table in sql server.

Hope this helps
Ramesh

su************@ yahoo.com (Sunshine) wrote in message news:<7b******* *************** ****@posting.go ogle.com>...
Hello and thank you for your time. I have never worked with sql
server and currently have a project updating the database.

I have a CSV file as follows:
id
keywords (this field contains commas within)
date

I have a sql server table as follows with only the id populated.
id
keywords
date

How do I update the sql server fields from the CSV file? I'm assuming
I would use an UPDATE statement. What SQL server utility would I use
(I'm very new at this, please be basic)?

Thank you once again,
Sunshine

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3091
by: Pesko S | last post by:
Hi, Could anybody just point me in a direction where I can find information on how the heck I can update a database with relational data from an XML file. I use stored procedures to insert the data. And I am NOT storing the xml in the database. I want to map the xml elements to the database columns. I am about to start peeling my skin off in frustration about this. There
2
1469
by: nek | last post by:
Seasons greetings, Platform is W2K SP4, DB2 V8.1 FP2. A few days ago I posted the question to globally 'update db cfg' for all servers which could be done via a script thanks to Ian and PM's responses. I would like to do the same to a file containing plain text that exists on ALL W2K servers which are of identical h/w configuration.
4
2203
by: Jozef | last post by:
Hello, I'm trying to check for and add a field to a table to a back end database through code. The problem I've been faced with is changing permissions, because I have to use administer permissions to change the design, then remove the delete permissions when the design has been changed. I have to do it in code because there are several sites that need an automated update (as opposed to manually adding the field). The platform is...
2
1373
by: siaj | last post by:
Hi.. I m just trying to learn using XML file as a datastore. I am reading a dataset from a XML file and then trying to update the XML file with some changes in the dataset. My code is as follwoing. Private Sub ReadUpdateData() Dim dsUser As New DataSet dsUser.ReadXml(Server.MapPath("Datafiles\Users.xml"))
12
1993
by: facicad | last post by:
Look my prog. I have combobox where is have all tables names of access file. The user select one table and the content is show in datagrid. The user can add, erase or modify some record or row. Until now is ok but went I want to update access file, VB send me error. Some body can help me Dim myCnn As New OleDb.OleDbConnection Dim objDataAdapteur As OleDbDataAdapter
2
1860
by: Subhashini | last post by:
Hi friends, i have one doubt, that how can we update a file without using a temporary file in C. i need to do it in my program. If there is any way to do so then please let me know the procedure. Thanks N regards Subu
0
1365
by: Patty05 | last post by:
Can someone help me please. I can not figure out what is wrong. I get the error: "An unhandled exception of type 'System.Data.OleDb.OleDbException' occured in system.data.dll. I am simply trying to update a field in a database by using the update command as follows: Imports System.Data.OleDb Dim myOleDbDataAdapter As OleDbDataAdapter Dim myDataSet As DataSet = New DataSet Dim myDataTable As DataTable = New DataTable ...
0
1310
by: =?Utf-8?B?TWF0dA==?= | last post by:
Hello, Can someone please advise. I have been unable to install any security, wmp and explorer updates for some time. Looking at the logs there seems to be trouble with the location of the update.inf file and this seems to tie in with other peoples problems on the newsgroup. However, on performing a file search to find the location of this file i find it does not exist, only in the downloaded updates.
1
1248
by: SKODA | last post by:
Hi, Could someone help me in knowing how to update tables in MS Access with data available on a website? I mean the functionality similar to refresh available in MS EXCEL where the data is refreshed from website based on the tables chosen in the web query. Can refresh happen in a single click like that?
0
9816
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10840
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9371
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6978
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5647
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4448
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.