Date format export/import

Help!

I have a table that has datetime format field, I exported the table to
a csv while I dropped it and tried some other data, but now sql
doesn't recognise the date format for importing, heck I don't!

The dates look something like:

40:58.1

Whick means nothing to me, or any of us here for that matter...

Any ideas?

JohnHi

Without knowing how you exported the file it is hard to say how you came
to have the dates you have. It seems that you have something like
minutes, seconds and the first digit of the milliseconds.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Date field in exported excel file

Date field in exported excel file.

I export a table to excel file by using DTS. It seems the date field show as ###### when I open the excel file. If I expend the column I see the date. Is there any way I export in away that this date field will not show up as #####.

Very Good Q just like if there is Nvarchar Data type with length 4000, why not it auto expend when i see it with select query in SQL? Continue....|||

Hi,

I didn't find any issue here. There is nothing wrong with your data; the cell simply isn’t big enough to display the result. Widen the column

SQL Server's task is to export data into excel & it does properly. You are not loosing any data here. When it export the data it doesn't format any data, it fills/writes all the data on Rows & Columns. It uses the default column width to fill the data(64 Pixels). You can expand your columns to read your data properly (there is no data loose)

SQL Server only export data & it never format your data (don't expect that it will bold your Header column, auto size your column width & etc.)

WHY IT IS NOT FORMATING?

Bcs SQL Server export taks uses the JET Provider to write the data. (here excel docuemnt will be treated as database rather than doc).It is not using EXCEL ActiveX EXE to fill the data.

The export code may use the following connection string to export,

Provider=Microsoft.Jet.OLEDB.4.0;Data Source={File Path};Extended Properties="Excel 8.0;"

Date field in exported excel file

I export a table to excel file by using DTS. It seems the date field show as
###### when I open the excel file. If I expend the column I see the date. Is
there any way I export in away that this date field will not show up as ####
#.This just indicates the column isn't wide enough to display the date. You
can do it via a script.
Option Explicit
Dim filePath, oExcel, oSheet
filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Columns("A:A").ColumnWidth = 20
osheet.Range("A1").Select
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
>I export a table to excel file by using DTS. It seems the date field show
>as
> ###### when I open the excel file. If I expend the column I see the date.
> Is
> there any way I export in away that this date field will not show up as
> #####.
>|||Another option is to change the default column width for the excel
application Cells|Format|Cell Size|Default Width (FPN:this is user dependent
setting)
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect|||Where can I change the default Column With Dave?
"Dave Patrick" wrote:

> Another option is to change the default column width for the excel
> application Cells|Format|Cell Size|Default Width (FPN:this is user depende
nt
> setting)
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
>|||Excel 2007
Cells|Format|Cell Size|Default Width
Excel 2003
Format|Column|Standard Width
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Where can I change the default Column With Dave?|||Ok. It works but this is specific to a file, it is not setting to Excel
application. I am sending the excel file I exported by using DTS to external
clients. So I need to set this during export. I just simply run DTS and it
exports to excel as many files and I am not sure if I can set this default
with during export, is this possible?
"Dave Patrick" wrote:
[vbcol=seagreen]
> Excel 2007
> Cells|Format|Cell Size|Default Width
> Excel 2003
> Format|Column|Standard Width
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "JIM.H." wrote:|||I think you have to change this setting in an Excel file and save this file
as book.xlt in your xlstart folder (using this as default template) These
two articles may also help.
http://office.microsoft.com/en-us/e...0548151033.aspx
http://support.microsoft.com/kb/214123
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Ok. It works but this is specific to a file, it is not setting to Excel
> application. I am sending the excel file I exported by using DTS to
> external
> clients. So I need to set this during export. I just simply run DTS and it
> exports to excel as many files and I am not sure if I can set this default
> with during export, is this possible?

Date field in exported excel file

I export a table to excel file by using DTS. It seems the date field show as ###### when I open the excel file. If I expend the column I see the date. Is there any way I export in away that this date field will not show up as #####.

I may be wrong, but I would think that this is a function of Excel itself -|||I agree with David, it should be a display issue of Excel. You can expand all folded columns: go to Format Menu in Excel->Column->click AutoFit Selection.

Date field in exported excel file

I export a table to excel file by using DTS. It seems the date field show as
###### when I open the excel file. If I expend the column I see the date. Is
there any way I export in away that this date field will not show up as #####.
This just indicates the column isn't wide enough to display the date. You
can do it via a script.
Option Explicit
Dim filePath, oExcel, oSheet
filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Columns("A:A").ColumnWidth = 20
osheet.Range("A1").Select
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
>I export a table to excel file by using DTS. It seems the date field show
>as
> ###### when I open the excel file. If I expend the column I see the date.
> Is
> there any way I export in away that this date field will not show up as
> #####.
>
|||Another option is to change the default column width for the excel
application Cells|Format|Cell Size|Default Width (FPN:this is user dependent
setting)
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
|||Where can I change the default Column With Dave?
"Dave Patrick" wrote:

> Another option is to change the default column width for the excel
> application Cells|Format|Cell Size|Default Width (FPN:this is user dependent
> setting)
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
>
|||Excel 2007
Cells|Format|Cell Size|Default Width
Excel 2003
Format|Column|Standard Width
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Where can I change the default Column With Dave?
|||Ok. It works but this is specific to a file, it is not setting to Excel
application. I am sending the excel file I exported by using DTS to external
clients. So I need to set this during export. I just simply run DTS and it
exports to excel as many files and I am not sure if I can set this default
with during export, is this possible?
"Dave Patrick" wrote:
[vbcol=seagreen]
> Excel 2007
> Cells|Format|Cell Size|Default Width
> Excel 2003
> Format|Column|Standard Width
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "JIM.H." wrote:
|||I think you have to change this setting in an Excel file and save this file
as book.xlt in your xlstart folder (using this as default template) These
two articles may also help.
http://office.microsoft.com/en-us/excel/HA010548151033.aspx
http://support.microsoft.com/kb/214123
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Ok. It works but this is specific to a file, it is not setting to Excel
> application. I am sending the excel file I exported by using DTS to
> external
> clients. So I need to set this during export. I just simply run DTS and it
> exports to excel as many files and I am not sure if I can set this default
> with during export, is this possible?

Date field in exported excel file

I export a table to excel file by using DTS. It seems the date field show as
###### when I open the excel file. If I expend the column I see the date. Is
there any way I export in away that this date field will not show up as #####.This just indicates the column isn't wide enough to display the date. You
can do it via a script.
Option Explicit
Dim filePath, oExcel, oSheet
filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oSheet.Columns("A:A").ColumnWidth = 20
osheet.Range("A1").Select
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
>I export a table to excel file by using DTS. It seems the date field show
>as
> ###### when I open the excel file. If I expend the column I see the date.
> Is
> there any way I export in away that this date field will not show up as
> #####.
>|||Another option is to change the default column width for the excel
application Cells|Format|Cell Size|Default Width (FPN:this is user dependent
setting)
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect|||Excel 2007
Cells|Format|Cell Size|Default Width
Excel 2003
Format|Column|Standard Width
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Where can I change the default Column With Dave?|||Ok. It works but this is specific to a file, it is not setting to Excel
application. I am sending the excel file I exported by using DTS to external
clients. So I need to set this during export. I just simply run DTS and it
exports to excel as many files and I am not sure if I can set this default
with during export, is this possible?
"Dave Patrick" wrote:
> Excel 2007
> Cells|Format|Cell Size|Default Width
> Excel 2003
> Format|Column|Standard Width
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "JIM.H." wrote:
> > Where can I change the default Column With Dave?|||I think you have to change this setting in an Excel file and save this file
as book.xlt in your xlstart folder (using this as default template) These
two articles may also help.
http://office.microsoft.com/en-us/excel/HA010548151033.aspx
http://support.microsoft.com/kb/214123
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"JIM.H." wrote:
> Ok. It works but this is specific to a file, it is not setting to Excel
> application. I am sending the excel file I exported by using DTS to
> external
> clients. So I need to set this during export. I just simply run DTS and it
> exports to excel as many files and I am not sure if I can set this default
> with during export, is this possible?

Date columns exported as dates when exported into Excel

Is it possible to have a DateTime field exported into excel as a datetime
fields so that the user can sort in Excel on this datefield?are you right clicking the field in RS, then going to properties and then
setting the format to 'Date'?
Not sure how excel interperates this though.
"MicroMoth" wrote:
> Is it possible to have a DateTime field exported into excel as a datetime
> fields so that the user can sort in Excel on this datefield?