CSV Import into SQL Server table(s)

up vote
0
down vote

favorite

I have a spreadsheet which is laid out

ITEM NAME    TAG1    TAG2     TAG3

Hats           X       X

Socks          X                X

Pants                  X        X

and I have 3 DB tables

Items - PK_ItemId

Tags - PK_TagId

ItemTags - PK_ItemId_TagId

How can I import the data from the spreadsheet into the correct mapping in ItemTags where the column has an X?

edited Nov 12 at 5:07

marc_s

568k12810991249

asked Nov 12 at 0:26

jonahpup

5010

How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 at 0:33

All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 at 0:37

I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 at 0:39

1

That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 at 0:51

Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 at 0:58

|
show 1 more comment

up vote
0
down vote

favorite

I have a spreadsheet which is laid out

ITEM NAME    TAG1    TAG2     TAG3

Hats           X       X

Socks          X                X

Pants                  X        X

and I have 3 DB tables

Items - PK_ItemId

Tags - PK_TagId

ItemTags - PK_ItemId_TagId

How can I import the data from the spreadsheet into the correct mapping in ItemTags where the column has an X?

edited Nov 12 at 5:07

marc_s

568k12810991249

asked Nov 12 at 0:26

jonahpup

5010

How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 at 0:33

All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 at 0:37

I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 at 0:39

1

That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 at 0:51

Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 at 0:58

|
show 1 more comment

up vote
0
down vote

favorite

I have a spreadsheet which is laid out

ITEM NAME    TAG1    TAG2     TAG3

Hats           X       X

Socks          X                X

Pants                  X        X

and I have 3 DB tables

Items - PK_ItemId

Tags - PK_TagId

ItemTags - PK_ItemId_TagId

How can I import the data from the spreadsheet into the correct mapping in ItemTags where the column has an X?

edited Nov 12 at 5:07

marc_s

568k12810991249

asked Nov 12 at 0:26

jonahpup

5010

I have a spreadsheet which is laid out

ITEM NAME    TAG1    TAG2     TAG3

Hats           X       X

Socks          X                X

Pants                  X        X

and I have 3 DB tables

Items - PK_ItemId

Tags - PK_TagId

ItemTags - PK_ItemId_TagId

How can I import the data from the spreadsheet into the correct mapping in ItemTags where the column has an X?

sql-server excel

edited Nov 12 at 5:07

marc_s

568k12810991249

asked Nov 12 at 0:26

jonahpup

5010

edited Nov 12 at 5:07

marc_s

568k12810991249

asked Nov 12 at 0:26

jonahpup

5010

edited Nov 12 at 5:07

marc_s

568k12810991249

edited Nov 12 at 5:07

marc_s

568k12810991249

edited Nov 12 at 5:07

marc_s

568k12810991249

asked Nov 12 at 0:26

jonahpup

5010

asked Nov 12 at 0:26

jonahpup

5010

asked Nov 12 at 0:26

jonahpup

5010

How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 at 0:33

All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 at 0:37

I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 at 0:39

1

That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 at 0:51

Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 at 0:58

|
show 1 more comment

How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 at 0:33

All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 at 0:37

I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 at 0:39

1

That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 at 0:51

Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 at 0:58

How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 at 0:33

All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 at 0:37

I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 at 0:39

That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 at 0:51

Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 at 0:58

|
show 1 more comment

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53254603%2fcsv-import-into-sql-server-tables%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky