|
The database load command has two functions:
1) It will be used to populate database tables from CSV files
2) It will be used to update database tables from CSV files
The command might be run in Sun Solaris (sparc) or in Windows 2000
machine. We are assuming that will not get local access to their database
server, the access is probably happen over JDBC or/and ODBC.
Database will be Microsoft SQL Server 2000 SP3.
Please note that a updated CSV file will be loaded to the database
once a month (the function 2) above). The load command should
not duplicate existing rows in tables, and it should not cause
production downtime, i.e. it must not just delete all existing rows
(or drop existing table) and create all rows from scratch as
that kind of update would cause production downtime.
I have included few lines from beginning of two of the CSV files.
As you can see, the first line in CSV file lists the column names.
The corresponding database tables will have same column names to
make things easier. All columns won’t be VARCHARs, some
of them will be floating point numbers (longitude and latitude must
be floating point numbers in the tables).
--- postalcode.csv ---
"POSTAL_CODE","CITY","PROVINCE","PROVINCE_ABBR","AREA_CODE","CITY_FLAG","TIME_ZONE","DAY_LIGHT_SAVING","LATITUDE","LONGITUDE"
"A0A 1A0","AQUAFORTE","NEWFOUNDLAND","NF","709","N","3.5","Y","47.007050","-52.95195"
"A0A 1B0","AVONDALE","NEWFOUNDLAND","NF","709","N","3.5","Y","47.416880","-53.19755"
"A0A 1C0","BAY BULLS","NEWFOUNDLAND","NF","709","N","3.5","Y","47.321210","-52.81743"
"A0A 1E0","BAY DE VERDE","NEWFOUNDLAND","NF","709","N","3.5","Y","48.086930","-52.89581"
--- postalcode.csv ---
--- zipcode.csv ---
"ZIP_CODE","CITY","STATE","AREA_CODE","CITY_ALIAS_NAME","CITY_ALIAS_ABBR","CITY_TYPE","COUNTY_NAME","COUNTY_FIPS","TIME_ZONE","DAY_LIGHT_SAVING","LATITUDE","LONGITUDE","ELEVATION"
"00501","HOLTSVILLE","NY","516","I R S SERVICE CENTER","","N","SUFFOLK","103","5","Y","40.81518","-73.0455","25"
"00501","HOLTSVILLE","NY","516","HOLTSVILLE","","P","SUFFOLK","103","5","Y","40.81518","-73.0455","25"
--- zipcode.csv ---
dadsadasd
|
|
|
|
|
Hate to be blunt, but what's the question you want answered? 
Chris Meech
I am Canadian. [heard in a local bar]
I think people should be required to have an operator's permit to use the internet. John Simmons
|
|
|
|
|
Not sure what your question is either 
But if you are asking for help creating this loading program, here's an article right here on code project that I found helpful.
http://www.codeproject.com/cs/database/inifileado.asp
|
|
|
|
|
Hi, everybody,
How to get(use sql statement) the name of the default on a column when I use "col_name int not null default 'aa'" create the table in MS SQL Server.
Thanks a lot
Alan
|
|
|
|
|
Hello all,
I have a set of legacy applications that use dbaseIII+, containing English, as well as data in ISCII format. These have to be transferred to SQL Server. I am working on an XP with SQL Server 2000 installed.
In the DTS' Import/Export wizard, after the data source has been selected as 'Dbase III', the file name containing the data, and user name /password are asked.
( Ofcourse, dbaseIII+ in itself does not have any name/password. ) After giving the file name, if the user name and password are not given, the following error message comes up -
"Invalid date setting in the Xbase key of the Windows Registry".
If, along with the filename, the user name / password combination is entered (the one used at the time of logging in), then the following error message is thrown -
"Cannot start your application. The workgroup information file is missing or opened exclusively by another user."
RegEdit on the key -
HKEY_LOCAL_MACHINE\software\microsoft\jet\4.0\engines\Xbase - date
showed the data equal to 'MDY'. I changed this to 'MM/DD/YY'. Still no dice.
This same error of "Invalid date setting ..." appears when i try to do an import on the dbase file in Access 2003. Though, in the Access 2003/Import window, upon specifying the file type as 'dbaseIII', all
the files are listed properly, indicating that the files as such are being recognized correctly by the system.
What could be the reason for this problem, and how does one correct it. I appreciate any pointers that could resolve this problem.
Regards,
Sanjay.
P.S. : The dbf file does not have any dbt/index etc. It also does not have any date field. All fields are character.
|
|
|
|
|
Hi
How can I return the row no. in my query result?
Like the presentation of SQL analyzer in GRID VIEW, please help me…
e.g.
The first column represent the Row No.
---------------------------------------
| | field1 | field2 | field3 | --- this is the field header
---------------------------------------
| 1 | 1 | dsdfsf | sdfsdd | --- Result with 2 rows
---------------------------------------
| 2 | 1233 | asdfsf | wefsdd |
---------------------------------------
If you look at my sample the first column represent the row number.
If my query look like this “Select * From Table”,
How can I return the row number it self with my returning field and row value.
Mark
|
|
|
|
|
you should handle this display issue in your client code..
but if you insist to do this in SQL server .. ok
here is a sample :
set nocount on<br />
create table #tempResult<br />
(MyID int identity,col1 varchar(30),col2 varchar(30))<br />
<br />
<br />
insert into #tempResult (col1,col2) Select lastname , firstname from employees<br />
set nocount off<br />
select * from #tempResult<br />
drop table #tempResult
|
|
|
|
|
I have a program that imports data from an excel spreadsheet into a dataset. I then create a connection to sql server and check to see if a table exist, drop it and recreate it. I cant figure out how to poulate the newly create sql server table with the dataset from excel. any help would be great. Here is the code:
Imports System.Data.OleDb
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents TabControl1 As System.Windows.Forms.TabControl
Friend WithEvents ProgressBar1 As System.Windows.Forms.ProgressBar
Friend WithEvents TabPage1 As System.Windows.Forms.TabPage
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents TabPage2 As System.Windows.Forms.TabPage
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
<system.diagnostics.debuggerstepthrough()> Private Sub InitializeComponent()
Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(Form1))
Me.TabControl1 = New System.Windows.Forms.TabControl()
Me.TabPage1 = New System.Windows.Forms.TabPage()
Me.Button2 = New System.Windows.Forms.Button()
Me.Button1 = New System.Windows.Forms.Button()
Me.TabPage2 = New System.Windows.Forms.TabPage()
Me.ProgressBar1 = New System.Windows.Forms.ProgressBar()
Me.DataGrid1 = New System.Windows.Forms.DataGrid()
Me.TabControl1.SuspendLayout()
Me.TabPage1.SuspendLayout()
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'TabControl1
'
Me.TabControl1.Controls.AddRange(New System.Windows.Forms.Control() {Me.TabPage1, Me.TabPage2})
Me.TabControl1.Location = New System.Drawing.Point(8, 40)
Me.TabControl1.Name = "TabControl1"
Me.TabControl1.SelectedIndex = 0
Me.TabControl1.Size = New System.Drawing.Size(544, 360)
Me.TabControl1.TabIndex = 3
'
'TabPage1
'
Me.TabPage1.Controls.AddRange(New System.Windows.Forms.Control() {Me.Button2, Me.Button1})
Me.TabPage1.Location = New System.Drawing.Point(4, 22)
Me.TabPage1.Name = "TabPage1"
Me.TabPage1.Size = New System.Drawing.Size(536, 334)
Me.TabPage1.TabIndex = 0
Me.TabPage1.Text = "Step 1 - Import EDI"
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point(8, 40)
Me.Button2.Name = "Button2"
Me.Button2.Size = New System.Drawing.Size(136, 24)
Me.Button2.TabIndex = 6
Me.Button2.Text = "Transfer EDI to Visual"
Me.Button2.TextAlign = System.Drawing.ContentAlignment.MiddleLeft
'
'Button1
'
Me.Button1.AccessibleDescription = ""
Me.Button1.Cursor = System.Windows.Forms.Cursors.Hand
Me.Button1.Image = CType(resources.GetObject("Button1.Image"), System.Drawing.Bitmap)
Me.Button1.ImageAlign = System.Drawing.ContentAlignment.MiddleRight
Me.Button1.Location = New System.Drawing.Point(8, 8)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(136, 24)
Me.Button1.TabIndex = 0
Me.Button1.Tag = ""
Me.Button1.Text = "Load EDI from Deere"
Me.Button1.TextAlign = System.Drawing.ContentAlignment.MiddleLeft
'
'TabPage2
'
Me.TabPage2.Location = New System.Drawing.Point(4, 22)
Me.TabPage2.Name = "TabPage2"
Me.TabPage2.Size = New System.Drawing.Size(536, 334)
Me.TabPage2.TabIndex = 1
Me.TabPage2.Text = "Step 2 - Compare Data"
'
'ProgressBar1
'
Me.ProgressBar1.Location = New System.Drawing.Point(416, 16)
Me.ProgressBar1.Name = "ProgressBar1"
Me.ProgressBar1.Size = New System.Drawing.Size(136, 13)
Me.ProgressBar1.TabIndex = 4
'
'DataGrid1
'
Me.DataGrid1.CaptionForeColor = System.Drawing.Color.Yellow
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(16, 136)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(528, 256)
Me.DataGrid1.TabIndex = 0
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(560, 405)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.TabControl1, Me.ProgressBar1})
Me.Name = "Form1"
Me.Text = "Form1"
Me.TabControl1.ResumeLayout(False)
Me.TabPage1.ResumeLayout(False)
CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' Create connection string variable for excel.
Dim a As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=c:\supplier requirements.xls;" _
& "Extended Properties=Excel 8.0;"
' Create the connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(a)
' Open connection with the database.
objConn.Open()
' The code to follow uses a SQL SELECT command to display the data from the worksheet.
' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("Select * from [supplier requirements$]", objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter As New OleDbDataAdapter()
' Pass the Select command to the adapter.
objAdapter.SelectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.
Dim objDataset As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter.AcceptChangesDuringFill = False
objAdapter.Fill(objDataset, "XLData")
' Build a table from the original data.
DataGrid1.DataSource = objDataset.Tables(0).DefaultView
'get row count
Dim rows As DataRow()
Dim numberofrows As Integer
rows = objDataset.Tables(0).Select()
numberofrows = rows.Length
Me.DataGrid1.CaptionText = CStr(numberofrows) + " rows succesfully loaded into dataset."
Dim SQLConnection As New System.Data.SqlClient.SqlConnection()
SQLConnection.ConnectionString = "user id=sa; password=sa; initial catalog=akgpilot; server=akg-chris-l\vsdotnet;"
SQLConnection.Open()
Dim sqlString As String = "If exists (SELECT name FROM sysobjects WHERE name = N'JD_EDI' AND type = 'U') DROP TABLE JD_EDI"
Dim sqlString2 As String = "Create Table JD_EDI (Type varchar)"
Dim sqlStringCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlString, SQLConnection)
sqlStringCommand.ExecuteNonQuery()
Dim sqlStringCommand2 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlString2, SQLConnection)
sqlStringCommand2.ExecuteNonQuery()
|
|
|
|
|
Hi,
I would like to find out how to delete, or at least modify a stored procedure. I'm using MS Access DB, and add stored procedure using C#.
Thanks!
|
|
|
|
|
I may be mistaken but: MS Access doesn't have stored procedures.
|
|
|
|
|
MS Access does have a kind of stored procedure. I did a test on it before and it worked.
The stored procedures are basically queries that can receive parameters, so you should find out how to create queries in Access using C#.
I specified SQL statements to create these 'stored-procedures' before and I suppose you can pass statements to create them from C#.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thank you for your answer. However, I do know how to create stored procedures, I don't know how to delete them or modify them when they are defined already...
Thanks anyway...
|
|
|
|
|
|
Thank you so much! on both answers!
You are a life-saver.
|
|
|
|
|
I'm trying to use the following code to create a backup set that would store the backup as 20040608.bak
USE master
GO
DECLARE @curdate as INT
SET @curdate = (SELECT CONVERT(char(12), GETDATE(), 112))
EXEC sp_addumpdevice 'disk','MyNwind_1', 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @curdate + ".dat"
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
I'm getting the following error
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.
Why am I unable to concat the @curdate tot the string
|
|
|
|
|
Just a guess, but is it since you have @Curdate defined as an Int, and you're attempting to do a string concatenation on it?
Like I say - just a guess, but would personally try CASTing or CONVERTing @CurDate first....
BTW - there is also no need to create the dump device first - you can use the syntax:
<br />
BACKUP DATABASE NWind TO DISK='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\'....<br />
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
".dat" may need to be changed to '.dat' depending upon your SQL Configuration.
|
|
|
|
|
try this:
USE master
GO
DECLARE @curdate as char(8)
DECLARE @filePath as varchar(1024)
SET @curdate = (SELECT CONVERT(char(8), GETDATE(), 112))
SET @filePath = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @curdate + '.bak'
EXEC sp_addumpdevice 'disk','MyNwind_1', @filePath
There are a couple of problems with your code. First, as already stated, you're trying to add an int to a varchar, which would cause problems. This isn't the error you're getting, but it would have been the next.
When you pass parameters into a proc via EXEC, you have to pass constants or variables. You can't pass expressions that must be evaluated. ("Path\" + @file + ".bak" would be an expression.)
Grim (aka Toby) MCDBA, MCSD, MCP+SB
|
|
|
|
|
Thank you all for the comments and support. I'm new to T-SQL. Need hours and hours of practice 
|
|
|
|
|
I use C++, COM,
so sample from VB is good.
first- I need to enumerate SQL servers on network,
Especially interesting now:
and secondly to enumerate data bases within some SQL server,
how to do it?
Thanks.
|
|
|
|
|
|
When iam extracting a date from SQL Svr 7 and i want it to be in a specific format, isn't there a SQL command that will do the formatting?
system date '2004-06-09 13:43:41.363' i want this date in 'mm\dd\yy' format
If so, could you give me the syntax and the associated style codes.
Thanks
syed saba
|
|
|
|
|
this works on 2000
check it on 7 :
select convert(varchar(10),DateValue,1)
|
|
|
|
|
I concur with hspc. There are about 20 options for formatting the date. Look at the help on convert in SQL which will give you the parameters and examples.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hi,
I have a question. I'm inserting a row into a table in Access DB (without using the command builder), one of the coloumns (the primary key, actually) in the table is an AutoNumber - which gets updated when I insert.
Does anyone know how can I get the coloumn's value?
thanks!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
