loopExcelExportAsSQL

-- DROP TABLE IF EXISTS  PurchaseHistory;
-- CREATE TABLE PurchaseHistory (buyer VARCHAR(10),PN VARCHAR(20),desc VARCHAR(255),Supplier VARCHAR(255),Price DOUBLE,Qty DOUBLE,PurchaseDate date); 
-- INSERT INTO PurchaseHistory ('buyer','PN','desc','Supplier','Price','Qty','PurchaseDate')
-- VALUES
-- ('DEFAULT','2610-2004','COUPLER - M16','XXX Services','30','3.006','01/12/2014'),
-- ('DEFAULT','2619-2011','EXE DOME HD M20 STOPPING PLUG','XXX Services','36','3.45','01/12/2014')
-- ;
-- DELETE  FROM PurchaseHistory WHERE buyer ="test";

'code as here

Sub TransferToTxt()

Toff

Dim i, j As Long
Dim buyer As String
Dim pn As String
Dim desc As String
Dim supplier As String
Dim price As Double
Dim qty As Double
Dim purchasedDate As Date
Dim category As String
Dim deter As Double
Dim r As String

Dim filePath As String
filePath = CStr(Environ("USERPROFILE")) & "\Documents\0 Sppliers\0 Temp\" & "BuyHistory.sql"

Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim fileStream As TextStream
Set fileStream = fso.CreateTextFile(filePath, True)
'
Dim sht As Worksheet
Dim lastrow, lastcol As Long
Set sht = ActiveSheet
  lastrow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).row
  lastcol = sht.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
  
fileStream.WriteLine ("DROP TABLE IF EXISTS  PurchaseHistory;")
fileStream.WriteLine ("CREATE TABLE PurchaseHistory (buyer VARCHAR(10),PN VARCHAR(20),desc VARCHAR(255),Supplier VARCHAR(255),Price DOUBLE,Qty DOUBLE,PurchaseDate date); ")
fileStream.WriteLine ("INSERT INTO PurchaseHistory (" & "'buyer'," & "'PN'," & "'desc'," & "'Supplier'," & "'Price'," & "'Qty'," & "'PurchaseDate'" & ")")
fileStream.WriteLine ("VALUES")
            
For i = 8 To lastrow
    For j = 5 To lastcol
        If i Mod 2 <> 0 And Cells(i, j).Value2 > 0 Then

            buyer = Cells(i, 1).text
            pn = Cells(i, 2).text
            desc = Replace(Cells(i, 3).text, "'", "")' trim quote if exsiting in desc
            supplier = Cells(i, 4).text
            qty = Cells(i, j).Offset(-1, 0)
            price = Cells(i, j).Value
            purchasedDate = Format(GetPdate(Cells(7, j)), "DD/MM/YYYY")
            
            Debug.Print ("('" & buyer & "','" & pn & "','" & desc & "','" & supplier & "','" & price & "','" & qty & "','" & purchasedDate & "'),")
            fileStream.WriteLine ("('" & buyer & "','" & pn & "','" & desc & "','" & supplier & "','" & price & "','" & qty & "','" & purchasedDate & "'),")
                    
    End If
    
        Application.StatusBar = "Working on row " & i & " of total rows " & lastrow & " column " & j & " of total columns " & lastcol
        
    Next j
Next i

    fileStream.WriteLine ("('test','test','test','test','0','0','01/01/1900')")
    fileStream.WriteLine (";")
    fileStream.WriteLine ("DELETE FROM PurchaseHistory WHERE buyer ='test';")
    
    MsgBox "Done."
    
    fileStream.Close
    Set fso = Nothing
    Set fileStream = Nothing
Ton

End Sub


Sub formatABCDcolumns()

Toff

ActiveSheet.UsedRange.Unmerge
ActiveSheet.Hyperlinks.Delete
ActiveWindow.FreezePanes = False

Dim i As Long
Dim lastrow As Long
Dim lastcol As Long
Dim myColumn As Long

Dim sht As Worksheet
Set sht = ActiveSheet

  lastrow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).row
  lastcol = sht.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
  
 
    For i = 1 To lastcol
   
        If Cells(7, i).Value = "" Then
            Columns(i).EntireColumn.Delete
        End If
              
    Next i
    
    For myColumn = 1 To 4
        For i = 9 To lastrow + 1
            If Cells(i, myColumn) = "" Then
                Range(Cells(i - 1, myColumn), Cells(i, myColumn)).FillDown
            End If
        Next
        i = i + 2
    Next myColumn
 
Ton

End Sub

Function GetPdate(D As String) As Date
Dim Pdate As Date
Dim MM As Integer
Dim YYYY As Integer
    MM = Right(D, 2)
    YYYY = Left(D, 4)
    GetPdate = "01" & "/" & MM & "/" & YYYY
End Function