Creating insert statements with excel 1

[doorbreaker]

Hi,

I'm a bit useless at using excel and was wondering if anyone can help me create an insert script for my database?

At the moment on my worksheet I have the following:

productname    prodid    rc       spy    fitness   other
testa          1         1           
testb          2         1        1
testc          3                         1

I am looking to create this:

insert into category_lookup (productid, category) values (1, 'rc');
insert into category_lookup (productid, category) values (2, 'rc');
insert into category_lookup (productid, category) values (2, 'spy');
insert into category_lookup (productid, category) values (3, 'fitness');

and so on......

Basically a '1' in any of my category columns (rc, spy, fitness, other) means an insert statement is needed.

Does anyone know the simple vba to do this or a formula??

Hope you can help

Thanks

Chris

 

[SkipVought]

Chris,

Some questions...

how does

insert into category_lookup (productid, category) values (1, 'rc');

relate to

productname prodid rc spy fitness other
testa 1 1

where is productname included in your insert?



Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[doorbreaker]

oops sorry.

in the insert i only need to insert the productid and the category.

ah, i see the formatting on the page has gone awry.

the productid is 1

and a 1 is used to show that rc is a category that should be attributed to productid 1. Does that make any sense?

 

[doorbreaker]

I could send my excel sheet if that would help....

it might be a lot easier to see what i mean from that.

 

[SkipVought]

Assuming that prodid is in B1...

dim r as range, sPID as string, c as range, sSql as string
for each r in range(cells(2,"B"), Cells(2,"B").end(xldown))
  with r
    sSql = "insert into category_lookup (productid, category) values (" & .value & ","
    for each c in range(cells(.row,"C"), cells(.row,"C').end(xltoright))
       with c
         if .value <> "" then
            sSql = sSql & "'" & .Value & "');"
         end if
       end with
    next
  end with
next

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[doorbreaker]

hi,

i ran the macro and got the following line as having a syntax error:

for each c in range(cells(.row,"C"), cells(.row,"C').end(xltoright))

any ideas what may be wrong?

thanks

chris

 

[PHV]

Replace this:
,"C').
By this:
,"C").

 

[doorbreaker]

This code doesn't seem to do anything at the moment. How do i write the insert statements to either a cell or external file?

any help welcome

thanks

chris

 

[SkipVought]

for each c in range(cells(.row,"C"), cells(.row,"C[red][b]"[/b][/red]).end(xltoright))

"How do i write the insert statements to either a cell or external file?"

I thought you were inserting rows into a table via SQL using either DAO or ADO.


Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[doorbreaker]

No,

I was hoping just to write them to a text file.

Any ideas - is this difficult?

Thanks

 

[SkipVought]

Take a look at Write, Print # etc. The method that you choose will depend on your requirements. You will also need to use the Open & Close methods to open and close your output file. Also look at the FreeFile function to use in conjunction with the Open method.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[doorbreaker]

thanks for the tips skip, i will look into how to write to a txt file.

cheers

chris

 

[doorbreaker]

Ok,

I've done a bit of work on this but still having trouble:

If a value is in a certain column then the appropriate value is written into the insert statement in the generated file:

Sub test()
Dim r As Range, sPID As String, c As Range, sSql As String
Open "Datafile.txt" For Output As #1
For Each r In Range(Cells(2, "B"), Cells(2, "B").End(xlDown))
  With r
      sSql = "insert into category_lookup (productid, category) values (" & .Value & ","
      For Each c In (Cells(.Row, "C"), Cells(.Row, "D"), Cells(.Row, "E"), Cells(.Row, "F"), Cells(.Row, "G"))
        With c
         If .Value <> "" Then
         If .Cells(.Row, "C") Then
            .Value = "RC"
         ElseIf .Cells(.Row, "D") Then
            .Value = "Spy"
         ElseIf .Cells(.Row, "E") Then
            .Value = "Fitness"
         ElseIf .Cells(.Row, "F") Then
            .Value = "Fitness"
         ElseIf .Cells(.Row, "G") Then
            .Value = "Photo"
         End If
            sSql = sSql & "'" & .Value & "');"
            sSql = sSql & vbCrLf
         End If
       End With
       Print #1, sSql
    Next
  End With
Next
Close #1
End Sub

I have been able to generate a text file but it wasn't as I needed - it just contained a 1 instead of a particular category value. So I've modified it and now its erroring on this line:

For Each c In (Cells(.Row, "C"), Cells(.Row, "D"), Cells(.Row, "E"), Cells(.Row, "F"), Cells(.Row, "G"))

anyone know why?

Sorry to bang on about this

Cheers
Chris

 

[SkipVought]

i messed you up with my original post. Try this...

Dim r As Range, sPID As String, c As Range, sSql As String
For Each r In Range(Cells(2, "B"), Cells(2, "B").End(xlDown))
  With r
    For Each c In Range(Cells(.Row, "C"), Cells(.Row, "F"))
       With c
         If .Value <> "" Then
            sSql = "insert into category_lookup (productid, category) values (" & r.Value & ","
            sSql = sSql & "'" & Cells(1, c.Column) & "');"
         End If
       End With
    Next
  End With
Next

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[doorbreaker]

Hey Skip,

this works:

Sub testtest()
Dim r As Range, sPID As String, c As Range, sSql As String
Open "Datafile.txt" For Output As #1
For Each r In Range(Cells(2, "B"), Cells(2, "B").End(xlDown))
  With r
    For Each c In Range(Cells(.Row, "C"), Cells(.Row, "M"))
       With c
         If .Value <> "" Then
            sSql = "insert into category_lookup (productid, category) values (" & r.Value & ","
            sSql = sSql & "'" & Cells(1, c.Column) & "');"
            sSql = sSql & vbCrLf
         End If
       Print #1, sSql
       End With
    Next
  End With
Next
Close #1
End Sub

All the necessary insert statements are written e.g:

insert into category_lookup (productid, category) values (6,'Health and Beauty');

But it writes the statement loads of times before going to the next one. Sometimes it writes the same one 10 times, other times 41 times, 9 times, seems very odd.

I can go through it manually and remove the ones that don't need to be there but I just wondered if maybe you had an idea on why this may be occurring?

Thanks a lot for your help on this matter, its very much appreciated.

Chris

 

[SkipVought]

...
         If .Value <> "" Then
            sSql = "insert into category_lookup (productid, category) values (" & r.Value & ","
            sSql = sSql & "'" & Cells(1, c.Column) & "');"
            sSql = sSql & vbCrLf
            [b]Print #1, sSql  'move within if statement[/b]
         End If
...
[code]

Skip,
[sub]
[glasses] [b][red]A palindrome gone wrong?[/red][/b]
A man, a plan, a ROOT canal...
[b]PULLEMALL![/b][tongue][/sub]

 

[doorbreaker]

Perfect!

Thanks a lot for your help. I learnt a fair bit too about vba having to work through this.

Chris

 

[SkipVought]

"I learnt a fair bit too about vba having to work through this."

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]