Convert VBA code to run in SQL server

I have this script in VBA. Which I need to get into a stored procedure ASAP in SQL Server 2008.

Any help on how I can replicate this in SQL would be greatly appreciated.

I am opening up a text file and reading through the lines 1 by 1 to find the values in my columnname array. If the value is found then it goes to the next row and stores that in another array in the same position as the columnname. I will then write it to a table rather than just pasting the results into a text box.


Option Compare Database

Private Sub Command0_Click()
Dim textline
Dim sqlstr As String
Dim rowcount, column, columncount As Integer
Dim columnname, info As Variant
ReDim info(0 To 7)
columnname = Array("Contact name:", "Company:", "Address:", "Activity:", "Postcode:", "Town/City:", "Email:", "Phone Number:")
rowcount = 0
column = 0
x = 0
sqlfile = LaunchCD(Me)
Open sqlfile For Input As #1
Do While Not EOF(1)
    Line Input #1, textline
    Debug.Print textline
    text1 = Replace(textline, "  ", "")
    'MsgBox textline
    If rowcount = 0 Then
        Do While Not x = 8
            If InStr(text1, columnname(x)) = 1 Then
                rowcount = 1
                columncount = x
                'MsgBox columnname(x)
                x = 7
            End If
        
            x = x + 1
        Loop
            x = 0
    ElseIf rowcount = 1 Then
        'MsgBox text1
        info(columncount) = text1
        rowcount = 0
    End If
'MsgBox rowcount
x = 0
Loop


Do While Not x = 8
        'MsgBox info(x)
        sqlstr = sqlstr & vbNewLine & columnname(x) & "   " & info(x)
        x = x + 1
    Loop

Me.Text3.Value = sqlstr
    
Close #1
End Sub

Open in new window

LVL 6
CaptainGibletsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
There is no Access VBA to T-SQL migration tool.   Also, looking at your code, I'm guessing this is going to be more than a single EE question, as the developer will have to be close to the data to make sure it is working right.

Good luck.
0
Jeffrey CoachmanMIS LiasonCommented:
Also you may want to clearly define "ASAP" in chronological terms.

As many experts may be apprehensive about starting this complex task here, only to be told that it needed to be done by 5PM EST today...
;-)

JeffCoachman
0
CaptainGibletsAuthor Commented:
there is no deadline but the sooner the better as work that this process is meant to relieve from users is piling up.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

CaptainGibletsAuthor Commented:
furthermore I will be using sql server data tools in SQL 2012 to run this as part of a process so I believe I can use coding other than T-SQL
0
Jeffrey CoachmanMIS LiasonCommented:
Then just be aware that may experts consider a question of this complexity, a "Project", ...not really a "question"

As Jim stated, this conversion must be "baby-sat" though each function.

You may wish to search here for examples of Stored procedures that read in text files...

JeffCoachman
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
To give you a start...

>Dim textline
Variables in T-SQL are prefixed with an @ sign, and Declare instead of Dim
Declare @textline as int

Open in new window

Also, the above line does not dim textline as a specific datatype, which makes it a Variant.  SQL Server requires a specific data type, and there are many differences between SQL Server versions.

>sqlfile = LaunchCD(Me)
>Open sqlfile For Input As #1
In T-SQL this would be an existing table and a BULKINSERT command to insert the contents of the text file into that table.

>Open sqlfile For Input As #1
>Do While Not EOF(1)
>  Line Input #1, textline
Looping through a table and editing values row-by-row would be a cursor, which experts try to avoid as much as possible as it is much more complex then writing code that affects entire sets, and it adds a lot of overhead.

Personally I would have an experienced SQL expert look at these requirements to tell if the cursor can be avoided.

>Do While Not x = 8
The T-SQL While loop is very similar to the Access VBA While.

>    If rowcount = 0 Then
The T-SQL IF block goes like this..

IF some expression that evaluates to true or false
    begin
    -- any number of statements goes here
   end
ELSE
   begin
   -- any number of statements goes here
   end

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.