Tjyylli

What are the advantages and disadvantages of running one shots compared to campaigns?

Patience, young "Padovan"

I’m planning on buying a laser printer but concerned about the life cycle of toner in the machine

Are cabin dividers used to "hide" the flex of the airplane?

How to create a consistent feel for character names in a fantasy setting?

Symmetry in quantum mechanics

Can a planet have a different gravitational pull depending on its location in orbit around its sun?

Manga about a female worker who got dragged into another world together with this high school girl and she was just told she's not needed anymore

aging parents with no investments

Is this food a bread or a loaf?

How to move the player while also allowing forces to affect it

Synthetic Control Method

Is there a familial term for apples and pears?

Why is making salt water prohibited on Shabbat?

Information to fellow intern about hiring?

COUNT(id) or MAX(id) - which is faster?

Why is the design of haulage companies so “special”?

Why was the "bread communication" in the arena of Catching Fire left out in the movie?

Check if two datetimes are between two others

Mapping arrows in commutative diagrams

What is the command to reset a PC without deleting any files

Prime joint compound before latex paint?

Some basic questions on halt and move in Turing machines

I see my dog run

Excel, importing incomplete data to a complete table?

Excel Help: Data Input HelpCan you pull Excel data from an external (and dynamic) .csv file?how to plot data from csv file?advice on generating reports with Excel vs Microsoft AccessAdd an elapsed time format to a datetime in ExcelHow can I easily split a CSV into two Excel worksheets with PowerShell?Is there a way to group an excel table similar to a SharePoint list?Excel - Find a specific column in a CSV file, and copy to the current sheetHow to automate data scraping from multiple pages of site where the URL doesn't changeApply formula to all rows in a column from data table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

0

I'm not sure if I'm asking this correctly, but here's my problem.

I'm managing the occupancy of a building, and the website we use lets us generate a report of the occupants by room. This information comes out in a CSV format. The data is sorted by room number, 101A, 101B, 102A, 103B, and so on... However, if a room is vacant it just omits the room entirely and that row of data doesn't exist in the CSV.

Ultimately I need to find a way to import this data from the CSV to populate a roster I'm maintaining in excel. I'm thinking the easiest way would be to somehow have excel fill in the vacant room information itself based on what's missing (maybe by comparing it to a pre-existing table)? I can't figure out how to do it though.

Data from CSV

What I want the final table to look like after import to Excel

microsoft-excel csv import

share|improve this question

edited 2 days ago

teylyn

17.5k22539

asked 2 days ago

AustinAustin

1

New contributor

Austin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  One simple method is,, after you get data been imported, put formula in Occupant Column to check whether Check in Date Cell is blank or not, if is blank, then set VACANT to display as TRUE option. And set BLANK for Gender Column ☺
  
  – Rajesh S
  2 days ago
  
  
  

  
  
  
  

add a comment | 

0

I'm not sure if I'm asking this correctly, but here's my problem.

I'm managing the occupancy of a building, and the website we use lets us generate a report of the occupants by room. This information comes out in a CSV format. The data is sorted by room number, 101A, 101B, 102A, 103B, and so on... However, if a room is vacant it just omits the room entirely and that row of data doesn't exist in the CSV.

Ultimately I need to find a way to import this data from the CSV to populate a roster I'm maintaining in excel. I'm thinking the easiest way would be to somehow have excel fill in the vacant room information itself based on what's missing (maybe by comparing it to a pre-existing table)? I can't figure out how to do it though.

Data from CSV

What I want the final table to look like after import to Excel

microsoft-excel csv import

share|improve this question

edited 2 days ago

teylyn

17.5k22539

asked 2 days ago

AustinAustin

1

New contributor

Austin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  One simple method is,, after you get data been imported, put formula in Occupant Column to check whether Check in Date Cell is blank or not, if is blank, then set VACANT to display as TRUE option. And set BLANK for Gender Column ☺
  
  – Rajesh S
  2 days ago
  
  
  

  
  
  
  

add a comment | 

I'm not sure if I'm asking this correctly, but here's my problem.

I'm managing the occupancy of a building, and the website we use lets us generate a report of the occupants by room. This information comes out in a CSV format. The data is sorted by room number, 101A, 101B, 102A, 103B, and so on... However, if a room is vacant it just omits the room entirely and that row of data doesn't exist in the CSV.

Ultimately I need to find a way to import this data from the CSV to populate a roster I'm maintaining in excel. I'm thinking the easiest way would be to somehow have excel fill in the vacant room information itself based on what's missing (maybe by comparing it to a pre-existing table)? I can't figure out how to do it though.

Data from CSV

What I want the final table to look like after import to Excel

microsoft-excel csv import

share|improve this question

edited 2 days ago

teylyn

17.5k22539

asked 2 days ago

AustinAustin

1

New contributor

Austin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 2 days ago

teylyn

17.5k22539

asked 2 days ago

AustinAustin

1

New contributor

Austin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 2 days ago

teylyn

17.5k22539

asked 2 days ago

AustinAustin

1

New contributor

Austin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 2 days ago

teylyn

17.5k22539

edited 2 days ago

teylyn

17.5k22539

asked 2 days ago

AustinAustin

1

New contributor

Austin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 days ago

AustinAustin

1

1 Answer
1

active

oldest

votes

-1

The easiest way to do this is using Power Query. This is a free Microsoft Add-in for Excel 2010 and 2013 and included in 2016 and later as Get and Transform in the Data ribbon.

Prepare a complete list of rooms and keep it in a spreadsheet. then

• load the CSV data into Power Query and save it as a connection only (without loading it to the grid)


• load the room list into Power Query.


• From the room list query as the active query, merge the CSV query with the room list query, matching on the room number and using the setting "All from first, matching from second"


• expand the merged table

Play around with this concept. You can save the workbook and when you have a new CSV, save it with the same file name as the first one, then refresh the query.

One click. Magic.

share|improve this answer

answered 2 days ago

teylynteylyn

17.5k22539

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ... and would the downvoter please explain what s/he does not like?
  
  – teylyn
  yesterday
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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',
autoActivateHeartbeat: false,
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
});


}
});

Austin is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

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

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1422083%2fexcel-importing-incomplete-data-to-a-complete-table%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

-1

The easiest way to do this is using Power Query. This is a free Microsoft Add-in for Excel 2010 and 2013 and included in 2016 and later as Get and Transform in the Data ribbon.

Prepare a complete list of rooms and keep it in a spreadsheet. then

• load the CSV data into Power Query and save it as a connection only (without loading it to the grid)


• load the room list into Power Query.


• From the room list query as the active query, merge the CSV query with the room list query, matching on the room number and using the setting "All from first, matching from second"


• expand the merged table

Play around with this concept. You can save the workbook and when you have a new CSV, save it with the same file name as the first one, then refresh the query.

One click. Magic.

share|improve this answer

answered 2 days ago

teylynteylyn

17.5k22539

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ... and would the downvoter please explain what s/he does not like?
  
  – teylyn
  yesterday
  

  
  
  
  

add a comment | 

-1

The easiest way to do this is using Power Query. This is a free Microsoft Add-in for Excel 2010 and 2013 and included in 2016 and later as Get and Transform in the Data ribbon.

Prepare a complete list of rooms and keep it in a spreadsheet. then

• load the CSV data into Power Query and save it as a connection only (without loading it to the grid)


• load the room list into Power Query.


• From the room list query as the active query, merge the CSV query with the room list query, matching on the room number and using the setting "All from first, matching from second"


• expand the merged table

Play around with this concept. You can save the workbook and when you have a new CSV, save it with the same file name as the first one, then refresh the query.

One click. Magic.

share|improve this answer

answered 2 days ago

teylynteylyn

17.5k22539

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ... and would the downvoter please explain what s/he does not like?
  
  – teylyn
  yesterday
  

  
  
  
  

add a comment | 

The easiest way to do this is using Power Query. This is a free Microsoft Add-in for Excel 2010 and 2013 and included in 2016 and later as Get and Transform in the Data ribbon.

Prepare a complete list of rooms and keep it in a spreadsheet. then

• load the CSV data into Power Query and save it as a connection only (without loading it to the grid)


• load the room list into Power Query.


• From the room list query as the active query, merge the CSV query with the room list query, matching on the room number and using the setting "All from first, matching from second"


• expand the merged table

Play around with this concept. You can save the workbook and when you have a new CSV, save it with the same file name as the first one, then refresh the query.

One click. Magic.

share|improve this answer

answered 2 days ago

teylynteylyn

17.5k22539

share|improve this answer

answered 2 days ago

teylynteylyn

17.5k22539

answered 2 days ago

teylynteylyn

17.5k22539

answered 2 days ago

teylynteylyn

17.5k22539