Inset into SQL

[Striker]

For a long time now I have been using the following code to extract data
from a CSV file, saving the part I want as an Excel file, then going to SQL
and importing the data. I recently found that I can skip the step of
importing into SQL if I can figure out how to extract the date and insert
directly to SQL.

In the code below I have commented out directly below the DIM statements
until I can work out how to do the insert. Suppose the part where I am
adding a TRIM(OFFSET) to a cell I wanted to add directly to the SQL table,
How would I do this?

Also suppose I wanted to add the result of the statement '& Trim(.Offset(0,
1).Value) & Trim(lSerialDate) & Trim(lCounter)
to the UID field of the record in the SQL table.

I guess I'm really asking How can I insert each variable into one record,
then move to the next record in SQL?

Thanks for any input.

~~~~~~~~~~~~~~~ C O D E ~~~~~~~~~~~~~~~~

Sub AddUID()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim lLastRow As Long
Dim lCounter As Long
Dim lSerialDate As Long
Dim sPath As String
Dim sFile1 As Variant
Dim rDestCell As Range
Dim wkbkCSV As Workbook
Dim sTerm As String
'Dim MyConnection As ADODB.Connection
'Dim MySQL As String

'Set MyConnection = New ADODB.Connection
'MyConnection.Open "driver=SQL
Server;server=temsl01;DATABASE=Str;UID=striker;PWD=striker;"



'MyConnection.Execute MySQL
'MyConnection.Close
'Set MyConnection = Nothing

ChDrive "K:\Test\Test"
sFile1 = Application.GetOpenFilename("CSV Files, *.csv")
If sFile1 = False Then
Exit Sub
End If

'Set objects
Set WB = Workbooks("Template_Assigned.xls")
Set SH = WB.Sheets("CO_Template")
Set rDestCell =
Workbooks("Template_Assigned.xls").Worksheets("CO_Template").Range("A2")
Set wkbkCSV = Workbooks.Open(Filename:=sFile1)
sPath = "K:\Test\Test\RepAssigned" + "\" + "CO " + "Import Date " + Date$
sTerm = "!!"
lCounter = 1
lSerialDate = DateValue(Now)

wkbkCSV.Worksheets(1).UsedRange.Copy Destination:=rDestCell
wkbkCSV.Close savechanges:=False

With WB
'Find the lastRow in column A with data
lLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Set rng = Range("A2:A" & lLastRow)

For Each rCell In rng.Cells
With rCell
.Offset(0, 5).Value = Trim(.Value) _
& Trim(lSerialDate) & Trim(lCounter)
.Offset(0, 6).Value = sTerm
.Offset(0, 7).Value = sTerm
.Offset(0, 8).Value = sTerm
'& Trim(.Offset(0, 1).Value) & Trim(lSerialDate) &
Trim(lCounter)
lCounter = lCounter + 1
End With
Next rCell
lCounter = lCounter - 1

End With
'Let user know how many redcords were imported
MsgBox "You Imported " & lCounter & " Records, PressOK to save this file
with the current Date.", vbInformation

'Force Save As, so Template does not get over written.
ActiveWorkbook.SaveAs Filename:=sPath

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Dave Patrick]

If you can post a more condensed version of what you're after someone may be
able to help.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| For a long time now I have been using the following code to extract data
| from a CSV file, saving the part I want as an Excel file, then going to
SQL
| and importing the data. I recently found that I can skip the step of
| importing into SQL if I can figure out how to extract the date and insert
| directly to SQL.
|
| In the code below I have commented out directly below the DIM statements
| until I can work out how to do the insert. Suppose the part where I am
| adding a TRIM(OFFSET) to a cell I wanted to add directly to the SQL table,
| How would I do this?
|
| Also suppose I wanted to add the result of the statement '&
Trim(.Offset(0,
| 1).Value) & Trim(lSerialDate) & Trim(lCounter)
| to the UID field of the record in the SQL table.
|
| I guess I'm really asking How can I insert each variable into one record,
| then move to the next record in SQL?
|
| Thanks for any input.
|
| ~~~~~~~~~~~~~~~ C O D E ~~~~~~~~~~~~~~~~
|
| Sub AddUID()
| Dim WB As Workbook
| Dim SH As Worksheet
| Dim rng As Range
| Dim rCell As Range
| Dim lLastRow As Long
| Dim lCounter As Long
| Dim lSerialDate As Long
| Dim sPath As String
| Dim sFile1 As Variant
| Dim rDestCell As Range
| Dim wkbkCSV As Workbook
| Dim sTerm As String
| 'Dim MyConnection As ADODB.Connection
| 'Dim MySQL As String
|
| 'Set MyConnection = New ADODB.Connection
| 'MyConnection.Open "driver=SQL
| Server;server=temsl01;DATABASE=Str;UID=striker;PWD=striker;"
|
|
|
| 'MyConnection.Execute MySQL
| 'MyConnection.Close
| 'Set MyConnection = Nothing
|
| ChDrive "K:\Test\Test"
| sFile1 = Application.GetOpenFilename("CSV Files, *.csv")
| If sFile1 = False Then
| Exit Sub
| End If
|
| 'Set objects
| Set WB = Workbooks("Template_Assigned.xls")
| Set SH = WB.Sheets("CO_Template")
| Set rDestCell =
| Workbooks("Template_Assigned.xls").Worksheets("CO_Template").Range("A2")
| Set wkbkCSV = Workbooks.Open(Filename:=sFile1)
| sPath = "K:\Test\Test\RepAssigned" + "\" + "CO " + "Import Date " + Date$
| sTerm = "!!"
| lCounter = 1
| lSerialDate = DateValue(Now)
|
| wkbkCSV.Worksheets(1).UsedRange.Copy Destination:=rDestCell
| wkbkCSV.Close savechanges:=False
|
| With WB
| 'Find the lastRow in column A with data
| lLastRow = Cells(Rows.Count, "A").End(xlUp).Row
|
| Set rng = Range("A2:A" & lLastRow)
|
| For Each rCell In rng.Cells
| With rCell
| .Offset(0, 5).Value = Trim(.Value) _
| & Trim(lSerialDate) & Trim(lCounter)
| .Offset(0, 6).Value = sTerm
| .Offset(0, 7).Value = sTerm
| .Offset(0, 8).Value = sTerm
| '& Trim(.Offset(0, 1).Value) & Trim(lSerialDate) &
| Trim(lCounter)
| lCounter = lCounter + 1
| End With
| Next rCell
| lCounter = lCounter - 1
|
| End With
| 'Let user know how many redcords were imported
| MsgBox "You Imported " & lCounter & " Records, PressOK to save this
file
| with the current Date.", vbInformation
|
| 'Force Save As, so Template does not get over written.
| ActiveWorkbook.SaveAs Filename:=sPath
|
| End Sub
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|