insert excel file into sql (selected data)

Question

I have an excel file (currently look like attached image) wanted to import it into my db.

currently my query look like this

INSERT INTO [inSharePoint] (processingDate, [url], [sharepointName], [ownerEmail], [secondaryOwnerEmail], [lastUpdateDate], [totalQuotaInMB], [totalStorageInMB], [storageUsedInPercentage], [lockStatus], [siteType], [spVersion])
    VALUES ('{processingDate}', @url, @sharepointName, @ownerEmail, @secondaryOwnerEmail, @lastUpdateDate, @totalQuotaInMB, @totalStorageInMB, @storageUsedInPercentage, @lockStatus, @siteType, @spVersion)

but what if i wanted to insert the same file but the only difference is for my column [LockStatus], I ONLY want with the status nolock to be insert and not others status (No readonly and noaccess)

i am really new to sql hope somebody could explain it thank you

You want all the columns to say 'nolock' or you only want to insert the rows which have a LockStatus of 'nolock'? — dbajtr
– dbajtr, Commented May 14, 2018 at 14:37
No, i don't want to convert it. I want to insert row LockStatus with only "nolock" status @dbajtr — 1aliff
– 1aliff, Commented May 14, 2018 at 14:39
I map the file using mongoDB. and driver call xlsxWriter @squillman — 1aliff
– 1aliff, Commented May 14, 2018 at 14:40

squillman · Accepted Answer · 2018-05-14 14:50:11Z

1

As @Daniel Marcus mentioned, you could use SSIS (SQL Server Integration Services) to do this pretty easily if you have it available.

In your current solution it looks like you're processing the Excel data row by row in which case you'd need to check the data in your NodeJS code using an IF statement and only do the insert if LockStatus = nolock.

Or, you could use a mongo query to pull only the data where LockStatus = nolock and send that data to SQL Server. I'm not familiar with querying mongo, though, so not sure what that would look like.

answered May 14, 2018 at 14:50

squillman

13.7k3 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

1aliff Over a year ago

OK i understood, currently looking for SSIS. Thank you very much for the hint! I will try it @squillman

squillman Over a year ago

Sounds good! If you're using SQL Server Express (or LocalDB) you won't have access to SSIS. You'll need at least SQL Server Standard

ASH · Accepted Answer · 2018-05-16 21:54:00Z

You can use VBA to do this!

Sub InsertARecord()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String, stSQL As String
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

stCon = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=JOEY"
cnt.ConnectionString = stCon
stSQL = "INSERT INTO MyTable (FieldNames)"
stSQL = stSQL & "VALUES (ActualValues)"
stSQL = stSQL & "WHERE lockStatus = 'nolock'"

cnt.Open
rst.Open stSQL, cnt, adOpenStatic, adLockReadOnly, adCmdText

If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) = True Then cnt.Close
Set cnt = Nothing

End Sub

Also, see this URL.

https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Introduction

Collectives™ on Stack Overflow

insert excel file into sql (selected data)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related