How to enforce untyped columns in ExcelDataReader?
0
How do you prevent dates or times getting strictly-formatted?
I'm reading an XLS file using reader.AsDataSet, and when the source data is 12/12/2014 then this generates an output of 12/12/2014 12:00:00AM.
Also, when the source is for example 5:01:23 AM then this generates something weird: 12/31/1899 5:01:23 AM
Here's the function:
using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader = null;
if (excelFilePath.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (excelFilePath.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (reader == null)
return false;
var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
How do we read in the XLS file into a DataSet without automatically typing-concretely the data?
c# type-conversion dataset xls exceldatareader
edited Nov 27 '18 at 7:43
shA.t
12.9k43664
asked Nov 21 '18 at 17:07
l--''''''---------''''''''''''l--''''''---------''''''''''''
10.3k233533878
add a comment |
0
How do you prevent dates or times getting strictly-formatted?
I'm reading an XLS file using reader.AsDataSet, and when the source data is 12/12/2014 then this generates an output of 12/12/2014 12:00:00AM.
Also, when the source is for example 5:01:23 AM then this generates something weird: 12/31/1899 5:01:23 AM
Here's the function:
using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader = null;
if (excelFilePath.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (excelFilePath.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (reader == null)
return false;
var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
How do we read in the XLS file into a DataSet without automatically typing-concretely the data?
c# type-conversion dataset xls exceldatareader
edited Nov 27 '18 at 7:43
shA.t
12.9k43664
asked Nov 21 '18 at 17:07
l--''''''---------''''''''''''l--''''''---------''''''''''''
10.3k233533878
I don't think, its weird. Looks like you needtimespancolumn, instead you getDateTime. Datetime of course needs date part, so it adds it. Mat be,AsDataSetdoes not fit your case. You probably need to convert types in a loop. But I also see that date does seem coming from Excel, not converted. Otherwise it would be1/1/0001 5:01:23 AM
– T.S.
Nov 21 '18 at 17:21
@T.S. can you give an example suggestion of how to read this?
– l--''''''---------''''''''''''
Nov 21 '18 at 17:23
github.com/ExcelDataReader/ExcelDataReaderdo { while (reader.Read()) { // reader.GetDouble(0); } } while (reader.NextResult());
– T.S.
Nov 21 '18 at 17:25
@T.S. sorry not understandingreader.GetDouble(0)- none of my data is aDouble
– l--''''''---------''''''''''''
Nov 21 '18 at 17:37
Ok, but there must beReader.GetDateetc. This is just a sample. Don't take literally. Look at definition github.com/ExcelDataReader/ExcelDataReader/blob/develop/src/…. So in your loop you'll do something like (depends what you need)reader.GetDate(..).ToString("hh:mm:ss")- this will give you string representation of timespan. You can fill string column in data table
– T.S.
Nov 21 '18 at 19:05
add a comment |
0
0
0
How do you prevent dates or times getting strictly-formatted?
I'm reading an XLS file using reader.AsDataSet, and when the source data is 12/12/2014 then this generates an output of 12/12/2014 12:00:00AM.
Also, when the source is for example 5:01:23 AM then this generates something weird: 12/31/1899 5:01:23 AM
Here's the function:
using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader = null;
if (excelFilePath.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (excelFilePath.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (reader == null)
return false;
var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
How do we read in the XLS file into a DataSet without automatically typing-concretely the data?
c# type-conversion dataset xls exceldatareader
edited Nov 27 '18 at 7:43
shA.t
12.9k43664
asked Nov 21 '18 at 17:07
l--''''''---------''''''''''''l--''''''---------''''''''''''
10.3k233533878
How do you prevent dates or times getting strictly-formatted?
I'm reading an XLS file using reader.AsDataSet, and when the source data is 12/12/2014 then this generates an output of 12/12/2014 12:00:00AM.
Also, when the source is for example 5:01:23 AM then this generates something weird: 12/31/1899 5:01:23 AM
Here's the function:
using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader = null;
if (excelFilePath.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (excelFilePath.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (reader == null)
return false;
var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
How do we read in the XLS file into a DataSet without automatically typing-concretely the data?
c# type-conversion dataset xls exceldatareader
c# type-conversion dataset xls exceldatareader
edited Nov 27 '18 at 7:43
shA.t
12.9k43664
asked Nov 21 '18 at 17:07
l--''''''---------''''''''''''l--''''''---------''''''''''''
10.3k233533878
edited Nov 27 '18 at 7:43
shA.t
12.9k43664
asked Nov 21 '18 at 17:07
l--''''''---------''''''''''''l--''''''---------''''''''''''
10.3k233533878
edited Nov 27 '18 at 7:43
shA.t
12.9k43664
edited Nov 27 '18 at 7:43
shA.t
12.9k43664
edited Nov 27 '18 at 7:43
shA.t
12.9k43664
12.9k43664
asked Nov 21 '18 at 17:07
l--''''''---------''''''''''''l--''''''---------''''''''''''
10.3k233533878
asked Nov 21 '18 at 17:07
l--''''''---------''''''''''''l--''''''---------''''''''''''
10.3k233533878
asked Nov 21 '18 at 17:07
l--''''''---------''''''''''''l--''''''---------''''''''''''
10.3k233533878
10.3k233533878
I don't think, its weird. Looks like you needtimespancolumn, instead you getDateTime. Datetime of course needs date part, so it adds it. Mat be,AsDataSetdoes not fit your case. You probably need to convert types in a loop. But I also see that date does seem coming from Excel, not converted. Otherwise it would be1/1/0001 5:01:23 AM
– T.S.
Nov 21 '18 at 17:21
@T.S. can you give an example suggestion of how to read this?
– l--''''''---------''''''''''''
Nov 21 '18 at 17:23
github.com/ExcelDataReader/ExcelDataReaderdo { while (reader.Read()) { // reader.GetDouble(0); } } while (reader.NextResult());
– T.S.
Nov 21 '18 at 17:25
@T.S. sorry not understandingreader.GetDouble(0)- none of my data is aDouble
– l--''''''---------''''''''''''
Nov 21 '18 at 17:37
Ok, but there must beReader.GetDateetc. This is just a sample. Don't take literally. Look at definition github.com/ExcelDataReader/ExcelDataReader/blob/develop/src/…. So in your loop you'll do something like (depends what you need)reader.GetDate(..).ToString("hh:mm:ss")- this will give you string representation of timespan. You can fill string column in data table
– T.S.
Nov 21 '18 at 19:05
add a comment |
I don't think, its weird. Looks like you needtimespancolumn, instead you getDateTime. Datetime of course needs date part, so it adds it. Mat be,AsDataSetdoes not fit your case. You probably need to convert types in a loop. But I also see that date does seem coming from Excel, not converted. Otherwise it would be1/1/0001 5:01:23 AM
– T.S.
Nov 21 '18 at 17:21
@T.S. can you give an example suggestion of how to read this?
– l--''''''---------''''''''''''
Nov 21 '18 at 17:23
github.com/ExcelDataReader/ExcelDataReaderdo { while (reader.Read()) { // reader.GetDouble(0); } } while (reader.NextResult());
– T.S.
Nov 21 '18 at 17:25
@T.S. sorry not understandingreader.GetDouble(0)- none of my data is aDouble
– l--''''''---------''''''''''''
Nov 21 '18 at 17:37
Ok, but there must beReader.GetDateetc. This is just a sample. Don't take literally. Look at definition github.com/ExcelDataReader/ExcelDataReader/blob/develop/src/…. So in your loop you'll do something like (depends what you need)reader.GetDate(..).ToString("hh:mm:ss")- this will give you string representation of timespan. You can fill string column in data table
– T.S.
Nov 21 '18 at 19:05
I don't think, its weird. Looks like you need
timespan column, instead you get DateTime. Datetime of course needs date part, so it adds it. Mat be, AsDataSet does not fit your case. You probably need to convert types in a loop. But I also see that date does seem coming from Excel, not converted. Otherwise it would be 1/1/0001 5:01:23 AM– T.S.
Nov 21 '18 at 17:21
I don't think, its weird. Looks like you need
timespan column, instead you get DateTime. Datetime of course needs date part, so it adds it. Mat be, AsDataSet does not fit your case. You probably need to convert types in a loop. But I also see that date does seem coming from Excel, not converted. Otherwise it would be 1/1/0001 5:01:23 AM– T.S.
Nov 21 '18 at 17:21
@T.S. can you give an example suggestion of how to read this?
– l--''''''---------''''''''''''
Nov 21 '18 at 17:23
@T.S. can you give an example suggestion of how to read this?
– l--''''''---------''''''''''''
Nov 21 '18 at 17:23
github.com/ExcelDataReader/ExcelDataReader
do { while (reader.Read()) { // reader.GetDouble(0); } } while (reader.NextResult());– T.S.
Nov 21 '18 at 17:25
github.com/ExcelDataReader/ExcelDataReader
do { while (reader.Read()) { // reader.GetDouble(0); } } while (reader.NextResult());– T.S.
Nov 21 '18 at 17:25
@T.S. sorry not understanding
reader.GetDouble(0) - none of my data is a Double– l--''''''---------''''''''''''
Nov 21 '18 at 17:37
@T.S. sorry not understanding
reader.GetDouble(0) - none of my data is a Double– l--''''''---------''''''''''''
Nov 21 '18 at 17:37
Ok, but there must be
Reader.GetDate etc. This is just a sample. Don't take literally. Look at definition github.com/ExcelDataReader/ExcelDataReader/blob/develop/src/…. So in your loop you'll do something like (depends what you need) reader.GetDate(..).ToString("hh:mm:ss") - this will give you string representation of timespan. You can fill string column in data table– T.S.
Nov 21 '18 at 19:05
Ok, but there must be
Reader.GetDate etc. This is just a sample. Don't take literally. Look at definition github.com/ExcelDataReader/ExcelDataReader/blob/develop/src/…. So in your loop you'll do something like (depends what you need) reader.GetDate(..).ToString("hh:mm:ss") - this will give you string representation of timespan. You can fill string column in data table– T.S.
Nov 21 '18 at 19:05
add a comment |
1 Answer
1
active
oldest
votes
1
I think in this situation you have all data you need in your result (as DataSet)!
So, I can suggest you to ignore that option in reading time and instead add any extra column to your result like below:
// `Copy` used to generate a new object
var dataTable = ds.Tables[0].Copy();
// create a new column with your own settings (like `TimeOnly`) and add it to your new DataTable
var newColumn = new DataColumn("TimeOnly", typeof(string)) { AllowDBNull = true };
dataTable.Columns.Add(newColumn);
// update your new column data based on other columns like `Column1`
foreach (DataRow row in dataTable.Rows)
{
var value = DateTime.Parse(row["Column1"].ToString()).ToString("HH:mm:ss");
row["TimeOnly"] = value;
}
HTH
answered Nov 27 '18 at 7:41
shA.tshA.t
12.9k43664
add a comment |
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',
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417238%2fhow-to-enforce-untyped-columns-in-exceldatareader%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
I think in this situation you have all data you need in your result (as DataSet)!
So, I can suggest you to ignore that option in reading time and instead add any extra column to your result like below:
// `Copy` used to generate a new object
var dataTable = ds.Tables[0].Copy();
// create a new column with your own settings (like `TimeOnly`) and add it to your new DataTable
var newColumn = new DataColumn("TimeOnly", typeof(string)) { AllowDBNull = true };
dataTable.Columns.Add(newColumn);
// update your new column data based on other columns like `Column1`
foreach (DataRow row in dataTable.Rows)
{
var value = DateTime.Parse(row["Column1"].ToString()).ToString("HH:mm:ss");
row["TimeOnly"] = value;
}
HTH
answered Nov 27 '18 at 7:41
shA.tshA.t
12.9k43664
add a comment |
1
I think in this situation you have all data you need in your result (as DataSet)!
So, I can suggest you to ignore that option in reading time and instead add any extra column to your result like below:
// `Copy` used to generate a new object
var dataTable = ds.Tables[0].Copy();
// create a new column with your own settings (like `TimeOnly`) and add it to your new DataTable
var newColumn = new DataColumn("TimeOnly", typeof(string)) { AllowDBNull = true };
dataTable.Columns.Add(newColumn);
// update your new column data based on other columns like `Column1`
foreach (DataRow row in dataTable.Rows)
{
var value = DateTime.Parse(row["Column1"].ToString()).ToString("HH:mm:ss");
row["TimeOnly"] = value;
}
HTH
answered Nov 27 '18 at 7:41
shA.tshA.t
12.9k43664
add a comment |
1
1
1
I think in this situation you have all data you need in your result (as DataSet)!
So, I can suggest you to ignore that option in reading time and instead add any extra column to your result like below:
// `Copy` used to generate a new object
var dataTable = ds.Tables[0].Copy();
// create a new column with your own settings (like `TimeOnly`) and add it to your new DataTable
var newColumn = new DataColumn("TimeOnly", typeof(string)) { AllowDBNull = true };
dataTable.Columns.Add(newColumn);
// update your new column data based on other columns like `Column1`
foreach (DataRow row in dataTable.Rows)
{
var value = DateTime.Parse(row["Column1"].ToString()).ToString("HH:mm:ss");
row["TimeOnly"] = value;
}
HTH
answered Nov 27 '18 at 7:41
shA.tshA.t
12.9k43664
I think in this situation you have all data you need in your result (as DataSet)!
So, I can suggest you to ignore that option in reading time and instead add any extra column to your result like below:
// `Copy` used to generate a new object
var dataTable = ds.Tables[0].Copy();
// create a new column with your own settings (like `TimeOnly`) and add it to your new DataTable
var newColumn = new DataColumn("TimeOnly", typeof(string)) { AllowDBNull = true };
dataTable.Columns.Add(newColumn);
// update your new column data based on other columns like `Column1`
foreach (DataRow row in dataTable.Rows)
{
var value = DateTime.Parse(row["Column1"].ToString()).ToString("HH:mm:ss");
row["TimeOnly"] = value;
}
HTH
answered Nov 27 '18 at 7:41
shA.tshA.t
12.9k43664
answered Nov 27 '18 at 7:41
shA.tshA.t
12.9k43664
answered Nov 27 '18 at 7:41
shA.tshA.t
12.9k43664
answered Nov 27 '18 at 7:41
shA.tshA.t
12.9k43664
12.9k43664
add a comment |
add a comment |
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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53417238%2fhow-to-enforce-untyped-columns-in-exceldatareader%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I don't think, its weird. Looks like you need
timespancolumn, instead you getDateTime. Datetime of course needs date part, so it adds it. Mat be,AsDataSetdoes not fit your case. You probably need to convert types in a loop. But I also see that date does seem coming from Excel, not converted. Otherwise it would be1/1/0001 5:01:23 AM– T.S.
Nov 21 '18 at 17:21
@T.S. can you give an example suggestion of how to read this?
– l--''''''---------''''''''''''
Nov 21 '18 at 17:23
github.com/ExcelDataReader/ExcelDataReader
do { while (reader.Read()) { // reader.GetDouble(0); } } while (reader.NextResult());– T.S.
Nov 21 '18 at 17:25
@T.S. sorry not understanding
reader.GetDouble(0)- none of my data is aDouble– l--''''''---------''''''''''''
Nov 21 '18 at 17:37
Ok, but there must be
Reader.GetDateetc. This is just a sample. Don't take literally. Look at definition github.com/ExcelDataReader/ExcelDataReader/blob/develop/src/…. So in your loop you'll do something like (depends what you need)reader.GetDate(..).ToString("hh:mm:ss")- this will give you string representation of timespan. You can fill string column in data table– T.S.
Nov 21 '18 at 19:05