DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
always have a title row and all the data will be on the primary (first) worksheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
even possible?), create a destination table, and squirt the data into the table.
I'd be very grateful for any help anyone can provide! :-)
John Peterson
John,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=29...8-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1' ' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheone')
set @.sql = replace(@.sql,'##table##','thisisthetablenametogive it)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
>always have a title row and all the data will be on the primary (first) worksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-one with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
>even possible?), create a destination table, and squirt the data into the table.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>
|||Steve Kass <skass@.drew.edu> wrote in message ...
[vbcol=seagreen]

> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.
|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server? I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need to worry
about -- so I'm hoping that determining which is the "first" sheet won't be a problem.
Though, if I understand your concern correctly -- it seems like I need to know the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.c om...
> Steve Kass <skass@.drew.edu> wrote in message ...
>
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --
|||"John Peterson" wrote ...

> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.
|||I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
work for me, but there are issues with OPENROWSET that are kind of stymieing me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
the columns that are "in use" and create Transformations for them, maybe I can do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.c om...
> "John Peterson" wrote ...
>
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --
|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:

>I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
>work for me, but there are issues with OPENROWSET that are kind of stymieing me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
>the columns that are "in use" and create Transformations for them, maybe I can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google. com...
>
>
>

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
always have a title row and all the data will be on the primary (first) worksheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
even possible?), create a destination table, and squirt the data into the table.
I'd be very grateful for any help anyone can provide! :-)
John PetersonJohn,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=29C76785-22D9-46A2-A398-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1'' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheone')
set @.sql = replace(@.sql,'##table##','thisisthetablenametogiveit)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
>always have a title row and all the data will be on the primary (first) worksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-one with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
>even possible?), create a destination table, and squirt the data into the table.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>|||Steve Kass <skass@.drew.edu> wrote in message ...
> > I have a series of Excel spreadsheets, of which
> > I don't know the columns beforehand, that
> > I'd like to import into SQL Server. These spreadsheets
> > are pretty simple in that they'll
> > always have a title row and all the data will be
> > on the primary (first) worksheet.
> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.
--|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server? I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need to worry
about -- so I'm hoping that determining which is the "first" sheet won't be a problem.
Though, if I understand your concern correctly -- it seems like I need to know the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.com...
> Steve Kass <skass@.drew.edu> wrote in message ...
> > > I have a series of Excel spreadsheets, of which
> > > I don't know the columns beforehand, that
> > > I'd like to import into SQL Server. These spreadsheets
> > > are pretty simple in that they'll
> > > always have a title row and all the data will be
> > > on the primary (first) worksheet.
> > You can do this with an ad hoc query:
> >
> > select * into #abc
> > from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> > 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --|||"John Peterson" wrote ...
> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.
--|||I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
work for me, but there are issues with OPENROWSET that are kind of stymieing me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
the columns that are "in use" and create Transformations for them, maybe I can do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
> "John Peterson" wrote ...
> > Jamie, in my case, I think that there will
> > only be one Worksheet that I need to worry
> > about -- so I'm hoping that determining which
> > is the "first" sheet won't be a problem.
> > Though, if I understand your concern correctly
> > -- it seems like I need to know the *name*
> > of that worksheet beforehand in the OPENQUERY?
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:
>I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
>work for me, but there are issues with OPENROWSET that are kind of stymieing me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
>the columns that are "in use" and create Transformations for them, maybe I can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
>
>>"John Peterson" wrote ...
>>
>>Jamie, in my case, I think that there will
>>only be one Worksheet that I need to worry
>>about -- so I'm hoping that determining which
>>is the "first" sheet won't be a problem.
>>Though, if I understand your concern correctly
>>-- it seems like I need to know the *name*
>>of that worksheet beforehand in the OPENQUERY?
>>
>>Correct, unless you are using a defined Name ('named range') in the
>>Excel workbook in which case you'd need to know the Name's name.
>>Jamie.
>>--
>>
>
>

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns bef
orehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in
that they'll
always have a title row and all the data will be on the primary (first) work
sheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one
with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the T
ransform Data
Task on a specific spreadsheet basis. However, I'd like to make the package
dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet colum
ns (is this
even possible?), create a destination table, and squirt the data into the ta
ble.
I'd be very grateful for any help anyone can provide! :-)
John PetersonJohn,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=2...98-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1'' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheon
e')
set @.sql = replace(@.sql,'##table##','thisisthetable
nametogiveit)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns be
forehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple i
n that they'll
>always have a title row and all the data will be on the primary (first) wor
ksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-on
e with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the
Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the packag
e dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet colu
mns (is this
>even possible?), create a destination table, and squirt the data into the t
able.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>|||Steve Kass <skass@.drew.edu> wrote in message ...

[vbcol=seagreen]
> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server?
I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need
to worry
about -- so I'm hoping that determining which is the "first" sheet won't be
a problem.
Though, if I understand your concern correctly -- it seems like I need to kn
ow the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.com...
> Steve Kass <skass@.drew.edu> wrote in message ...
>
>
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --|||"John Peterson" wrote ...

> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.|||I'm still at kind of an impasse with this. I had thought that Steve's sugge
stion might
work for me, but there are issues with OPENROWSET that are kind of stymieing
me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to i
dentify all
the columns that are "in use" and create Transformations for them, maybe I c
an do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
> "John Peterson" wrote ...
>
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:

>I'm still at kind of an impasse with this. I had thought that Steve's sugg
estion might
>work for me, but there are issues with OPENROWSET that are kind of stymiein
g me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to
identify all
>the columns that are "in use" and create Transformations for them, maybe I
can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
>
>
>

DTS: excel to sql server

I posted this earlier, but it did not seem to get any views. Please help!

I'm trying to import an excel spreadsheet into SQL Server. I keep getting an error saying that 2 columns cannot except NULL values. Here's the setup of each file.

SQL TBL:
My table has 9 columns. the first column is a Primary Key and IDENTITY(1,1). My last column is a dateCreated column with a default of getDate(). Both values are set to NOT NULL. The 7 inside columns are just plain varChar datatypes.

XLS:
My spreadsheet has 7 columns which corelate to the 7 inside columns of my SQL TBL. I do not want to specify my first and last columns because they should be unique to the SQL TBL (identity and getDate()).

Any help with how to do this would be great. If you need more info, please let me know.

Thanks,
TimTim:

If this table is already full of data, edit the DTS package Transformation properties to NOT Copy to EITHER your PK or Date field.

Otherwise, if the table's empty, drop the PK & Date fields for the initial import THEN re-add them back.

Hope this helps

RobbieD

DTS, Excel, and ActiveX

Hi-
I have an excel spreadsheet that I want to poulate with data via DTS. What
I need is the previous data to be cleared from the spreadsheet starting
with the second row(The first row is going to be column headers). Can that
be accomplished via an ActiveX? Any help is appreciated.Hi
You could either start with a template file without any data and then
copy/create the final data file before you start see
http://www.sqldts.com/default.aspx?292 for help.
Alternatively this may help to delete the whole sheet
http://www.sqldts.com/default.aspx?245
If not then issue a SQL delete statement againsta range in your sheet.
John
"Jeff York" wrote:

> Hi-
> I have an excel spreadsheet that I want to poulate with data via DTS. Wha
t
> I need is the previous data to be cleared from the spreadsheet starting
> with the second row(The first row is going to be column headers). Can tha
t
> be accomplished via an ActiveX? Any help is appreciated.

DTS vs Excel numeric conversion

I am having a problem importing an Excel spreadsheet. I have a column in
an Excel sheet with alphanumeric text and some of the cells are numeric.
Some of the cells contain numbers like 12345.6 and when DTS is done
importing it into a field that is nvarchar the results are
"12345.600000000001". I have tried:

1. Changing the format of the Excel column to text
2. Using the formula =text(a1,0) which only truncates the .6
3. Using the formula =t(a1) which will remove some numeric representations
4. Exporting the sheet to CSV or TXT first which will not enclose the cell
contents with ""
5. Beating the computer with a nine iron

None of these options work. Any idea anyone?

Don VonderBurgFormatting the cells as text after the data are there won't help.
Copy the cells to another location which is PREformatted as text, then
copy the copy back onto the original cells and try again. :)

On Thu, 13 May 2004 22:03:51 GMT, Don.Vonderburg@.nospam.com wrote:

>I am having a problem importing an Excel spreadsheet. I have a column in
>an Excel sheet with alphanumeric text and some of the cells are numeric.
>Some of the cells contain numbers like 12345.6 and when DTS is done
>importing it into a field that is nvarchar the results are
>"12345.600000000001". I have tried:
>1. Changing the format of the Excel column to text
>2. Using the formula =text(a1,0) which only truncates the .6
>3. Using the formula =t(a1) which will remove some numeric representations
>4. Exporting the sheet to CSV or TXT first which will not enclose the cell
>contents with ""
>5. Beating the computer with a nine iron
>None of these options work. Any idea anyone?
>Don VonderBurg|||Never thought of that one. Thank you.

Don|||Hi,

I vaguely remember that when you import the Excel file through DTS
that you can set the data type somewhere. Perhaps that helps.

When you look at the numbers that are wrongly imported in the Excel
formula bar, do you see the error as well? I guess when this is the
result in an Excel calculcated cell you may expect these rounding
errors. Remember that in Excel you never actually see the underlying
value. All values are always displayed using some kind of a display
mask. You can use the round function in Excel to round your results.
That should take care of it.

Just to make you aware of another way to pump your data into the
database. I wrote an addin for Excel called SQL*XL. Its goal is to
remove these hassles from the end user. You can use SQL*XL to get data
from the database into Excel or to pump data from Excel into the
database. It even lets you change retrieved data in Excel and post the
changes back.

If you are interested, have a look at SQL*XL at www.oraxcel.com

Best regards, Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com

DTS to export formated date to excel

Hello,

I am trying to output data from my sql table to an excel spreadsheet and send it by email which works fine, the problem is he wants the date to be in the format d-mmm-yy, which is easy to format in excel manually, but he do not want to do this manually. I tried to do this when I select the date from the table to spreadsheet, "select convert(char,value_date,106) from table", but this don't get transported to the excel spreadsheet, I get my results on the spread sheet as dd/mm/yy. Can you please help either to set the date on excel forever to be in this format "d-mmm-yy" or to force this output to excelOk, i manage to answer myself, you need to format the cells onto the excel file itself