Hi,

I have an Excel sheet that one of the workers has created and it must be
imported by column (number of columns varies). The pattern is simple, there
are groups of three columns repeated for each data header.

In one of the columns there is a value that looks like -9:49 (9 hours and
49 minutes ago). When I first save the xls as a csv, it puts an "=" (equal
sign) in front of the value and this value won't import. I tried to change
the column to be text only but this also is failing. I discovered I can go
in and open the csv in notepad and fix the screwed up values.

Then the import begins. I use VStudio to create an Excel Object (late
bound). All is well until for values like -9:30 and -9:00 but when it hits
the -9:49, the bound cell value returns -2146826273. I read something about
how the Excel.Application is now an Excel.Applicationclass.

I am running 2007 but have Office 97 loaded and can probably use this in an
emergency. I am not sure if it is the 2007 Excel object that is causing the
problem or if it is Visual Studio 2005.

Prior to getting the -2146826273, I received a Exception from HRESULT:
0x800A03EC. I was able to get around this by printing the cell value to
string before inserting it into the sql insert statement.

Please tell me there is an easier way?
--
Regards,
Jamie

I don't think that you can have negative times or dates. However, when I am
having problems importing csv files, I ensure that I have file extensions
enabled in Windows Explorer and change the file name to .txt; Then when you
import it you get full control over the Text To Columns.

If you do the above you might be able to set the column to text and then
handle the problem after it is imported. I would need to see a sample of the
data to suggest how you might handle it.

Don't know if you need the following but just in case:-
To turn on file extensions, Windows Vista:-
Open windows explorer
Select Organize-folder and search options-view tab and uncheck 'Hide
extensions for known file types'

Earlier versions of windows select Tools-Folder options and then as above.

--
Regards,

OssieMac


"thejamie" wrote:

Hi,

I have an Excel sheet that one of the workers has created and it must be
imported by column (number of columns varies). The pattern is simple, there
are groups of three columns repeated for each data header.

In one of the columns there is a value that looks like -9:49 (9 hours and
49 minutes ago). When I first save the xls as a csv, it puts an "=" (equal
sign) in front of the value and this value won't import. I tried to change
the column to be text only but this also is failing. I discovered I can go
in and open the csv in notepad and fix the screwed up values.

Then the import begins. I use VStudio to create an Excel Object (late
bound). All is well until for values like -9:30 and -9:00 but when it hits
the -9:49, the bound cell value returns -2146826273. I read something about
how the Excel.Application is now an Excel.Applicationclass.

I am running 2007 but have Office 97 loaded and can probably use this in an
emergency. I am not sure if it is the 2007 Excel object that is causing the
problem or if it is Visual Studio 2005.

Prior to getting the -2146826273, I received a Exception from HRESULT:
0x800A03EC. I was able to get around this by printing the cell value to
string before inserting it into the sql insert statement.

Please tell me there is an easier way?
--
Regards,
Jamie

That was easy. What was I thinking?
--
Regards,
Jamie


"OssieMac" wrote:

I don't think that you can have negative times or dates. However, when I am
having problems importing csv files, I ensure that I have file extensions
enabled in Windows Explorer and change the file name to .txt; Then when you
import it you get full control over the Text To Columns.

If you do the above you might be able to set the column to text and then
handle the problem after it is imported. I would need to see a sample of the
data to suggest how you might handle it.

Don't know if you need the following but just in case:-
To turn on file extensions, Windows Vista:-
Open windows explorer
Select Organize-folder and search options-view tab and uncheck 'Hide
extensions for known file types'

Earlier versions of windows select Tools-Folder options and then as above.

--
Regards,

OssieMac


"thejamie" wrote:

Hi,

I have an Excel sheet that one of the workers has created and it must be
imported by column (number of columns varies). The pattern is simple, there
are groups of three columns repeated for each data header.

In one of the columns there is a value that looks like -9:49 (9 hours and
49 minutes ago). When I first save the xls as a csv, it puts an "=" (equal
sign) in front of the value and this value won't import. I tried to change
the column to be text only but this also is failing. I discovered I can go
in and open the csv in notepad and fix the screwed up values.

Then the import begins. I use VStudio to create an Excel Object (late
bound). All is well until for values like -9:30 and -9:00 but when it hits
the -9:49, the bound cell value returns -2146826273. I read something about
how the Excel.Application is now an Excel.Applicationclass.

I am running 2007 but have Office 97 loaded and can probably use this in an
emergency. I am not sure if it is the 2007 Excel object that is causing the
problem or if it is Visual Studio 2005.

Prior to getting the -2146826273, I received a Exception from HRESULT:
0x800A03EC. I was able to get around this by printing the cell value to
string before inserting it into the sql insert statement.

Please tell me there is an easier way?
--
Regards,
Jamie